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.
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.
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). VBA
for the DAO version of the Access createquerydef code follows (see the ADO createquerydef version in
the 2nd example):
Dim sqltext As String
Dim qdfNew As DAO.QueryDef
Select Case Me.Rpt_Cbo.Column(1)
' 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
' createquerydef command line follows
Set qdfNew = .CreateQueryDef ("My_Query", sqltext)
' the recordsource of the report is called My_Query.
DoCmd.OpenReport "agencyconsolidatedinv", acViewPreview
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
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim sqltext As String
' create the sqltext as in the example
Set cat.ActiveConnection = CurrentProject.Connection
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)