Look through a recordset and insert data into tables RRS feed

  • Question

  • Im trying to copy data from a recordset into a table.

    This is my code:

    strSQL = "INSERT INTO VRSelectionData  (year,quarter,timeband,strata,station_entrance_id, station_entrance) VALUES " & _
     "('" & rsData!Year & "', " & rsData!quarter & ", '" & rsData!TimeBand & "', " & _
     " " & rsData!strata & ", " & rsData!station_entrance_id & ", '" & rsData!station_entrance & "')"

    Do Until rsData.EOF

    CurrentDb.Execute strSQL


    For some reason it keeps on inserting only the first record of the recordset. It does it the correct number of times, but always the same record. Also then I replace the Execute statement with Debug.Print, it prints out the correct records and does not get stuck on the first one. Its only when I run the INSERT statement that it happens.

    btw the recordset is a result of a SQL server SP.

    Friday, February 21, 2014 4:10 AM

All replies

  • I've solved it myself by placing the strSQL inside the loop. Not sure why that worked, seems to me like the same thing. Bus if anyone can explain that would be great.
    Friday, February 21, 2014 4:39 AM
  • Before adding strSQL to the code, you were creating a new string on each loop, and therefore erasing all the data from the previous loops. By adding strSQL you are concating the strings, and therefore getting the results you wanted.  

    VBA Programming Service

    Sunday, February 23, 2014 8:31 AM