OleDbException (0x80004005): Unknown

Question

I have the following code (part of a Windows Form) which connects to an Excel file successfully on my machine, but on a different box it fails.

var fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
    var extendedProperties = string.Empty;
    if (fd.FileName.Contains(".xlsx"))
    {
        // excel 2007 xml format file, IMEX = import data as text avoids data conversion errors
        extendedProperties = "Excel 12.0 Xml;IMEX=1";
    }
    else if (fd.FileName.Contains(".xls"))
    {
        // excel 2003 format file, IMEX: import data as text avoids data conversion errors
        extendedProperties = "Excel 8.0;IMEX=1";
    }

    var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fd.FileName + "';Extended Properties='" + extendedProperties + "'";
    using (var objXConn = new OleDbConnection(connectionString))
    {
        objXConn.Open();

        var selectStatement = string.Format("SELECT * FROM [tabName$] WHERE FormType IS NOT NULL");
        var dataTable = new DataTable("test");
        using (var objCommand = new OleDbCommand(selectStatement, objXConn))
        {
            var dataReader = objCommand.ExecuteReader();
            if (dataReader != null)
            {
                dataTable.Load(dataReader);
            }
        }
        using (var stringWriter = new StringWriter())
        {
            dataTable.WriteXml(stringWriter);
            this.textBox1.Text = stringWriter.ToString();
        }
    }
}

The behavior is reproducible using the OleDbDataAdapter rather than the OleDbCommand.ExecuteReader().

using (var dataAdapter = new OleDbDataAdapter(selectStatement, objXConn))
{
    dataAdapter.Fill(dataTable);
}

By failure, I mean getting the following error happens on the line dataTable.Load(dataReader);

When running/building the program with x86 configuration, I get the following error.

System.Data.OleDb.OleDbException (0x80004005): Unknown
   at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
   at System.Data.OleDb.OleDbDataReader.GetRowHandles()
   at System.Data.OleDb.OleDbDataReader.ReadRowset()
   at System.Data.OleDb.OleDbDataReader.Read()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
   at ExcelTest.Form1.button1_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

When running/building the program with AnyCPU/x64 configuration, I get the following error on both machines.

System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
   at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ExcelTest.Form1.button1_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

The Excel file being used for testing is the same file on both machines. I have been able to reproduce the issue with multiple files (both .xls ans .xlsx).

I have tried the following things to remedy the issue.

Installing the component described in 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine
Configuring the project to x86/x64
Uninstalling and Reinstalling 2007 Office System Driver: Data Connectivity Components
Uninstalling and Reinstalling Microsoft Access Database Engine 2010 Redistributable

What additional steps can I take to troublehsoot or resolve this issue?

so what line does it actually blow up on.. I ran into something similar lastweek and I changed my project properties from anyCPU to run x86 and it solved me similar issue.. — MethodMan, Jan 22 '15 at 22:11
Is the file the same on both machines? The error happens on the dataTable.Load call. — Steve, Jan 22 '15 at 22:13
@Steve I updated the question to include that the file is the same on both machines. — Ryan Gates, Jan 22 '15 at 22:13
have you thought about using a `SqlDataAdapter` or `OleDataAdapter` in your case..? instead of the `Load` you would utilize the `Fill()` method — MethodMan, Jan 22 '15 at 22:21
That is E_FAIL. Just a teacher's grade for the quality of the error reporting, it says absolutely nothing about the exact reason this failed. — Hans Passant, Jan 22 '15 at 22:22
@MethodMan I updated the question. It is reproducible using `OleDbDataAdapter` instead of `OleDbCommand`. — Ryan Gates, Jan 22 '15 at 22:35
I suggest to try removing rows until it works. Then check and double check the row that seems to be the reason of the failure. You are telling IMEX=1 but this is not always respected. http://www.etl-tools.com/imex1.html — Steve, Jan 22 '15 at 22:55

mybirthname · Accepted Answer · 2015-01-23T15:12:32.720

2

For first problem use OleDbDataAdapter and Fill() method or Update().

        DataTable newTbl = new DataTable()
        using(OleDbDataAdapter ad = new OleDbDataAdapter(
            @"SELECT * FROM [tabName$] WHERE FormType IS NOT NULL", objXConn))
        {
           OleDbCommandBuilder builder = new OleDbCommandBuilder(ad);

           builder.QuotePrefix = "[";
           builder.QuoteSuffix = "]";

           // Saves the data set
           ad.Update(newTbl);
        }

The OLEDB drivers for 32-bit and 64-bit applications are different.

If you only have the 32-bit driver installed, then 64-bit applications attempt to use it will get this error: Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. Similarly, if you have only the 64-bit version installed and 32-bit applications attempt to use it will get this error.

To understand what is happening you should check what is your application. I think this line line will help: Environment.Is64BitProcess

If the problem happens with xls(Excel 2003) file try to use JET connection string !

EDIT: Here is the link for both drivers:

edited Jan 23 '15 at 15:12

answered Jan 22 '15 at 22:56

mybirthname

17,949
3
31
55

Whare are the advantages of using `Fill` or `Update` vs `Load`? Is this contributing to the issue? I was able to reproduce the issue with `Fill`. I will try `Update`. How can I check which (32-bit vs 64-bit) drivers I have installed? I have installed those from the links. No matter which they are, shouldn't an x86 or x64 version work? Where can I find drivers for both 32-bit and 64-bit systems? I have been able to reproduce the issue for `xls` and `xlsx` files. – Ryan Gates Jan 23 '15 at 04:58
Here the link for http://www.microsoft.com/en-us/download/details.aspx?id=13255 both drivers. I will suppose that you have x32 bit drivers because you recieve this error when you run the project on x64. – mybirthname Jan 23 '15 at 08:30
I had to use the 32 bit version due to 32-bit Office being installed. Can you add the above link to the answer? Once I installed that, it seems to work. Can you add some detail as to why I would use OleDbDataAdapter and Fill() method or Update() rather than Load()? – Ryan Gates Jan 23 '15 at 14:40
The link was added, I use OleDbDataAdapter because it is more "flexible", than other approach. – mybirthname Jan 23 '15 at 15:15

OleDbException (0x80004005): Unknown

1 Answers1