ADO to connect Excel to Excel - OLEDB Providers?

[lovallee]

Hello,

For a while, I have been using ADO to use Excel as a database (i.e. link several Excel tables using SQL and return the results in an Excel pivot table).

With Excel 2003, I have been using one of the following connection string:

MyConnectionString2003 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                               "Data Source=" & MainSourceFile & ";" & _
                               "Extended Properties=""Excel 8.0;HDR=Yes"";"

OR

MyConnectionString2003 = "Driver={Microsoft Excel Driver (*.xls)};Dbq= " & MainSourceFile & ";ReadOnly=0;"

I understand both connection strings uses the JET OLEDB provider. They both works fine.



With Excel 2007, I have tried:

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

OR

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

I understand both connection strings uses ACE OLEDB provider. Both works with simple SQL queries but fails with more complex queries (10+ tables). However the same complex queries were processed ok with the JET OLEDB provider.


Question:
Is there another OLEDB provider than ACE in Excel 2007 and if yes, what is/are the connection string(s)?

Thanks.

Louis