Access to Excel via SQL

Keywords:   Access Excel SQL

I apologize in advance if you get bored reading this post until the good part at the end. I have finally achieved an OLE DB Transfer methodology that even according to Microsoft Documentation is not supposed to be possible.

First, the problem: With Microsoft DAO, it was pretty simple to export tables or queries from ACCESS to EXCEL - you simply had to invoke the DoCmd Object, then execute TransFerSpreadsheet(). Unfortunately, all that is missing from OLE DB, and there have been complaints from users that the Microsoft strategy is to eliminate support for ISAM's ( or flat files, including dBASE, ParaDox, .wks, Excel, Foxpro ). A second problem is that even using TransFerSpreadsheet(), a physical Table or Query must exist in the Access Database prior to translation. According to the DOCS - "you cannot use TransferSpreadsheet() with an on-the-fly SQL Statement.

My Situation: I am moving more from applications to data-mining scripts written in Winbatch . Recently, there have been major changes in telecommunication rate structures, and therefore the focus of my analysis has changed. To accomplish this, I do move data from DB2, Oracle and DBF files into temporary Access Databases/tables. Why? because I am guaranteed the functionality of Jet 4.0 OLE DB.

The Users: could care less about Access or FW, they want their queries in Excel, where they can sort,graph,report and analyze the raw data to suit their own needs. Even new hires in our department can do more with Excel (in terms or presenting data) than I can. Therefore, it is pointless for me to re-invent that wheel.

So, I have this 'on-the-fly' Access Database created - and need to perform SQL queries to send different results to up to 50 users in Excel. Using DAO (which would work) would be a nightmare!!!

I always felt that the SELECT INTO... command was the answer, but everything I tried failed with OLE Exception errors. At last I got it.

Assume:

ACCESS Database = C:\TEMP\TEMP.MDB
Table = TEMP
Desired user Excel file is C:\TEST.XLS

so in OLE DB

DB := ObjectOpen("ADODB.Connection")
cConn := "Provider=MicroSoft.Jet.OLEDB.4.0; Data
Source=C:\TEMP\TEMP.MDB"

to create a spreadsheet only involves two lines of code

DB.Open(cConn)
RS := DB.Execute(cSQL)

so what is cSQL???

cSQL:= "SELECT * INTO [Excel 8.0;DATABASE=C:\TEST.XLS].[TEST] FROM [TEMP]"

where the .[TEST] = the worksheet name (it could be "Sheet1" )

change Excel 8.0 to dbase III, or Text and you can output your data to DBF, comma-delimited or SDF. When working with Text output, ACCESS looks for a file called schema.ini so if you create one like

[MyFile.TXT]
FORMAT=DELIMITED(|)

your get comma-delimited output, or

[MyExport.DAT]
FORMAT=FixedLength
Col1=MyCustomers Text Width 15
Col2="My Address Field" Text Width 30

can completely customize the output.

Finally, the SQL is not limited to a full table - WHERE clauses, GROUP BY, PIVOT can all be used.

Sample Code: ISJET.WBT:

;The attached script can be easily modified to a subroutine to 
;check for the existence of an OLE DB Provider on a machine prior
;to executing ADO commands.
;
;The script uses the registry and returns the GUID's of the
;major Microsoft Providers.
;
;Another was to do this would be to perform an enumeration,
;but that code would have to be included in the OLE Extender 
;- and while we are on that subject, could the OLE extender
;have the QueryInterface() function added
;
;QueryInterface( GUID, Address)
;
;which returns an HRESULT of S_OK if the Interface is supported.
;Because much of OLEDB is nor required, sending the GUID for the
;COMMAND interface would ascertain if a particular Provider
;supported Commands - just a 'add to suggestion list'
;
;stan littlefield 


;////////////////////////////////////////////////////////////////////////////////
;//     WINBATCH - DETERMINE if MICROSOFT OLEDB Provider is Present
;////////////////////////////////////////////////////////////////////////////////
;//     Queries the Registry for Existence of the GUID for three of the
;//     Microsoft OLEDB Providers
;//
;//     Key Values should be as Below
;//     HKEY_CLASSES_ROOT\Microsoft.Jet.OLEDB.4.0\CLSID 
;//        - {dee35070-506b-11cf-b1aa-00aa00b8de95}
;//
;//     HKEY_CLASSES_ROOT\MSDASQL\CLSID 
;//        - {c8b522cb-5cf3-11ce-ade5-00aa0044773d}
;//
;//     HKEY_CLASSES_ROOT\Microsoft.Jet.OLEDB.3.51\CLSID 
;//        - {dee35060-506b-11cf-b1aa-00aa00b8de95}
;//
;//     The OLEDB 3.51 is not a real useful set of drivers and DLL's but
;//     exists for compatibilty with DAO Objects
;//
;//     MSDASQL is the Generic OLE DB for ODBC Drivers and existing DSN's
;//
;//     Stan Littlefield 07/22/2000
;////////////////////////////////////////////////////////////////////////////////

ret = strcat("OLEDB Provider Registry Information",@CRLF,@CRLF)
var = "MSDASQL"

If RegExistKey(@REGCLASSES,var) == @TRUE
   ret = strcat(var," [GUID] - ",RegQueryValue(@REGCLASSES,strcat(var,"\CLSID")),@CRLF)
Else
   ret = strcat(var," - Not Found",@CRLF)
Endif


var = "Microsoft.Jet.OLEDB.3.51"

If RegExistKey(@REGCLASSES,var) == @TRUE
   ret = strcat(ret,var," [GUID] - ",RegQueryValue(@REGCLASSES,strcat(var,"\CLSID")),@CRLF)
Else
   ret = strcat(ret,var," - Not Found",@CRLF)
Endif


var = "Microsoft.Jet.OLEDB.4.0"


If RegExistKey(@REGCLASSES,var) == @TRUE
   ret = strcat(ret,var," [GUID] - ",RegQueryValue(@REGCLASSES,strcat(var,"\CLSID")),@CRLF)
Else
   ret = strcat(ret,var," - Not Found",@CRLF)
Endif


Message("GUID Values For Microsoft OLEDB Providers",ret)

exit

Article ID:   W14686

Filename:   Access to Excel via SQL.txt

File Created: 2001:02:15:11:30:42

Last Updated: 2001:02:15:11:30:42

Database Search

TechHome

Access to Excel via SQL

Sample Code: ISJET.WBT: