SELECT from ODBC INTO access DB

[Seth Schrock]

I have a database from which I need to pull data and insert it into a local access table. I have successfully tested creating an ADODB recordset. Now I need to some method to copy the data locally. I really don't want to loop through the recordset and copy the data row by row. I'm using a DSN-less connection, so I'm having to provide credentials when the code runs, so I don't think that a straight-through query would work.

So I guess what I'm wondering is if there is a way to use an SELECT INTO query, possibly utilizing the ADODB connection that I have built.

This particular database is not one that I want to do a lot of testing against as it would cause major problems if it stopped working for any reason.

[NeoPa]

I may be wrong Seth, but I don't believe there's any way available to take data from within your code and dump it into a table. It's possible to do in Excel using CopyFromRecordset, but I'm not aware of such a thing in Access.

How about a linked table to the ODBC source that requires the user name and password then opening that from code and passing the credentials across invisibly. After that you copy the data in the normal way and then remove the credentials from the linked table. You can experiment with different approaches but as a last resort you could always delete the linked table.

[Seth Schrock]

So basically I'm stuck editing connection strings via code to get what I need since I can't hard code the credentials. I guess that isn't so much different than what I was doing.

I could create a pass-through query and then run and INSERT FROM based on that pass-through query once I get it the proper connection string and then remove the connection string once I was done. I have four tables that I'm pulling from, so I would rather not have to connect each one and then disconnect them.

Thanks for the input. At least I have a new direction to try.

[Brilstern]

Have you thought about dumping the remote tables into a spreadsheet then importing back into your local DB programmatically? Might be a little more code, but it bypasses your credential problem, I believe...

-Bril

[NeoPa]

As Stevan says, at least I think he is, you could use CopyFromRecordset() into an Excel worksheet and link to that from your database. Convoluted but workable for anything up to about a million records.

[Brilstern]

Indeed it is NeoPa. I have done quite a bit of work from excel to access and access to excel. As you stated, not always the most direct method; but it can be much simpler, as temporary content through excel is much easier than access.