Import Excel Spreadsheet into existing Access table using ADODB

[paulette33]

I am trying to import an Excel spreadsheet and do not want to use docmd.transferSpreadsheet as I need to do some additional manipulation, etc. I have created an ADODB connection to the Excel spreadsheet, but now I am not sure what is the best way to update my table with this data. Any suggestion?

Thank you in advance!

 

[ickleMouse]

You say you've created an ADODB connection to the spready - I'll assume then that you've created a recordset object based on the data in the spready that you want to xfer to Access. All you need to do now is create an ADODB recordset based on the table in Access that you want to append to and run somethng like;

Do While Not rs_td.EOF
  ado_rs.AddNew
    For int_c = 0 To rs_td.fields.Count - 1
      ado_rs.fields(int_c).Value = rs_td.fields(int_c).Value
    Next int_c
   ado_rs.Update
   rs_td.MoveNext
Loop

This was updating Access("ado_rs") with a recordset based on some Teradata data ("rs_td") instead of Excel but it'll work for a recordset based on Excel too.

HTH

 

[paulette33]

Thank you for your help!