Running SQL in visual Basic

D

Dale Campbell

Hello all,

I am working with Access '97. I need to create a visual basic script that
will create many different cross tabulations and then save each individual
cross tab as excel file. Below is an example of the script I am trying to
run. It is only one set of the script which I will duplicate for each cross
tab.

It will not let me run the script as I have not put a INSERT INTO or CREATE
TABLE statement.

DoCmd.RunSQL " TRANSFORM Count(RSI.respid) AS CountOfrespid " _
& " SELECT RSI.site " _
& " FROM RSI " _
& " GROUP BY RSI.site " _
& " PIVOT RSI.survey;", -1
DoCmd.OutputTo acQuery, , "MicrosoftExcel(*.xls)",
"d:\review\crosstab\r1.xls", False, ""

I am sure this not even on right track so if anyone please give guidance as
to how I can improve my query so that it will run it would be greatly
appreciated.

Regards,

Dale Campbell
 
R

Roger Carlson

DoCmd.RunSQL will only work with action queries, i.e. those that do
something to the data and do not return a recordset. What you have to do is
create a QueryDef, which is a saved query. Then you can export it.

Something like this (untested):

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

strSQL = "" TRANSFORM Count(RSI.respid) AS CountOfrespid " _
& " SELECT RSI.site " _
& " FROM RSI " _
& " GROUP BY RSI.site " _
& " PIVOT RSI.survey;"

Set qdf = db.CreateQueryDef("NewQuery", strSQL)
DoCmd.OutputTo acQuery, "NewQuery", "MicrosoftExcel(*.xls)",
"d:\review\crosstab\r1.xls", False, ""
End Sub

On my website (www.rogersaccesslibrary.com) there is a small sample database
called "CreateQueries2.mdb" which illustrates how to create saved queries
programmatically. Also you might want to look at the sample "DAO.mdb" and
look at the Class 14 module.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Top