How to create dynamic query ?

[cheer]

My form is going to generate a dynamic SQL in VBA . I need the SQL to create a dynamic query so that I can use DOCMD to call the query in later part.. Anyone familiar with this kind of code ?

Upon finish the execution, I need the query to be removed. How to do this ?

 

[JANR]

Rather than creating and deleting objects and thus bloat your db, create an empty queryshell and use DAO.QueryDef to change the SQL of your query.

Ex:

Function DynamicQuery()
Dim qdf As DAO.QueryDef
Dim strSQL As String
 
strSQL = "Select .......;"
Set qdf = CurrentDb.QueryDefs("MyQuery")
qdf.SQL = strSQL
DoCmd.OpenQuery "MyQuery"
End Function

JR

 

[cheer]

Rather than creating and deleting objects and thus bloat your db, create an empty queryshell and use DAO.QueryDef to change the SQL of your query.

Ex:

Function DynamicQuery()
Dim qdf As DAO.QueryDef
Dim strSQL As String
 
strSQL = "Select .......;"
Set qdf = CurrentDb.QueryDefs("MyQuery")
qdf.SQL = strSQL
DoCmd.OpenQuery "MyQuery"
End Function

JR

If second user query result with different criteria but first user still holding the previous data, how this will affect the first user ?

 

[JANR]

It woulden't affect another user provided that your database is split which all multiuser databases must be.

See this site for why this is important :http://www.kallal.ca/Articles/split/index.htm

JR