stanl
01-12-2011, 07:31 AM
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
cConn='Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\temp\test2010.accdb'
oConn=CreateObject("ADODB.Connection")
oConn.Open cConn
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
oConn.Execute cSQL
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
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
cConn='Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\temp\test2010.accdb'
oConn=CreateObject("ADODB.Connection")
oConn.Open cConn
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
oConn.Execute cSQL
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