Query Defs

QueryDefs correspond to queries in the Access interface. Though you can create dynaset-type recordsets based directly on queries, and for select queries that works fine, QueryDefs offer extra functionality: you can create a query on the fly, in VBA code (for example, to filter by a value entered or selected on a form), and then use that QueryDef as the data source for a recordset. You can even create a make-table query in code, and execute it to create a table, for circumstances where you need a table to work with. This eliminates the need for numerous filtered queries, and also lets you work around various problems with creating recordsets based on parameter queries.

The following procedure creates a QueryDef programmatically, using a SQL string as the data source, and returns the number of records; it is useful in determining whether there are any records in a filtered query, before taking an action. I call this procedure frequently in code in the sample databases for this book:

Public Function CreateAndTestQuery(strTestQuery As String, _ strTestSQL As String) As Long

On Error Resume Next

Delete old query:

Set dbs = CurrentDb dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler

Create new query:

Set qdf = dbs.CreateQueryDef(Name:=strTestQuery, _ sqltext:=strTestSQL)

Test whether there are any records:

Set rst = dbs.OpenRecordset(Name:=strTestQuery) With rst

.MoveFirst .MoveLast

CreateAndTestQuery = .RecordCount End With

ErrorHandlerExit: Exit Function

ErrorHandler:

If Err.Number = 3021 Then CreateAndTestQuery = 0 Resume ErrorHandlerExit Else

& "; Description: " & Err.Description Resume ErrorHandlerExit End If

End Function

Here is a typical code segment using the CreateAndTestQuery function:

strlnventoryCode = Me![InventoryCode] strQuery = "qryTemp" Set dbs = CurrentDb strSQL = "SELECT * FROM tbllnventoryltemsComponents _ WHERE [InventoryCode] = " & Chr$(3 9) _ & strlnventoryCode & Chr$(39) & ";" Debug.Print "SQL for " & strQuery & ": " & strSQL lngCount = CreateAndTestQuery(strQuery, strSQL) Debug.Print "No. of items found: " & lngCount If lngCount = 0 Then strPrompt = "No records found; canceling" strTitle = "Canceling"

MsgBox strPrompt, vbOKOnly + vbCritical, strTitle GoTo ErrorHandlerExit Else

'Further code here to work with the newly created query End If

The code creates a SQL string filtered by a value picked up from a form, and uses that string and a query name as arguments for the CreateAndTestQuery function. That function returns the number of records; if there are no records (the function returns zero), the code exits; otherwise, it can continue to perform some action on the query created by the CreateAndTestQuery function.

You can also create a QueryDef and use it directly to create a recordset, as in the following line of code:

Set rst = qdf.OpenRecordset

Argument names are inconsistently capitalized in VBA code. Regardless of how you type them in, when your cursor leaves the line of code, some argument names are capitalized (such as "Name") and some are not (such as "sqltext"). The capitalization does not match the capitalization of the arguments in their Help topics, where they are usually represented as all lowercase.

You can also create a QueryDef corresponding to an action query and run it directly from code, to create a table for use elsewhere in the code instead of a parameter query, to avoid errors that will occur if a query criterion is looking for a value on a form that is closed when the code runs:

strFilter = "[InvoiceDate] = #" & dteDue & "#" strSQL = "SELECT [InvoiceNo], InvoiceDate, _ Customer, Employee " _ & "INTO tmakMatchingRecords " _ & "FROM tblInvoices " _ & " WHERE " & strFilter & ";"

Debug.Print "SQL string: " & strSQL

Set qdf = dbs.CreateQueryDef(Name:=strQuery, _

sqltext:=strSQL) qdf.Execute

You can create a QueryDef without a name, using just double quotes, as in this line of code:

Set qdfTemp = dbs.CreateQueryDef(Name:="", _ sqltext:=strSQL)

However, I generally prefer to create a named query, so I can examine it in the interface for debugging purposes, if necessary.

0 0

Post a comment