• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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?
0
jriggin
Asked:
jriggin
  • 4
  • 4
  • 2
1 Solution
 
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
 
NorieCommented:
Where is the recordset coming from?
0
 
Kevin CrossChief Technology OfficerCommented:
imnorie: it is coming from MySQL ODBC (see the related question link).
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now