Create a query in code

[swas]

Hi,

I am dynamically creating a crosstab query for a datasheet in code, and this
is working OK. But I need a few extra columns from other tables, which
crosstabs themselves do not allow (Only one column header possible).

If the crosstab were static, I can have this as a saved query, then use
another query to bring in the crosstab and other columns needed. But creating
the crosstab dynamically means I would need to save this as a query, to
enable use in the second query...

Is there any way to save a SQL string as a query from code? Or is ther a
better way to approach the above problem?


Thanks in advance.


swas

 

[Ken Snell \(MVP\)]

Sure; here is generic example:

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String, strTemp As String

Const strQName As String = "ztempMyQuery"

Set dbs = CurrentDb

' Create temporary query and save it
strSQL = "Your crosstab SQL statement"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
' strQName holds the name of this temporary query

' Now put in code to do second query that uses above query
' (code goes here)

' Delete the temporary query
dbs.QueryDefs.Delete strTemp
dbs.Close
Set dbs = Nothing

 

[swas]

Ken,

Thanks for that. I haven't used querydefs before but had looked at them as a
solution, but couldn't work out how to have all columns generated from the
one query as below.

What I didn't realise is that you can use a created querydef as a normal
query, ie. in other queries etc...

Thanks for the help.


swas