Diagnosing an OLEDB exception when Quering Excel 2010

Question

To query an excel sheet via SQL, I used to use either:

Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""

or

Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + strPath + ";Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;"""

Now this worked fine until I installed Office 2010.

Now I get a

Microsoft.Ace.OLEDB.12.0 provider is not registered on this machine exception.

How can I find out the correct connection string/provider?

JDunkerley · Answer 1 · 2009-09-22T20:02:03.137

7

I believe for Excel 2010 it is :

Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=D:\\MyDocs\\oledb.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:Engine Type=37"

This appears to work in my visual studio, I got Excel to generate the query string and it had the extra entry in it.

edited Sep 22 '09 at 20:02

answered Sep 22 '09 at 16:23

JDunkerley

12,355
5
41
45

I ran into this same situation today and your answer was very helpful – Tony Peterson Sep 18 '12 at 12:11
Wish I could upvote this 10 times. Just spent a good few hours on this problem and your answer solved it. Thank you. – Sergey Koulikov Nov 22 '12 at 07:04

Andrei Drynov · Answer 2 · 2010-02-03T23:57:23.343

I downloaded and installed Office System Driver: Data Connectivity Components as advised above - and the below code worked:

    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Password=\"\";User ID=Admin;Data Source=d:\\Sample.xlsx;Mode=Share Deny Write;Extended Properties=\"HDR=YES;\";Jet OLEDB:Engine Type=37";

    OleDbConnection connection = new OleDbConnection(connectionString);

    try
    {
        connection.Open();

        OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.SelectCommand = command;

        DataSet ds = new DataSet();
        adapter.Fill(ds);

        GridView1.DataSource = ds;
        GridView1.DataBind();

    }
    catch (Exception)
    {            
        //throw;
    }
    finally
    {
        connection.Close();
    }

How come I don't get any results back. It doesn't throw me any exception though. — Meidi, Nov 01 '12 at 21:21

score 1 · Accepted Answer · answered Sep 23 '09 at 08:41

1

Perhaps you uninstalled the Access Database Engine (ACE) components? They are still available for download from MSDN as 2007 Office System Driver: Data Connectivity Components.

answered Sep 23 '09 at 08:41

onedaywhen

55,269
12
100
138

Diagnosing an OLEDB exception when Quering Excel 2010

3 Answers3

Linked