Accessing Excel 2007 Binary (.xlsb) via OleDb ACE

问题:

I found the Excel 2007 Binary format (with extension .xlsb) perfectly suitable for my needs, since it's fast to load and very compact. I deliver a bunch of reports in Excel that carry a lot of data, and those reports are actually being loaded with an IS package.

So I assumed the conversion to this very format, read documentation on Access Ole DB Provider 12, where is written that xlsb is supported by this provider. I converted the files in Excel, and then when trying to change them in the packages (just by changing the path of the file by adding the "b" suffix), I got the following error message:

"Test connection failed because of an error in initializing provider. This 
file was created in a previous beta version of Excel 2007.  Open the file 
with Excel 2007 to save it to the most recent version of the Excel 2007 file 
format before opening the file in Access 2007."

Well, I thought that it could be some problem with the conversion of the file, so I picked up a brand new one and filled it with some information. Tried again, same error!!

Then I tried the approach with ".udl" files, same error! Then I tried to test on different machines: - My own (Windows Vista SP1) - My colleagues (Windows XP SP2) - The IS Server (Windows Server 2003 x64 SP2)

And the error persists. I found it so easy to reproduce the error, that I'm a bit surprised that I didn't find anything about it by googling around!

Can somebody help me?

Thanks in advance, Rafael

回答1:

I know this is old post. I came across this issue few days ago and after lots of struggling, I'm able to resolve it. Hope it can help someone.

In my case, I have the same exact error:

This file was created in a previous beta version of Excel 2007. Open the file with Excel 2007 to save it to the most recent version of the Excel 2007 file format before opening the file in Access 2007."

This happen when I'm trying to open my OldDB Connection. I suspected it was due to my connection string syntax or value issue but nothing work after massive amount of changes.

I had the following installed in my machine:

1) Microsoft Access database engine 2010 Version 14.0.7015 (Get it from here)

2) Microsoft Office Access database engine 2007 Version 12.0.4581 (Get it from here)

And here is my connection string that causes the error to happen:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Project\Excel1.xlsb;Extended Properties='Excel 12.0;HDR={1}'"

And then I found this that resolve my struggles:

Microsoft Office Access Runtime and Data Connectivity 2007 Service Pack 2 (SP2).

I just need to download, run it and reboot my machine. And then voila, I'm able to open my OleDB connection without that error.

Hope this will help.