Microsoft OLE DB Provider Jet for SQL 2005 64 bits not available

Miguel João

Staff

Question

Hi All

During a production upgrade from SQL Server 2000 to SQL Server 2005, I've come up with a small problem regarding DB Links to some Access database files (.mdb). The problem was that in the SQL 2005 running over a Windows 2003 64 bits, the Microsoft OLE DB Provider Jet 4.0 was not available.

After some digging, I've found out that this DB Provider is being deprecated over time. MDAC library since version 2.6 doesn't include the Jet Provider and no port has been, or is going to be, done for a 64 bits architecture, as stated in the microsoft article Data Access Technologies Road Map

Jet: Starting with version 2.6, MDAC no longer contains Jet components. In other words, MDAC 2.6, 2.7, 2.8, and all future MDAC releases do not contain Microsoft Jet, Microsoft Jet OLE DB Provider, or the ODBC Desktop Database Drivers.
...
Data Access Objects (DAO): DAO provides access to JET (Access) databases. This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®, and scripting languages. It was included with Microsoft Office 2000 and Office XP. DAO 3.6 is the final version of this technology. It will not be available on the 64-bit Windows operating system.

Depending of the type of access required to this Access databases, the workarounds could be many. In my case, we were able to eliminate the Access database from our equation, hence, discarding this problem totally. However, before that, we investigated several workarounds:

1) Using a 32 bits SQL intermediary server to connect to the Access database with the 32 bits Jet OLE DB Provider, connected to the SQL 2005 64 bits server through a SQL native client DB Link (between SQL servers). This solution could use views and stored procedures in the 32 bits SQL Server to access the Access database

2) Use a Linked table in the Access database file to the 64 bits SQL 2005, eliminating the Jet Provider need. This solution is however, dependent of the requirement for the Access database file. For instance, if an application opens this file for processing, it could generate some errors regarding the fact that is not a real table, but a linked one.

But the best approach is to eliminate the Access database requirement totally.

Best regards

Miguel João

01 Aug 2006

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines