CODEWITHSUNDEEP
vbams-access
Andrew G. Johnson
Andrew G. Johnson

Reputation: 27003

Create a query dynamically

Hi I need to create a query in MSAccess 2003 through code (a.k.a. VB) -- how can I accomplish this?

Upvotes: 20

Views: 56126

Answers (4)

Marc Guvenc
Marc Guvenc

Reputation: 81

You don't need to open the query, you can just write the first 2 lines,

strSQL="SELECT * FROM tblT WHERE ID =" & Forms!Form1!txtID Set qdf=CurrentDB.CreateQueryDef("NewQuery",strSQL)

Upvotes: 0

Romana Ahmad
Romana Ahmad

Reputation: 91

Dim strSql As String 'as already in example
Dim qdf As QueryDef 'as already in example

strSql = "SELECT * FROM tblT WHERE ID =" & Forms!Form1!txtID 'as already in example

On Error Resume Next
'Delete the query if it already exists
DoCmd.DeleteObject acQuery, "NewQuery"

Set qdf = CurrentDb.CreateQueryDef("NewQuery", strSql) 'as already in example
DoCmd.OpenQuery qdf.Name 'as already in example

'release memory
qdf.Close 'i changed qdef to qdf here and below
Set qdf = Nothing

Upvotes: 9

Oliver R.
Oliver R.

Reputation: 323

Thanks for this answer and the small piece of code. If somebody needs to define the datatypes for the variables used, use this:

    Dim strsql As Variant
    Dim qdf As QueryDef

Upvotes: 8

Fionnuala
Fionnuala

Reputation: 91376

A vague answer for a vague question :)

strSQL="SELECT * FROM tblT WHERE ID =" & Forms!Form1!txtID 

Set qdf=CurrentDB.CreateQueryDef("NewQuery",strSQL)
DoCmd.OpenQuery qdf.Name

Upvotes: 39

Related Questions