Conversion to Excel 2007: ACE.OLEDB.12.0 gets confused where Jet.OLEDB.4.0 was fine

[lovallee]

Hello,

***The workbook***
My workbook contains several Excel named data ranges organised as database tables.
I then use ADODB.Connection in order to use SQL SELECT statement to join these tables.
The SQL query results (ADODB.Recordset) then feed a PivotCache.Recordset object which feeds a Pivot table.
This workbook has always been working fine on Excel 2003 (using SQL engine Jet.OLEDB.4.0).

I now moved to Excel 2007 and therefore, I changed the connection string to:

= "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & MainSourceFile & ";" & _
                    "Extended Properties=""Excel 12.0;HDR=Yes"";"

***Excel 2007 returns an error where Excel 2003 was fine***
Using Excel 2007, I get a "Runtime error '-2147467259 (80004005)': Type mismatch in expression." error. This error usually occurs when trying to JOIN or UNION fields having different data types. However, in my case, my data types are all consistent as proven by the fact that the query runs fine with Excel 2003.


***What I have tested so far***
My SQL query contains the following section:
(SELECT * FROM Table1
UNION ALL SELECT * FROM Table2
UNION ALL SELECT * FROM Table3) AS Table1To3

While testing, I remarked that I don't get the above error if I only UNION ALL any two tables such as:
(SELECT * FROM Table1
UNION ALL SELECT * FROM Table2) AS Table1To3

or

(SELECT * FROM Table1
UNION ALL SELECT * FROM Table3) AS Table1To3

Therefore, it seems that ACE.OLEDB.12.0 gets confused with field data types when there are several UNION ALL while Jet.OLEDB.4.0 had no issue with that.

***Question***
Any hint as to how I could overcome the above bug?
Can I use Jet.OLEDB.4.0 with Excel 2007?



Many thanks!

Louis

 

[Norie]

Louis

Is it even possible to 'type' fields if Excel is the source?

Have you checked the data to see if there are any inconstistencies.

 

[lovallee]

Thanks Norie,

When connecting to Excel, both Jet.OLEDB.4.0 (Excel 2003) and ACE.OLEDB.12.0 (Excel 2007) assigns a data type to each field by scanning the first 8 rows of data. There are no inconsistencies in my data as proven by the fact that the SQL query works perfectly with Jet.OLEDB.4.0 (Excel 2003).

Louis

Is it even possible to 'type' fields if Excel is the source?

Have you checked the data to see if there are any inconstistencies.

 

[Norie]

Louis

That could be the problem - scanning only 8 rows just doesn't work all the time.

I'm sure you've witnessed that when attempting to import data from Excel into Access or SQL Server.

Sometimes it seems more of a 'guess' the data type thing.

One other thing I just noticed - you are using * to pick up all fields, could it be possible that's returning more fields than you want/need.

That's another incostistency I've come across, though using named ranges probably rules it out.

 

[lovallee]

Results from further testing:
I just found out that Jet.OLEDB.4.0 is available on Excel 2010.

So far:
I don't get the error message when using Jet.OLEDB.4.0 on Excel 2003.
It does not appear that Jet.OLEDB.4.0 is available on Excel 2007.
I don't get the error message when using Jet.OLEDB.4.0 on Excel 2010.

I get the error message when using ACE.OLEDB.12.0 on Excel 2007.
I get the error message when using ACE.OLEDB.12.0 on Excel 2010.

Conclusion: there is really a bug in ACE.OLEDB.12.0.
Anyone knows how to use JET.OLEDB.4.0 on Excel 2007?

 

[Norie]

You can't use Jet.OLEDB 4.0 in 2007 as far as I'm aware.

When searching I found some code for importing from different versions of Excel using OLEDB, the very first thing it did was set the provider according to version.

Have you looked into some of the suggestions I made?

 

[CmputrAce]

Have you solved your problem? I have an issue with ACE.12.0 as well. All my queries work until I run a UNION Query - which also works, but after the UNION query has been run, all other queries EXCEPT the UNION query break.

 

[lovallee]

To solve my issues, I had to change the provider to:

"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq= " & MainSourceFile & ";ReadOnly=0;"

But it remains more sensitive than Jet. For example, the query only works if the MainSourceFile (an Excel file) is closed.