MS Access interop - Data Import

Question

I am working on a exe to export SQL to Access, we do not want to use DTS as we have multiple clients each exporting different views and the overhead to setup and maintain the DTS packages is too much.

*Edit: This process is automated for many clients every night, so the whole process has to be kicked off and controlled within a cursor in a stored procedure. This is because the data has to be filtered per project for the export.

I have tried many ways to get data out of SQL into Access and the most promising has been using Access interop and running a

doCmd.TransferDatabase(Access.AcDataTransferType.acImport...

I have hit a problem where I am importing from views, and running the import manually it seems the view does not start returning data fast enough, so access pops up a MessageBox dialog to say it has timed out. I think this is happening in interop as well, but because it is hidden the method never returns!

Is there any way for me to prevent this message from popping up, or increasing the timeout of the import command?

My current plan of attack is to flatten the view into a table, then import from that table, then drop the flattened table.

Happy for any suggestions how to tackle this problem.

Edit:

Further info on what I am doing:

We have multiple clients which each have a standard data model. One of the 'modules' is a access exporter (sproc). It reads the views to export from a parameter table then exports. The views are filtered by project, and a access file is created for each project (every view has project field)

We are running SQL 2005 and are not moving to SQL 2005 quickly, we will probably jump to 2008 in quite a few months.

We then have a module execution job which executes the configured module on each database. There are many imports/exports/other jobs that run in this module execution, and the access exporter must be able to fit into this framework. So I need a generic SQL -> Access exporter which can be configured through our parameter framework.

Currently the sproc calls a exe I have written and my exe opens access via interop, I know this is bad for a server BUT the module execution is written so only a single module is executing at a time, so the procedure will never be running more than one instance at a time.

Answer 1

Have you tried using VBA? You have more options configuring connections, and I'm sure I've used a timeout adjustment in that context in the past.

Also, I've generally found it simplest just to query a view directly (as long as you can either connect with a nolock, or tolerate however long it takes to transfer); this might be a good reason to create the intermediate temp table.

There might also be benefit to opening Acces explicitly in single-user mode for this stuff.

Answer 2

We've done this using ADO to connect to both source and destination data. You can set connection and command timeout values as required and read/append to each recordset.

No particularly quick but we were able to leave it running overnight

Answer 3

I have settled on a way to do this.

http://support.microsoft.com/kb/317114 describes the basic steps to start the access process.

I have made the Process a class variable instead of a local variable of the ShellGetApp method. This way when I call the Quit function for access, if it doesn't close for whatever reason I can kill the process explicitly.

app.Quit(Access.AcQuitOption.acQuitSaveAll);
if (!accessProcess.HasExited)
{
    Console.WriteLine("Access did not exit after being asked nicely, killing process manually");
    accessProcess.Kill();
}

I then have used a method timeout function here to give the access call a timeout. If it times out I can kill the access process as well (timeout could be due to a dialog window popping up and I do not want the process to hang forever. I got the timeout method here.

http://stackoverflow.com/questions/299198/implement-c-generic-timeout

Answer 4

I'm glad you have a solution that works for you. For the benefit of others reading this, I'll mention that SSIS would have been a possible solution to this problem. Note that the difference between SSIS and DTS is pretty much night and day.

It is not difficult to parameterize the export process, such that for each client, you could export a different set of views. You could loop over the lines of a text file having the view names in it, or use a query against a configuration database to get the list of views. Otherparameters could come from the same configuration database, on a per-client and/or per-view basis.

If necessary, there would also be the option of performing per-client pre- and post-processing, by executing a child process, or pacakge, if such is configured.