Queries written in VBA

[JCollins]

I can not seem to find the answer to this....

How do I name a query in a vba module for later use?

I wrote the SQL statement I wanted in a module, but I can not figure out how to use it as a part of another query's (a crosstab) source...

my fist query is written like:

strSQL = "SELECT field1, field2, field3 FROM tblTEST" '(Bogus names/fields, just to make it easy to read here)

Thanks! Jason

 

[Rich]

Why not save it as a query?

 

[raskew]

Function makeqry(qryName As String)
'*******************************************
'Name: makeqry (Function)
'Purpose: Create querydef from code
'Inputs: from debug window: ? makeqry("MyTestQry")<enter>
'*******************************************

Dim db As DATABASE, qd As QueryDef
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT field1, field2, field3 FROM tblTEST"
On Error Resume Next
'if query name already exists, delete it.
docmd.DeleteObject acQuery, qryName
Set qd = db.CreateQueryDef(qryName, strSQL)
db.QueryDefs.Refresh
db.Close
Set db = Nothing

End Function