Click here to Skip to main content
Click here to Skip to main content
Go to top

Very Fast Table Copy from an ODBC Source into Microsoft Access within a C# Project

, 11 Jan 2008
Rate this:
Please Sign up or sign in to vote.
An extremely fast and efficient way to import a table from any ODBC-capable database into MS Access (while running in a C# project), by using the Microsoft Access built-in import functionality.

Introduction

In a recent project I worked on, I came across a pretty serious problem that seemingly had no solution. The problem was simple: I needed to copy a fairly large amount of data from a table in a SQL Server database to a table in a Microsoft Access database. No matter which approach I followed, however, the speeds at which the copy was being executed were far too low and simply unacceptable.

My frame of reference was Microsoft Access itself, which has the ability to import tables from an ODBC source. When I compared the time it took to perform this import, it consistently beat the other tests by a wide margin of at least twenty to one.

It sounds huge. I know. I do not have the exact times it took for each of the tests, but they were all much longer than doing a simple import through Microsoft Access. The comparison tests I conducted were:

  • Using Enterprise Manager
  • Using SQL Server Management Studio
  • Using SQL Server Information Services
  • Using BCP (although this can only export to a text file or SQL Server binary file). The BCP process alone was able to match the time record achieved by the Microsoft Access import, but useless since it could not export to Microsoft Access.

The ultimate solution, then, was to find a way to have Microsoft Access perform the copy all the while running within a C# project. The biggest problem with this was that including support for Microsoft Access in a .NET project automatically means including COM, and a lot of people cringe at the thought. This article, then, discusses the implementation of an SQL server to Microsoft Access copy process that is performed by Microsoft Access, runs within a C# project, and minimizes the impact COM has on the overall application by using late binding.

Embedding a COM object in .NET

It isn't pretty, but it's possible. It will make your setup and installation more complex, but will add functionality that may otherwise not be available from within the .NET Framework. The bottom line is that it is up to you on whether you want to include COM support in your project, and you'll have to weigh the pros and cons and then decide.

In my case, there wasn't much of a choice. I could either leave my project spic and span and let the copy take almost an hour, or find an elegant way to implement COM and shrink the copy process down to a couple of minutes. The choice was pretty clear.

There are basically two ways to use a COM object in your project:

Early Binding

In this case, you add a reference to the COM object in question to your project, and benefit from the fact that Visual Studio can (in some cases) offer intellisense on the classes, methods and properties of this object. For screen controls, you also get the ability to add them to your forms in the screen designer. On the down side, however, you can no longer just copy the project files from your bin\debug or bin\release folder to another machine and run the program. You now need a setup project that will copy and register the COM controls on the target machine as well as copy your project files.

Late Binding

If you are guaranteed that the COM object in question is already installed on the target computer, and can do away with such niceties as intellisense, you can write your code so that it hooks up to the already-installed COM control and use it in pretty much the same way. The down side, obviously, is that you are not guaranteed your code will always work (because you cannot be 100% certain the COM object is already installed on the target machine), and you will not have access to intellisense or the screen designer.

Importing a Table

When importing a table into Microsoft Access, the application lets you do so by going to File\Get external data\Import. You then choose a source such as an ODBC connection, select the database and table, and that's it. The table gets copied over (albeit without any keys or indexes, which you then have to rebuild).

This functionality is very much available through COM automation, and can therefore be used in a C# project. The problem is that the code is only available from within an Access module (although I'm sure one the readers will find a way to access this function directly from C#). So to get to it, you need to write a VB function that will import a table. The C# project will then need to load the Access database and execute the function which in turns performs the import.

The only drawback to this method is that it is not asynchronous (although you could add threading on the C# side) and has no way to indicate progress.

The VB function you'll need to put into Microsoft Access is quite simple, and basically calls the TransferDatabase method by passing it a DSN (pointing to the source database), a source table name and target table name. The code is as follows:

Public Function Import(dsnName As String, sourceTableName As String, _
	targetTableName As String)
' if the table already exists, delete it.
   On Error GoTo CopyTable
   DoCmd.DeleteObject acTable, targetTableName
CopyTable:
   DoCmd.TransferDatabase _
      acImport, _
      "ODBC Database", _
      "ODBC;DSN=" + dsnName, _
      acTable, _
      sourceTableName, _
      targetTableName
End Function

And then the C# code:

object accessObject = null;
try
{
   accessObject = Activator.CreateInstance(Type.GetTypeFromProgID("Access.Application"));

   accessObject.GetType().InvokeMember(
      "OpenCurrentDatabase",
      System.Reflection.BindingFlags.Default | 
	System.Reflection.BindingFlags.InvokeMethod,
      null, 
      accessObject, 
      new Object[] { "AccessDbase.mdb" });

   accessObject.GetType().InvokeMember(
      "Run",
      System.Reflection.BindingFlags.Default | 
	System.Reflection.BindingFlags.InvokeMethod,
      null, 
      accessObject, 
      new Object[] { "Import", "DSN Name", "Source table name", "Target table name" });

   accessObject.GetType().InvokeMember(
      "CloseCurrentDatabase",
      System.Reflection.BindingFlags.Default | 
	System.Reflection.BindingFlags.InvokeMethod,
      null, 
      accessObject, 
      null);

   MessageBox.Show("Copy succeeded.");
}
catch (Exception ex)
{
   string message = ex.Message;
   while (ex.InnerException != null)
   {
      ex = ex.InnerException;
      message += "\r\n----\r\n" + ex.Message;
   }
   MessageBox.Show(message);
}
finally
{
   if (accessObject != null)
   {
      System.Runtime.InteropServices.Marshal.ReleaseComObject(accessObject);
      accessObject = null;
   }
}

All we're doing here is creating an instance of Access.Application, opening a database, executing the VB module to perform the import, closing the database and cleaning up.

Note that for the copy to work, you need to specify several pieces of information:

  • The fully qualified name of the Access database
  • The name of an ODBC DSN created in your control panel that points to the source database (i.e. where the table will be copied from)
  • The name of the table as it appears in the source database
  • The name you want to assign to the table once it's been copied (can be the same as the source)

Finally, it should be noted that the copy process creates the table with no keys or indexes, so these will have to be rebuilt after the copy is complete. This is very easily achieved, however, by executing SQL "ALTER TABLE " commands through ADO. There are plenty of articles out there that describe this, so I won't get into it here.

Credits and Further Information

This article may not seem very big, but it is the culmination of a massive headache and lot of needle hunting (in a haystack of needles) on the net. I found several articles out there that helped out, and these are some of them:

  1. http://www.thescripts.com/forum/thread255310.html - Executing a Microsoft Access module from C#
  2. http://support.microsoft.com/kb/306683 - Executing Microsoft Access modules from within C#. Note that the method described here uses early binding, which means you're adding COM references to your project and you are therefore complicating your setup/installation package.
  3. http://www.codeproject.com/cs/database/mdbcompact_latebind.asp - Very good article by Alexander Yumashev describing how to compact and repair a Microsoft Access database from within C#. More importantly, it overcomes the problem in the Microsoft article mentioned above by using late binding. The code I used for dynamically linking to Access comes from here.
  4. http://www.codeproject.com/cs/database/DSNAdmin.asp - A good article on how to dynamically create a DSN entry. By using this in conjunction with the current article, you'll no longer need to depend on a pre-existing DSN, and you can also clean up after yourself by deleting your DSN when you're done copying the table.

In Conclusion

Microsoft Access is being put out to pasture out for many good reasons, but it's still very much in use. So if you're one of those people who still has to copy large amounts of data from another database source to Microsoft Access, need to do in a C# project, and want a clean way to do it, then I think this article will help.

If you don't fall in that criteria, though, then I hope you at least had an interesting read and kept this in the back of your mind for future use.

History

  • November 27 2007 - Initial post
  • November 29 2007 - Added reference to article about dynamic DSN creation
  • January 11 2008 - Corrected formatting issue within the article

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

David Catriel
Team Leader
Canada Canada
A developer that's been tinkering with computers since he first laid eyes on his buddy's Atari in the mid 80's and messed around with GWBasic and Logo. He now divides his time among his wife, kids, and evil mistress (a term lovingly [ahem...] given to his computer by the wife ...).
 
For more info, please see my LinkedIn profile: http://www.linkedin.com/pub/david-catriel/44/b01/382

Comments and Discussions

 
GeneralFrom Access To SQL PinmemberAhsanS7-May-09 1:23 
GeneralRe: From Access To SQL PinmemberMuaddubby7-May-09 2:34 
GeneralRe: From Access To SQL PinmemberAhsanS7-May-09 21:18 
GeneralRe: From Access To SQL PinmemberMuaddubby8-May-09 2:26 
GeneralRe: From Access To SQL PinmemberAhsanS8-May-09 23:29 
GeneralRe: From Access To SQL PinmemberMuaddubby9-May-09 1:45 
GeneralRe: From Access To SQL PinmemberAhsanS10-May-09 19:27 
GeneralRe: From Access To SQL PinmemberMuaddubby11-May-09 2:36 
QuestionMicrosoft Office Access can't find the procedure 'Import.' PinmemberMike Ranzinger4-Mar-09 6:13 
AnswerRe: Microsoft Office Access can't find the procedure 'Import.' PinmemberMuaddubby4-Mar-09 6:26 
GeneralRe: Microsoft Office Access can't find the procedure 'Import.' PinmemberMike Ranzinger4-Mar-09 7:05 
GeneralRe: Microsoft Office Access can't find the procedure 'Import.' PinmemberMuaddubby4-Mar-09 7:15 
GeneralRe: Microsoft Office Access can't find the procedure 'Import.' PinmemberMike Ranzinger4-Mar-09 7:29 
GeneralRe: Microsoft Office Access can't find the procedure 'Import.' PinmemberMuaddubby4-Mar-09 8:15 
Questionhow to copy from dbf to MS Server(2) Pinmemberleon91621-Jan-08 0:50 
AnswerRe: how to copy from dbf to MS Server(2) PinmemberMuaddubby21-Jan-08 6:58 
QuestionADO.net provider for the Jet Engine? PinmemberPIEBALDconsult11-Jan-08 11:58 
AnswerRe: ADO.net provider for the Jet Engine? PinmemberMuaddubby11-Jan-08 12:21 
GeneralRe: ADO.net provider for the Jet Engine? PinmemberPIEBALDconsult11-Jan-08 13:16 
AnswerRe: ADO.net provider for the Jet Engine? PinmemberMuaddubby11-Jan-08 13:22 
Questionhow to copy from dbf to MS Server Pinmemberleon9169-Dec-07 14:11 
AnswerRe: how to copy from dbf to MS Server PinmemberMuaddubby13-Dec-07 13:44 
GeneralDoesnt Copy Pinmemberahkhaja28-Nov-07 8:20 
GeneralRe: Doesnt Copy PinmemberMuaddubby28-Nov-07 8:28 
GeneralRe: Doesnt Copy Pinmemberahkhaja28-Nov-07 8:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 11 Jan 2008
Article Copyright 2007 by David Catriel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid