Freigeben über

How to connect to file-based data sources (Microsoft Access , Microsoft Excel and Text files ) from a 64 bit application

 

The Issue:

A 64-bit process can load only 64-bit components in it's process boundary. Same is true for a 32-bit process also. So, if your application is 64 bit, you will need a 64-bit provider or driver to connect to  Microsoft Access (mdb, accdb) or Microsoft Excel 2010 (xls, xlsx, and xlsb) or  text files. Bad news is that there is no 64-bit provider or driver available "yet" to connect to these file-based data sources. Good news is that a 64-bit Provider is heading your way which is currently in beta phase.

 

The Kludge:

The common workaround is to connect to a 32-bit SQL Server instance that has a Linked Server to the Access/Excel/Text file. This is a hack, can be difficult to get set-up, and can have stability and performance issues, and realistically, we at Microsoft would rather not support this setup or issues arising from it.

 

The Good news:

 A 64-bit driver is headed your way. This is great news for users in a 64-bit world. Soon you'll be able to connect to these file-based data sources from your 64-bit application, rather than wrestle with obscure settings to force them to connect via a Linked Server.

 

The next version of Microsoft Office, Office 2010, will be available in a 64-bit version. This version will include a 64-bit version of "2010 Office System Driver Connectivity Components" which will include all the needed 64-bit ODBC driver and OLEDB providers to connect to these file-based data sources.

 

You will not have to buy or install Office 2010 to obtain and use the new 64-bit components. Like the current version of the provider, it will be available as a free download.

 

You can download the beta version from here:

https://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

 

Connection string for 64-bit OLEDB Provider:

  • For Microsoft Office Access : Set the Provider string to “Microsoft.ACE.OLEDB.12.0"
  • For Microsoft Office Excel : Add “Excel 12.0” to the Extended Properties of the OLEDB connection string.

 

Connection string for 64-bit ODBC Driver:

  • For Microsoft Office Access: Set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
  • For Microsoft Office Excel: Set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”

 

The gotchas:

  • You cannot install the 32-bit version and the 64-bit version of the "2010 Office System Driver Connectivity Components" on the same computer.
  • You cannot install the 64-bit version of the "2010 Office System Driver Connectivity Components" on a computer that already has the 32-bit Office 2007 ACE Provider. However, the 32-bit Office 2007 provider can coexist side-by-side with the 32-bit version of the "2010 Office System Driver Connectivity Components".

 

Authors: Enamul Khaleque & Srini Gajjela [DSD-SQLDeveloper group at Microsoft]

Comments

  • Anonymous
    September 27, 2012
    I have missed this useful post ( what's a shame for me ) .But it does not solve the problem of the many applications using Jet ( even if it is possible to change the driver and to use ODBC ) Even if it is very late, many thanks. A little reproach : it is impossible to have both 32-bit and 64-bit version on the same computer. To mitigage with your part The gotchas ( as it is written at the end of the post, it is possible that readers "forget" to read this article upto the end , but it will be their problem... ). I keep track of this post for the SQL Server Data Access Forum ( and because of that : THANKS )

  • Anonymous
    May 09, 2015
    very useful in SSIS

  • Anonymous
    December 14, 2015
    Why can’t the 32-bit and 64-bit be installed side-by-side? Why doesn’t the 64-bit version also install 32-bit interfaces/wrappers?How do I indicate if an excel file has a header row or not?

  • Anonymous
    March 07, 2016
    hi, how can i set priority to run queries.fore example i hav 2 select , 1 update , 1 insert commands from many users , and i want set priority to run queries in sqlserver ( first inserts , second updates , ... and last selects commands )

  • Anonymous
    April 17, 2017
    an example would be nice