SQL to MS Access export

Question

I am trying to replace a DTS access exporter package with a exe we can call from our stored procedures (using xp_cmdshell).

We are in the middle of a transition between SQL 2000 and SQL 2005, and for the moment if we can not use DTS OR SSIS that would be the best options.

I believe I have the following options:

• Using a SQL data reader to read SQL records, and using ADO.net to insert the read records into Access. I have implemented this and it is WAY too slow. This is not a option
• Setting up Linked tables in access, then getting access to pull the data out of sql. If anyone has any experience in doing this I would be grateful for some code examples or pointing out some resources?

If there are any other options for transferring large amounts of data from SQL into a Access database that would be awesome, but performance is a big issue as we can be dealing with up to 1mil records per table.

Answer 1

Have you tried this?

Answer 2

Why not creating a linked table in Access, and pulling data from Sql Server instead of pushing from Sql to Access ?

Answer 3

I've done plenty of cases where I start with an Access database, attach to SQL Server, create a Create Table or Insert Querydef, and write some code to execute the querydef, possibly with arguments. But there are a lot of assumptions I would need to make about your problem and your familiarity with Access to go into more detail. How far can you get with that description?

Answer 4

Have you looked at bcp? It's a command line utility that's supposed work well for importing and exporting large amounts of data. I've never tried to make it play nice with Access, but it's a great lightweight alternative to DTS and/or SSIS.

Like others have said, the easiest way I know to get data into an Access mdb is to set things up in Access to begin with. Roughly speaking:

1. Create linked tables to the SQL data you want to export. (in Access: File --> get ecternal data --> link tables) This just gives you a connection to sql server.
2. Create a local table that represents teh schema of the data you want to export. (on the tables tab, click the "new" button and follow your nose).
3. Create an Update query that selects data from the linked tables (SQL Server) and appends rows to the local table (access mdb).
4. On the macros tab, create a new macro that executes the query you just created above (I can't recall the exact "action" to use, but it's something like OpenQuery or RunQuery); name the macro "autoexec", which will cause it to automatically run when the mdb is opened.
5. Use a script (or whatever) to copy and open the mdb when appropriate; the autoexec macro will kick things off and the query will copy data from SQL server to the mdb.

Answer 5

I have ended up using Access interop, thanks to le dorfier for pointing me in the direction of the import function which seems to be the simplest way..

I now have something along these lines:

Access.ApplicationClass app = new Access.ApplicationClass();
Access.DoCmd doCmd = null;

app.NewCurrentDatabase(_args.Single("o"));
doCmd = app.DoCmd;

//Create a view on the server temporarily with the query I want to export

doCmd.TransferDatabase(Access.AcDataTransferType.acImport,
    "ODBC Database",
     string.Format("ODBC;DRIVER=SQL Server;Trusted_Connection=Yes;SERVER={0};Database={1}", _args.Single("s"), _args.Single("d")),
     Microsoft.Office.Interop.Access.AcObjectType.acTable,
     viewName,
     exportDetails[0], false, false);
//Drop view on server

//Releasing com objects and exiting properly.

Answer 6

I recommend MDBScript, it's a small utility to easily create, export and generate sql scripts from existing MS Access database.