Ellis Web

Connecting to Excel and Access Files using .Net on a 64-bit Server

If you are trying to query a MS Excel (.xls, .xlsx, .xlsb) or MS Access (.mdb, .accdb) file on a 64-bit server and are getting one of the following error messages:

• The ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine.
• The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine.

Then it is necessary to install 2010 Office System Driver Beta: Data Connectivity Components on the server (the reason for this is the old Jet4.0 drover does not exist for 64 bit, and the ACE driver needed to read the newer formats is not installed by default). If you are using a version of Windows with UAC enabled, be sure to do this as Administrator.

After installing the components, use the following connection string formats (from the page linked-to above):

• Using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.14.0”. Example: “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Path_To_File”
• Using OLEDB and connecting to a Microsoft Office Excel file, add “Excel 14.0” to the Extended Properties of the OLEDB connection string defined in the previous bullet point.
• Using ODBC:
  • Connecting to Microsoft Office Access (.mdb or .accdb): set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
  • Connecting to Microsoft Office Excel (.xls, .xlsx, .xlsb): set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”