Apped ADODB Recordset to local MS Access table.

For moving large amounts of data, I would probably use the External Data menu to add a linked table or the like and then insert data using normal SQL commands. If you have to do this via VBA, you will likely have to  use loop through the recordset to insert each row into your Access table. Many times this requires specific knowledge of the table structure, which is why I typically use link table anyway as the flexibility of a function does not actually help in that instance. Hope that helps!

Where is the recordset coming from?

imnorie: it is coming from MySQL ODBC (see the related question link).

The issue is that I won't know the table structure.  Vicidial (an Asterisk based auto dialer) makes a table name "custom_" & [List_ID] for each new list with custom fields my client adds.  My VB app will make a table with the same field names as "rs" in MS Access, but I will not know what they are.  I know that the table parameter for the MySQL sproc will match my local MS Access table.  I know I could iterate thought he adodb "rs" and update/append by hardcoding the column names, but I was hoping there was some way to do something like "docmd.runsql("append rs to _tblRecords") in VBA.  Then I don't have to change the code for each new list with custom fileds.

Note:  I don''t want to have to make a manual ODBC link for each new tablename parameter.

As far as I'm aware there's no way to do that with a recordset in Access.

Why not set up a link or import the data to Access as mwvisa suggests?

Then you should be able to use a simple append query with the name of the imported/linked table in place of the recordset.

This is VBA but it will import data from MySQL to Access and then run an append query.

Sub ImportFromMySQL()
Dim strODBC As String
Dim strDBName As String

    ' adjust driver to suit and change server/UID/password etc as needed

    strODBC = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
              "SERVER=localhost;UID=root;PASSWORD=XXXXXXXX;DATABASE="

    strDBName = "World"

    DoCmd.TransferDatabase acImport, "ODBC", strODBC & strDBName, acTable, "city", "NewCity"

    DoCmd.RunSQL "INSERT INTO OldCity SELECT * FROM NewCity;"

End Sub

Select allOpen in new window

I thought EE would note this is a continuation of question id 27417677, which provides the overall scenario.

In immorie's suggestion, can you do transferdatabase it the table is not linked in MS Access?

I already accepted this as a solution, but I must never have clicked submit.  Thanks for your help