Export SQL Server DB to Access DB 1

[traceytr]

Hi!

When we export a table from SQL Database to Access Database, we use two ado's. One for SQL connection/recordset & one for Access connection/recordset. After we get the SQL recordset, we step threw the recordset one record at a time and make the access recordset equal to the current sql recordset then do a recordset.addnew. This takes a while if the recordset has 10,000 records. I need to know if there is a way to take a SQL table and dump it to an Access table OR take the SQL recordset and do some sort of batch update to the access table/recordset.


Thanks,

Tracey

 

[tlbroadbent]

I'd use DTS to export from SQL (7 or 2000) to Access. Use the Import/Export Wizard in SQL Server to set up the export package.

You could also import the SQL table to Access. Select Import in Access and select the ODBC data source. Or link the SQL table and use a make table or append query to transfer the data from SQL Server to Access.

There are other ways to accomplish the export but these are the simplest to implement. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[traceytr]

Thanks, Terry. This information is SO helpful. By chance, would you know where I might find an example of a VB app calling a DTS Package?

Again, thanks for the direction.

Tracey