Insert Command Query

• isay

  SSC Veteran

  Points: 242

  More actions

  February 21, 2006 at 7:38 am

  #67233

  INSERT INTO Name(Surname, FirstName) FROM SELECT Surname, Name

  From OtherNames WHERE Age < 30;

  Hi,

  Just new for this kind of programming. I am using VB for the code below. I have a recordset (rs) that I want to insert in TestCorpFile where I dont want to insert rs!EXP_CTrY = 'vn'. I tried to follow the syntax I researched in the above of my message but I didn't get it right. Please need someones expertise. Thanks in advance. 

  rs.Open "Pacrim", cnCurrent, adOpenDynamic, adLockOptimistic, adCmdTable

  cnCurrent.Execute ("insert into TestCorpFile(Ctry_code,Ship_ID,LCamount,USDamount,Weekending)" & _

       " values ('" & rs!EXP_CTrY & "','" & rs!SHIPMENT_ID & "'," & rs!CONVERTED_AMOUNT & "," & rs!USD_AMOUNT & ",'" & rs!TDATE & "')")

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  February 21, 2006 at 8:50 am

  #622315

  When you create a SQL statement like this in VB it becomes (as you've noticed) very difficult to work out whether it's correct. I suggest you do something like the following

  dim strSQL as string

  strSQL = "insert into TestCorpFile(Ctry_code,Ship_ID,LCamount,USDamount,Weekending)" & _

       " values ('" & rs!EXP_CTrY & "','" & rs!SHIPMENT_ID & "'," & rs!CONVERTED_AMOUNT & "," & rs!USD_AMOUNT & ",'" & rs!TDATE & "')"

  msgbox strSQL

  and you'll be able to work out what's going on.  Then just

  cnCurrent.Execute(strSQL)

  ---