Createquerydef in VBA

If you are like me you hate working on large databases with dozens or 100’s of queries. Access’s CreateQueryDef statement is a visual basic function which will clean up that growing list of queries and allow you to use form field variables with an SQL Server database back end.Note that SQL Server won’t let you pass a form field variable through to the server via a dynamically created query using this function. Createquerydef example follows below:

In this example we have a report selection/parameter form.  The form has a dropdown combo box which includes a description field for the report and a hidden field containing the name of the stored report definition.  The report Combo is called ‘Rpt_Cbo’.  There are also two report parameter fields; Start_Date and End_Date (in this example we aren’t using End_Date).  VBAfor the DAO version of the Access createquerydef code follows (see the ADO createquerydef version in the 2nd example):

Rpt_Cbo_AfterUpdate()

Dim sqltext As String
Dim qdfNew As DAO.QueryDef

Select Case Me.Rpt_Cbo.Column(1)
Case “AgencyReport”

‘  build the create querydef sql  string

sqltext = “SELECT ” & Chr(34) & ” Billing for ” & Chr(34) & ” & [dbo_APRsMonthly].[StartDate] & ” & _

Chr(34) & Through & Chr(34) & ” & [dbo_APRsMonthly].[EndDate] AS Description & _

” FROM (dbo_APRsMonthly INNER JOIN dbo_Boards ON     [dbo_APRsMonthly].[BoardID]=[dbo_Boards].[BoardID]) INNER JOIN dbo_Customers ON [dbo_APRsMonthly].[customerID]=[dbo_Customers].[CustomerID] ” & _

“WHERE (Month([startdate])=Month(” & Me.Start_date & “) AND Year([startdate]=Year(” & Me.Start_date & “)) ” & _

” ORDER BY [dbo_APRsMonthly].[InvoiceNumber];”
‘  now create a reusable stored query def
On Error Resume Next
‘  is run for the first time
With CurrentDb              ‘  it would be better to check to see if the
‘  querydef exists and then delete it
.QueryDefs.Delete (“My_Query”)
‘  createquerydef command line follows
Set qdfNew = .CreateQueryDef (“My_Query”, sqltext)
.Close
End With
‘  the recordsource of the report is called My_Query.
DoCmd.OpenReport “agencyconsolidatedinv”, acViewPreview

End Select

Aside: Another cool technique is to create a query definition using a global variable.  This way the user can never see the code in a protected database.

ADO Version of the CreateQueryDef Function

Below is the Access CreateQueryDef ADO version for creating a saved query using VBA:  Note: You’ll need to add a reference to the Microsoft ADO Ext. 2.1 for DDL and Security library.

Rpt_Cbo_AfterUpdate()
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim sqltext As String

‘ create the sqltext as in the example above

Set cat.ActiveConnection = CurrentProject.Connection
cmd.CommandText sqltext
Cat.Views.Append “My_Query”, cmd

With the Microsoft Access Create Query Definition statement all your reports and popup forms can use the same query record source. (Note that a database using the this technique could make maintenance and debugging more difficult)

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & SharePoint

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016