Part II: Programming Microsoft Access
504
Creating a QueryDef in code is similar to creating a TableDef except that the new QueryDef
doesn’t have to be explicitly appended to the database’s QueryDefs collection:
Public Sub CreateNewQueryDef()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryDef(“MyQueryDef”, _
“SELECT * FROM tblCustomers”)
db.Close
End Sub
In fact, as soon at the CreateQueryDef method is executed, Access adds the new QueryDef to
the database. You must explicitly delete the QueryDef if you don’t want it to appear in the
Navigation Pane:
CurrentDb.TableDefs.Delete “QueryDefName”
You could, if desired, create a QueryDef without a name. In this case, the new QueryDef is not
saved and does not show up in the Navigation Pane. This technique might be useful, for instance,
if you’re filling a combo box or list box with data and you don’t want to create a permanent
QueryDef because the criteria changes every time the code is executed.
One time-honored advanced Access technique is dynamically changing an existing QueryDef
object’s SQL statement. Once the SQL property has been changed, the query returns the recordset
specified by the new SQL statement:
Public Sub ChangeQueryDefSQL()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(“MyQueryDef”)
qdf.SQL = “SELECT * FROM tblProducts”
End Sub
Notice that the ChangeQueryDefSQL procedure uses CurrentDb instead of declaring and instan-
tiating a Database object. Using CurrentDb in this fashion is easier than writing the more formal
method; it also eliminates the need to close the Database object at the end of the procedure.
It’s very easy to populate a DAO Recordset object directly from a QueryDef (see the next sec-
tion for more on the Recordset object). Notice how much simpler this procedure is than the
equivalent ADO process:
Public Function GetRecordset() As DAO.Recordset
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(“MyQueryDef”)
‘ Open Recordset with QueryDef:
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
rs.MoveLast
