423,850 Members | 1,661 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

SELECT from ODBC INTO access DB

Seth Schrock
Expert 2.5K+
P: 2,908
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.
4 Weeks Ago #1

✓ answered by 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.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,121
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.
3 Weeks Ago #2

Seth Schrock
Expert 2.5K+
P: 2,908
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.
3 Weeks Ago #3

Stevan Bias
100+
P: 200
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
3 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,121
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.
3 Weeks Ago #5

Stevan Bias
100+
P: 200
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.
3 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.