Re: Create a query in code

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


"Ken Snell (MVP)" wrote:

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

--

Ken Snell
<MS ACCESS MVP>

"swas" <swas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:18CA7675-B8C7-467F-B9EA-11E2C9802ACA@xxxxxxxxxxxxxxxx
Hi,

I am dynamically creating a crosstab query for a data*** 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



.

Quantcast