Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

Create Query Definition Visual Basic
Save Query Text
Create Stored Query in VBA






Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Denormalize Records
Stock Quotes
CreateQueryDef
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
FindFirst
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions


Visual Basic Function Examples


CreateQueryDef in Microsoft Access 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.



You did not know that nearly 100 cable and satellite tv installers use our software to manage their device inventory. They never lose track of an expensive device.

Did you know that most template databases often do not do exactly what you need done? We can create a custom template that fits your needs exactly.


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):

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)










Blue Claw Database Design Downloadable Tutorial:
How To Open Email Using SendObject Access Download  








A Blue Claw Software Design Template:

Student Grant Program Administration Software
 










A Blue Claw Database Design Article:

ODBC Links To Other DB Systems
 

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


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016