CODEWITHSUNDEEP
c#sqlms-accessexport
Jake Ginnivan
Jake Ginnivan

Reputation: 2142

SQL to MS Access export

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:

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.

Upvotes: 2

Views: 4129

Answers (5)

Jake Ginnivan
Jake Ginnivan

Reputation: 2142

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.

Upvotes: 1

Matt
Matt

Reputation: 5172

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.

Upvotes: 0

dkretz
dkretz

Reputation: 37655

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?

Upvotes: 1

iDevlop
iDevlop

Reputation: 25272

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

Upvotes: 1

MarkusQ
MarkusQ

Reputation: 21950

Have you tried this?

Upvotes: 1

Related Questions