Cannot read Excel 2010 files with Jet.OLEDB.4.0

[RK62]

I have used OLEDB to read from closed Excel 2002 and 2003 files (.xls). Now when I have to start reading from Excel 2010 files (.xlsx) the connection fails.
For a connection string I use:

sConnectionString ="provider=Microsoft.Jet.OLEDB.4.0; data source='" & sSourceFile & "'; Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""

Reading works ok if the .xlsx file is open but fails if the file is closed.
I receive the error when trying to open the connection:

Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()

I use and develop with Win7 and 64-bit Office but the users of the application may have 32-bit Office and even WinXP or Vista.

Is there some problem to use the Jet provider with Excel 2010 files?

 

[jmcilhinney]

Go to ConnectionStrings.com - Forgot that connection string? Get it here! to get your connection strings. Note that there are separate entries for Excel and Excel 2007, i.e. XLS and XLSX files. The same goes for Access (MDB) and Access 2007 (ACCDB). You will see that you need to use the ACE OLE DB provider for the new format. Unlike Jet, ACE is not part of Windows. It must be installed separately, either with Access or on its own. The stand-alone install package is a free download.

Note that, unlike Jet and ACE 12, ACE 14 comes in both 32-bit and 64-bit flavours. The problem is, you can only have one installed at a time. If you have installed 64-bit Office on your development system then you will need to target Any CPU or x64 during development. When you are ready to deploy, you will have to make a decision. If you target x64 then the app won't run on 32-bit systems. If you target Any CPU then the app will run on all systems but won't work with 32-bit Office. If you target x86 then the app will run on all systems but won't work with 64-bit Office. The vast majority of users will have 32-bit Office, either on a 32-bit or 64-bit system. As such, you really must target x86 if you want to distribute one version. If you want to support 64-bit Office then you must distribute at least two versions.

 

[RK62]

Ok. I see I have to get the ACE OLEDB 12.0.
Two questions:
1) Do the users of the application (that includes ACE OLEDB 12.0) need to install separately anything? Or is it only me (as a developer) who has to install it?
2) If I target x64 as you mentioned, will it then always work with 32-bit and 64-bit Office?
3) Is it possilbe to write to Excel2010 with ACE OLEDB 12.0 ?

I could make a "64-bit Windows7-version" of the application and ensure the users of it always have a 64-bit Win7 but I would not like to care which version of Office (32-bit vs. 64-bit) they are using.
I already know that some users now have 32-bit Office but will later change to 64-bit and I try to avoid the situation where they are required to install a new application only due to change in Office.

 

[jmcilhinney]

1. To use OleDb in ADO.NET, you must have the specified OLE DB provider on the system. If you specify the ACE OLE DB provider in your connection string and the ACE OLE DB provider is not present, you will not connect.

2. If you target x64 then your app is 64-bit, so it will only work with 64-bit Office/ACE.

3. Yes.

Microsoft specifically recommend installing 32-bit Office unless you have a requirement for 64-bit Office. Installing 64-bit Office just for the hell of it is not recommended.

If you want to support both 32-bit and 64-bit Office then you have no choice but to build two versions. That's not a big deal as it's just a selection in a drop-down. You should target x86, which will support 32-bit Office on 32-bit and 64-bit systems, and x64, which will support 64-bit Office on 64-bit systems.

 

[RK62]

So I need to develop both x64 and x86 versions. Most of the users will have 32-bit Office so x86 is usually enough. I just have to make them know that if they in any day move to 64-bit Office, they have to install a new application.
By the way, is there a way to check through VB.net code which Office version is installed on the user's computer? The check should be made on opening the application.

 

[jmcilhinney]

You don't need to "develop" two versions per se. You just develop one project. When you want to deploy, you set the configuration to x86 and build, make a copy of the output, then set the configuration to x64 and build again.

You could probably look in the Registry somewhere to see what version of Office/ACE is installed but you could also simply catch the appropriate exception when you try to connect to the database.

 

[RK62]

Yes, I understand the build configuration. I'll try your suggestions.
Thanks for your response.