Recordset Problem...

[gnt]

Hello-
I'm trying this thread again, because I haven't had much luck with it...
I'm trying to pull data from a database on our server and import it into a local table in Access. The only way I've found to do it is to create a recordset with the data and run an INSERT SQL statement cycling through all the records. I need to import about 50,000 records from 2 different db's. Needless to say, this method takes a bit long (it has to run 50,000 INSERT statements). Is there a more efficient way to do this?
A couple things:
-Using Access 2000
-I can't use a Pass through query
-I can't use linked tables

Thanks for any help!

 

[Steve101]

What is the database on your server? Why can't you use Passthrough or Linked tables. I presume that you have some sort of security issue there. If not, do you have ODBC connectivity.

If the above is truly a "no go", then you might want to try exporting the data as a CSV formatted file (comma separated variable), or an excel spreadsheet; then define the corresponding structure of the table in Access, and Import the data using the File, Import options.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[gnt]

Thanks Steve-
The problem with the pass-through query and linked tables is that once I establish a connection using the following Connect statement:

ODBC;server=cnxsvr;dsn=vax_db;uid=uid1;pwd=pwd1;

it won't close the connection until I shut down Access. So when I run another pass-through query using:

ODBC;server=cnxsvr;dsn=vax_db;uid=uid2;pwd=pwd2;

it still pulls data from uid1/pwd1. The same is true for linked tables.

Importing them manually from an xls or csv could work, but I need it to be extremely user friendly for my users, and I prefer not to give them access to all that. Currently, I have it set up through 2 different pass-through queries and I advise the user to shutdown and restart the program before updating the 2nd company's tables. I'd prefer to pull both company's data (uid1 and uid2) into a single local table with one command. But maybe this is not possible?

Thanks again-

 

[gnt]

Steve-
I figured it out using .AddNew to populate my tables. 50,000, 10 field records in 15 seconds - that I can live with. Syntax in thread705-208622.
Thanks-