Excell VBA

Hi Cameron. In VBA you need to use the Add method of the "QueryTables" collection object. Your example query suggest you're transferring data from Access to an Excel worksheet, but you can connect directly to an Access database from within other MS Office packages, although there are some stability (and coding!) issues. Here is a basic example lifted off the net:

Sub CreateQT()

Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable

sConn = "ODBC;DSN=MS Access 97 Database;"
sConn = sConn & "DBQ=C:\Program Files\Microsoft Office\"
sConn = sConn & "Office\Samples\Northwind.mdb;"
sConn = sConn & "DefaultDir=C:\Program Files\Microsoft Office\Office\Samples;"
sConn = sConn & "DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

sSql = "SELECT Customers.CustomerID, Customers.CompanyName, Customers.City "
sSql = sSql & "FROM `C:\Program Files\Microsoft Office\Office\Samples\Northwind`"
sSql = sSql & ".Customers Customers "
sSql = sSql & "WHERE (Customers.City='Berlin') "
sSql = sSql & "ORDER BY Customers.CompanyName"

Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("a1"), _
Sql:=sSql)

oQt.Refresh

End Sub

In the above case (which is attaching to an Access databse) your SQL query ends up as a string variable which is passed to the QueryTables object. The same thing applies for databases built in Excel, but coding connection is obviously much easier.

This is some basic code for the Excel VBA query:

http://www.dbforums.com/microsoft-excel/907427-export-sql-query-excel-vba-easier-way.html

...and this goes into more detail:

http://www.excelguru.ca/node/23

Hope this helps,

Anthony