Solved: ACE.OLEDB and SELECT INTO [Excel

[stanl]

I have been unable to find a specfic 'bug' or logical answer to an issue I encountered with the Microsoft.ACE.OLEDB.12.0 Provider and Office 2010, specifically Access 2010. You can replicate the issue with a 2010 Access file of your choice unless someone can provide a means to for to upload my file (which exceeds the upload size)

1. I have a test .accdb file [test2010.accdb] in a directory C:\temp which has a table USZip with city, state, zip fields, and running on Win7 32-bit.

2. Because it is 2010, you cannot use the Jet 4.0 drivers but instead use the Ace Provider, so the following works

[vba]
cConn='Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\temp\test2010.accdb'
oConn=CreateObject("ADODB.Connection")
oConn.Open cConn
[/vba]

3. Upon opening the .accdb file, I want to export a query to an Excel file with SQL. I have 2 choices for the file I want to create

cXLS="c:\temp\NCZip.xlsx"

or

cXLS="c:\temp\NCZip.xls"

and 6 choices for the SQL statement

cSQL='SELECT * INTO [Excel 12.0;DATABASE=c:\temp\NCZip.xls].[NC] FROM [USZip] WHERE state="NC";'
cSQL='SELECT * INTO [Excel 12.0;DATABASE=c:\temp\NCZip.xlsx].[NC] FROM [USZip] WHERE state="NC";'

or

cSQL='SELECT * INTO [Excel 8.0;DATABASE=c:\temp\NCZip.xls].[NC] FROM [USZip] WHERE state="NC";'
cSQL='SELECT * INTO [Excel 8.0;DATABASE=c:\temp\NCZip.xlsx].[NC] FROM [USZip] WHERE state="NC";'

or

cSQL='SELECT * INTO [Excel 14.0;DATABASE=c:\temp\NCZip.xls].[NC] FROM [USZip] WHERE state="NC";'
cSQL='SELECT * INTO [Excel 14.0;DATABASE=c:\temp\NCZip.xlsx].[NC] FROM [USZip] WHERE state="NC";'

when I code

[vba]
oConn.Execute cSQL

[/vba]

4. Choices 5 or 6 won't work - although Office 2010 is version 14.0, there is no 14.0 Isam available - I think 14.0 is for 64bit

Using 8.0 will work with the .xls file, but it will have to be saved manually as .xlsx when loaded into Excel 2010. If you use 8.0 to output to .xlsx it won't load due to compatibility between format and extension.

Using 12.0 will create an .xls, you will get a warning that there is a compatability issue, but it will load.

now the problem

Using 12.0 to create an .xlsx (which is what you would normally do) causes a compatability issue and will not open in Excel 2010.

I have re-installed the Ace Drivers for 32-bit. I do a lot of scripting where I create Excel files from Access queries via SQL 'SELECT INTO' - don't need the extra steps of loading..saving as .xlsx... then deleting the .xls

I have tried to make the issue as clear as possible - cannot create a usable .xlsx file from an .accdb table using ACE.OLEDB

really appreciate some input on this.

TIA
Stan

[stanl]

..tried this outa frustration ... works like a charm

cSQL='SELECT * INTO [Excel 12.0 xml;DATABASE=c:\temp\NCZip.xlsx].[NC] FROM [USZip] WHERE state="NC";'

"There is no Do...just Try" [Yoda's Uncle, the one that drinks too much]