Apped ADODB Recordset to local MS Access table.

If there was a local MS Access table called "tblRecords" table that had the same column names as the adodb record set "rs" in your example, what would be the easiest way of appending rs to tblRecords?
LVL 1
jrigginAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Apologies for not looking at the other question, the Related Question part isn't very prominent.

I'll take a look at it later.

Not sure what you mean about the table not being linked to MS Access.

Which table do you mean?

Do you meant that neither table will be in Access?

ie the source being in MySQL and the destination somewhere else but not Access.
0
 
Kevin CrossChief Technology OfficerCommented:
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!
0
 
NorieVBA ExpertCommented:
Where is the recordset coming from?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Kevin CrossChief Technology OfficerCommented:
imnorie: it is coming from MySQL ODBC (see the related question link).
0
 
jrigginAuthor Commented:
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.
0
 
jrigginAuthor Commented:
Note:  I don''t want to have to make a manual ODBC link for each new tablename parameter.
0
 
NorieVBA ExpertCommented:
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.
0
 
NorieVBA ExpertCommented:
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

Open in new window

0
 
jrigginAuthor Commented:
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?
0
 
jrigginAuthor Commented:
I already accepted this as a solution, but I must never have clicked submit.  Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.