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”
Tagged , , , , , , . Bookmark the permalink.

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

  1. Sarah says:

    I’ll try this out when i get the chance

  2. Ivn says:

    howto get it work from visual studio 2010?

  3. Ritesh says:

    It works for my local XP box but it still doesnt work for my Win Server 2003 Standard Edition box where we test our application. I am getting this error – “The ‘Microsoft.ACE.OLEDB.14.0′ provider is not registered on the local machine.”

    I installed x86 version of the driver downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

    I would appreaciate any input on this.

  4. Adrian says:

    I’m using the new 64bit Office 2010 drivers with provider name Microsoft.ACE.OLEDB.12.0.

    Ritesh Note there is a problem with the provider name Microsoft.ACE.OLEDB.14.0 is not registered but if you use Microsoft.ACE.OLEDB.12.0 it works.

    Anyhow, it all works ok for a while reading CSV files and excel sheets etc ok but then throws a “The provider reported an unexpected catestrophic failure”

    My environment is 64-bit Windows 7 running VM Workstation, with a 64-bit Windows 7 guest running SQL Server 2008 64 Bit. Essentially I’m running the following which works… occasionally getting the failure above

    select top 50 * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,’Text;Database=c:\temp\captell;HDR=Yes’, ‘SELECT * FROM [DASDGIGS0013046591395.TXT]‘)

    I’ve also tried this on Windows server 2003 64bit with SQL Server 2008 64 bit and get the same error.

    Has anyone else had this problem?

  5. raymond says:

    Hi, i did follow the step and it worked fine;My only worry is that when i published the site on the production server the reference to the driver are not fount ;Is here anyways that i can add reference to the driver so that it can work on the production server.
    Thanks

  6. Naresh says:

    Hi ,

    I am facing same problem,above mentioned by raymond.Is there is one to help me :(

  7. Naresh says:

    Hi ,

    Please help me ????

  8. Raja sekaran says:

    Am having error

    ‘The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine.!’

    in vista operating system 32 bit processor

    is there any solution from retrieve this problem please help

  9. This has been a right pain for me today.

    As above, install the driver, but also make sure you change the target to x86…

    1. Install AccessDatabaseEngine.exe from http://www.microsoft.com/en-us/download/details.aspx?id=13255
    2. Change the connection string in your program to “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + your file
    3. Change the target of the application to x86

Leave a Reply