Failure when opening Excel file via ADO.NET on some computers

Hello - I am writing a little Excel-to-SQL Server business tool for a client. It simply lets the user open an Excel spreadsheet with a named area defined, select one or more rows from a datagrid, and transfer the information to some SQL Server tables. Works like a champ on my PC, and 2 of my colleagues PCs, but not my clients. The situation:

Using Oledb namespace in ADO.NET for .NET Framework 1.1
Standard Jet driver - I have XP SP2 which has something better than the most recent MDAC.
Office 2003 installed, including MS Access.

The root exception thrown is this: "This application has failed to start because msaccess.exe was not found. Re-installing the application may fix this problem." Huh

I've made sure that my client (on Win XP SP1) has installed the latest MDAC and Jet updates. Still getting the same error...every time. I'm at a loss. I've even tried reverting to ODBC.NET with the exact same results. I built a very simple test app. Here's the relevent code snippet - not exactly rocket science!

    Private Function LoadExcel(ByVal ExcelFile As String, ByVal NamedRange As String) As DataTable
        Dim daXls As OleDbDataAdapter
        Dim cnXls As OleDbConnection
        Dim cmdXls As OleDbCommand
        Dim strSelect, strConn As String
        Dim intRecordsRetrieved As Integer

        Try
            'Add a unique column to use for subsequent selection operation
            Dim dt As New DataTable("ExcelData")
            dt.Columns.Add("ID", GetType(System.Int32))

            strConn = "Provider=Microsoft.Jet.OleDb.4.0;data source=" & ExcelFile & ";Extended Properties=Excel 8.0;"
            strSelect = "SELECT * FROM [" & NamedRange & "]"
            cnXls = New OleDbConnection(strConn)
            cmdXls = New OleDbCommand(strSelect, cnXls)

            cnXls.Open()
            daXls = New OleDbDataAdapter(cmdXls)
            daXls.Fill(dt)

            LoadExcel = dt

        Catch ex As Exception
            Throw New Exception("Excel import failed for the file " & ExcelFile, ex)

        Finally
            If Not (cnXls.State = ConnectionState.Closed) Then
                cnXls.Close()
            End If
        End Try

    End Function

Any ideas what I might be missing I think the most perturbing part is that it works fine on all other machines I've tested it on.

Thanks, folks.

Dylan

Answer this question

Failure when opening Excel file via ADO.NET on some computers

The MaiN MaN

Interesting call stack! Could it be something specific to the Excel file in question Is it a user-provided file, or one that you also have locally for testing

Since it's happening during the connection open, you can also try to connect via a DataLinks file to simplify things somewhat. You could try that before a console app even, although a console app would be easy to test. The DataLinks file will use the OLE DB provider outside of .NET.

For a UDL test, create a new text file on the desktop, and rename it test.udl. Make sure it doesn't stick a .txt on the end. Double-click the newly-created file, and select the Jet provider on the Provider tab. Fill in your connection info on the Connection tab, and on the All tab, under Extended Properties, add Excel 8.0 just like in your app's connection string. Then click the Test Connection button on the Connection tab. This will confirm that it's not app specific, although I doubt it is. Since it's not even getting to the point of selecting from the named range, you could do this test with a brand new Excel file to see if that has the same problem.

Thanks,
Sarah
sun.Xiang
Sarah - Thanks for the quick response. Let me give the console route a try. As I said, it's working everywhere except my client so I'll WebEx with him tomorrow to test that approach. The error is in response to a button click in my test app. Here's the stack trace:

...at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()

Not terribly useful but definitely leads me to believe that it's OleDb and not something else. The System.Data.Odbc version bombs similarly (...at System.Data.OdbcConnection.Open()). And I agree, the whole "msaccess.exe" error is a red herring - it shouldn't have anything to do with it.

More to follow no doubt

Dylan
Adremoshier

I have compared the versions of the following file in both computers (the one that was working and the one that's not)...

C:\Program Files\Microsoft Office\OFFICE11\MSAEXP30.DLL

... and I saw that both versions were different. The file with minor version was not working, so I decided to replace it with the newer one.

Surprise! It worked.

Whats the glitch on this dll file (Microsoft Access Expression Builder)

Best regards,
alert('hi');

Wow, that's really weird. Is this by any chance related to this posting on another forum: http://forum.manifold.net/Site/Thread.aspx id=21591&ti=632841129096300000

I'm asking because that's the only other reference I could find to this error anywhere externally *or* internally within past customer reports, bugs, etc. The format of the error is typical when an application reference (such as a DLL) is missing, but I can't find any other references that include msaccess.exe in the error.

Are you saying that if you take your sample function above, stick it in a brand new console application (no references beyond the default), add a call to it like the following, you still get the error:

Imports System.Data.OleDb
Imports System.Data

Module Module1

Sub Main()
Dim dt As DataTable = LoadExcel("d:\databases\excelTest.xls", "NamedRangeTable")
End Sub

...snipped LoadExcel implementation, just copied directly from your post

End Module

Is there a call stack associated with the error, or does it happen as soon as a particular process is launched I would expect it to be at launch time, but your description sounds like the user may be performing some action before this happens, in which case you may have a stack.

Aside from the fact that you say Access is on the machine, it shouldn't be looking for it anyway if you're using using OleDb, so that is the most curious part. One thing you could do is use the FileMon tool from http://www.sysinternals.com to see if it's looking for Access even on the working machines, and just happens to be finding it and not failing. That would help narrow down if it's doing something completely different on the client machine, or if it's doing the same thing and just happens to work in your environment.

Thanks,
Sarah
Confused999
That's a good question. The answer is no, there is no network or mapped drives involved. I've been testing in C:\Temp\test.xls and my client has been testing in something similar on the C drive (no spaces in the path, 8.3 compliant).

But...here's the good news. I just convinced the system administrator to at least try installing XP SP2 on one machine and it actually solved the issue! I have no idea why, except that SP2 installs the latests and greatest Jet driver and MDAC above and beyond the individual downloads available on MSDN.

So, I think this one can be classified as fixed, but not solved/completely understood Thanks for your input on the question. The suggestions most definitely put me on the right track.

Dylan
Raidx

Is the path to the Excel file on the client any different than what you have been testing with For example, is it located on a network path by any chance

Typically the ISAM error indicates one of two possible problems. First, the connection string is in the incorrect format and second, the ISAM driver is missing or not properly registered. Since the client is unable to link to an Excel Worksheet through Access I'm assuming it's likely an issue with the Excel ISAM driver installation. I just wanted to eliminate the possibility that the network is involved.
Robert Burger

Just another suggestion, similar to Sarah's, try linking or importing an Excel Worksheet from the Workbook to an Access database. This should validate the ODBC and Excel driver configuration if the link/import is successful.
Sjako
Paul, Sarah,

Thanks for your suggestions thus far. I've been trying everything and more. Here's what I've arrived at:
- The console application exhibits exactly the same behavior as before. However, when debugging on my clients machine we get the additional message "Could not find installable ISAM.", which apparently has been the root cause all along.
- I tried the UDL test, and it works for me, and fails for my client with the same ISAM message.
- My client tried to import the Excel spreadsheet in to Access and that causes Access to choke every single time. On any spreadsheet. Leads me to believe that .NET is not the culprit, and it's all about the Jet configuration at this point.
I did research on the whole ISAM thing and found a couple of approaches, both of which have been tried and eliminated as fixes:
- This blog post describes how quoting the Extended Properties of the connection string corrects the error. Not in this case!
- Many other references online such as KB283881 describe adjusting the registry to ensure that the Excel component of the Jet Engine is configured correctly. I actually did this first on my machine and was able to get the exact same error as my client. I simply mispelled the name of the driver so make sure that it couldn't be found:
- My client's PC has all of the same configurations and is getting the ISAM error regardless of registry tweaking.
Whew! Lots to take in :-) The short version is that there's something up with the Jet configuration on the client PC. It seems to be the same on every PC at his place, so I'm open for suggestions. Can't think what to try next. The good news It isn't my code that's breaking

Hope someone can help.

Dylan
vinaya
It could also be that SP2 just fixed something that was wrong with the configuration of the Excel ISAM or Jet itself. It may not necessarily have been a higher version that actually contained a fix for the problem. In any case, I'm glad to hear it's working now.
TACOBOB

Hi everybody.

I tell you that I'm having the same problem here, one year after this thread was created.

I saw all posts in this thread, and tried a lot of things to make it work. I have noticed that the 'Extended Properties' property of the connection string must be enclosed in double quotes ("), but even so the problem was not solved (http://msdn2.microsoft.com/en-us/library/ms254500(VS.80).aspx).

Unlike our thread creator did, the SP2 installation did not solved the problem.

Any clues

Thanks everybody in advance.
Failure when opening Excel file via ADO.NET on some computers

Answer this question