Accessing Office Files in C#

As the world of computers begins to evolve, things start to change in the programs that we use and the ways that we use them. One shining example of this is the Jet driver from the Visual .Net languages. Depending on the version of Office that you use, you will have to configure things differently. If you use 2010 solely, you will need to use the Ace driver, but if you find yourself using older versions of Office you can still use the Jet driver. Using this one though needs a couple little tweaks to your IDE, but you can do it rather easily. If you receive the error message: Microsoft.Jet.4.0.OLEDB or Microsoft.ACE.12.0.OLEDB, you will need to configure your SDK to handle the database or spreadsheet commands that you need for your program.

To really understand what is going on with this error, you need to know a little about the drivers. The Jet driver is used for all versions of Office prior to 2010. The Jet driver is a 32 bit library. Since computers are evolving past 32 bits, the drivers need to evolve as well. Instead of upgrading the existing Jet driver, Microsoft has created the new Ace driver. Jet will not work on any program that is not compiled in a 32 bit atmosphere. The fact that Microsoft has made newer versions of Office still compatible with older versions, it is not yet required to upgrade your driver. You will, however, need to configure your program development environment to handle a 32 bit driver. This tutorial will be geared towards the new 2010 Visual Studio SDK's, but the same steps can be used for any of the .NET IDE's.

The first thing that you will need to do is determine which version of Office you want your computer to deal in. This will require you knowing your user base. If your users rely primarily on older versions of Office, you will want to continue to use the Jet driver. If you know that your users are solely using 2010 or newer, you will want to use the Ace driver. Use of the Ace driver may need to wait for a while since it will take several years for everyone to upgrade, but there is an alternative. You can use both. This is very difficult to debug, but it is possible. You can use two different buttons so that the user can choose which version they are using. The Jet driver will still work with 2010, but Ace will not work with earlier versions. This means that if you do target primarily 2010 users, you may still want to use Jet until older versions are phased out from most users.

Once you know which version you will use, you can start to configure your machine. If you are using Ace, it is rather easy. You just need to delete all pre-2010 versions of Office and install 2010. In your program, the setup is the same as the old Jet driver. Here is the code to use the driver:

string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + strFileLocation +

";Jet OLEDB:Database Password=" + strPassword;

OleDbConnection myOLEConnection = new OleDbConnection(strConnection);

The Ace driver is a little tricky though. If you are accessing a database the version number is 12.0. If you are trying to write or access an Excel file, you will have to use version 14.0.

Configuring your environment for the Jet driver is a little more difficult. The call to the driver is very similar to the above code. You will need to change the driver name and the version number though.

string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFileLocation +

";Jet OLEDB:Database Password=" + strPassword;

OleDbConnection myOLEConnection = new OleDbConnection(strConnection);

The simple part of this driver setup is that you only have to load the driver once. It will handle all of the Office products that you include in your program. You will need to configure the IDE to compile in 32 bit. When using Visual Studio 2010, you can do this in the in the Build menu. Open the Build dropdown and select the Configuration Manager. This will open the Configuration Manager Dialog box. Select the Platform drop down arrow. This will give you a new menu. You will need to select the new option. This will open another dialog box. This will allow you to select the x86 platform for your project. Under the x86 schema, your program can handle 32 bit operations. This will give you the ability to use the Jet driver.

Some easy configuration changes are all that you need to make to get your program 32 bit compatible. There are several things that you can do with 32 bit programs. Whether you are accessing older Office programs or have some libraries that deal in 32 bit, these steps can get you the access that you need to use them. Older SDK versions can use very similar steps to overcome these obstacles as well.