Working with Queries in Code - MS Access

Using the DAO, we can create temporary queries, create saved queries, execute queries, change the SQL in saved queries, and open Recordsets on queries. We can also manipulate queries with Parameters. Working with QueryDefs is an area in which you will probably spend a lot of time.

Inside the Database object, you will find the QueryDefs collection . This collection holds all the queries in your application.

The QueryDefs and Parameters collections.

The QueryDefs and Parameters collections.

The following code loops through the entire QueryDefs collection and displays basic information about each object:

Sub modQueryDefs_ListQueries() ' Example listing queries in the current database ' Also displays a list of any query parameters Dim db As DAO.Database Dim qdef As DAO.QueryDef Dim param As DAO.Parameter Set db = CurrentDb For Each qdef In db.QueryDefs Debug.Print "********************************" Debug.Print qdef.Name & " : " & qdef.SQL If qdef.Parameters.Count > 0 Then Debug.Print "----------------------------" End If For Each param In qdef.Parameters Debug.Print vbTab & param.Name, param.Type Next Debug.Print "********************************" Next db.Close Set db = Nothing Debug.Print "Completed : modQueryDefs_ListQueries" End Sub

Temporary QueryDefs

The first type of QueryDef to investigate is the temporary QueryDef. If you want to execute a block of SQL, there are several techniques that can be used.

Different methods for executing SQL

Access provides a number of different methods for executing SQL. It is possible to use a pre-saved query and execute the query by using either DoCmd.OpenQuery “qryname” (which we don’t recommend because you need to switch warnings off to prevent messages, and you can easily forget to switch them back on), or you could use CurrentDB. Execute “qryname”.

As an alternative you can construct the SQL in program code and then execute the code by using either CurrentDB.Execute strSQL or DoCmd.RunSQL strSQL, as shown in the following:

Sub modQueryDefs_ExecuteSQL() ' example showing direct execution of SQL Dim strSQL As String strSQL = "UPDATE Customers SET [ZIP/Postal Code] = Null " & _"WHERE [State/Province] = 'MA'" CurrentDb.Execute strSQL DoCmd.RunSQL strSQL Debug.Print "Completed : modQueryDefs_ExecuteSQL" End Sub

QueryDefs provide another method for executing SQL, and they can create temporary and permanent queries. Recordsets can also be opened on the QueryDef objects.

It is also possible to create a temporary QueryDef to perform this operation. The advantage of doing this is that you can then manipulate other more advanced query properties. For example, when working with SQL Server, you can create a temporary pass-through QueryDef to execute SQL against SQL Server, a simpler example is shown here:

Sub modQueryDefs_TempActionQuery() ' example showing how a temporary query can be created and ' executed in code Dim db As DAO.Database Dim qdef As DAO.QueryDef Dim strSQL As String Set db = CurrentDb Set qdef = db.CreateQueryDef("") strSQL = "UPDATE Customers SET [ZIP/Postal Code] = Null " & _"WHERE [State/Province] = 'MA'" qdef.SQL = strSQL qdef.Execute qdef.Close db.Close Set qdef = Nothing Set db = Nothing Debug.Print "Completed : modQueryDefs_TempActionQuery" End Sub

If you look at the qdef.Name property during execution, you will see it has the value #Temporary QueryDef#.

QueryDefs and Recordsets

It is also possible to create a temporary QueryDef by using a SQL statement or saved query, and then open a Recordset on the temporary object. This again becomes a very powerful technique when you are working with external sources of data, such as SQL Server:

Sub modQueryDefs_TempSelectQuery() ' example showing how a temporary query can be created and ' a recordset opened on the query Dim db As DAO.Database Dim qdef As DAO.QueryDef Dim strSQL As String Dim rst As DAO.Recordset Set db = CurrentDb Set qdef = db.CreateQueryDef("") strSQL = "SELECT * FROM Customers " & _"WHERE [State/Province] = 'MA'" qdef.SQL = strSQL Set rst = qdef.OpenRecordset(dbOpenDynaset) Do While Not rst.EOF Debug.Print rst!Company rst.MoveNext Loop rst.Close qdef.Close db.Close Set rst = Nothing Set qdef = Nothing Set db = Nothing Debug.Print "Completed : modQueryDefs_TempSelectQuery" End Sub

Creating QueryDefs

New QueryDefs can be created in program code by using the CreateQueryDef method of the database, as illustrated in the following:

Sub modQueryDefs_CreateQueryDef() ' example creating a querydef in code Dim db As DAO.Database Dim qdef As DAO.QueryDef Dim strSQL As String Set db = CurrentDb Set qdef = db.CreateQueryDef("qryCreatedInCode") qdef.SQL = "SELECT * FROM Customers" qdef.Close db.Close Set qdef = Nothing Set db = Nothing Application.RefreshDatabaseWindow Debug.Print "Completed : modQueryDefs_CreateQueryDef" End Sub

In addition to creating QueryDefs, you can also modify the SQL in existing QueryDefs, as follows:

Sub modQueryDefs_ChangeTheSQL() ' Example show that changes you make to the SQL in a query ' change the actual SQL stored in the saved query ' Notice that we do not need to perform a save as this ' happens automatically Dim db As DAO.Database Dim qdef As DAO.QueryDef Set db = CurrentDb Set qdef = db.QueryDefs("qryListCustomersChangedInCode") qdef.SQL = "SELECT Customers.Company, Customers.[State/Province] " & _"FROM Customers " & _" WHERE (((Customers.[State/Province])='IL'))" Stop ' Now open the query in design, look at the sql, then close the query qdef.SQL = "SELECT Customers.Company, Customers.[State/Province] " & _"FROM Customers " & _" WHERE (((Customers.[State/Province])='WA'))" Stop ' Now open the query in design, look at the sql, then close the query ' Now we try and enter some incorrect SQL, this will cause an error qdef.SQL = "SELECTxxxx Rubbish" Stop ' but the following will work even when a table does not exist qdef.SQL = "SELECT Rubbish" ' Access will syntax check your SQL but not check that the objects exist db.Close Set db = Nothing Debug.Print "Completed : modQueryDefs_ChangeTheSQL" End Sub

Changes to the SQL in a QueryDef need to use valid SQL, but not necessarily reference a valid object, and changes are automatically saved

When you change the SQL in a QueryDef, you do not need to save your changes, because they are automatically saved for you. In the previous example, you will see that if you try to change the SQL to invalid syntax, it will fail, but if you reference objects that do not exist it will not fail (until you execute or use the query).

QueryDef Parameters

The example that follows shows how you can populate parameter values in an existing parameterized query:

Sub modQueryDefs_ActionQuery() ' example showing how an existing parameterised query ' can be executed by program code Dim db As DAO.Database Dim qdef As DAO.QueryDef Set db = CurrentDb Set qdef = db.QueryDefs("qrySetZipForStateToNull") ' There are several equivalent ways to set a parameter qdef.Parameters!TheStateProvince = "MA" qdef.Parameters(0) = "MA" qdef.Parameters("TheStateProvince") = "MA" qdef.Execute qdef.Close db.Close Set qdef = Nothing Set db = Nothing Debug.Print "Completed : modQueryDefs_ActionQuery" End Sub

In a similar manner to referencing fields in a Recordset, there are several equivalent ways to refer to a parameter in a QueryDef.

Creating parameters in a QueryDef by using program code

If you want to create parameters in program code, you do this in the SQL and not with the Parameters collection (which is indicated as read-only in the Help system in so far as you cannot add or remove parameters, but only set the parameter value), as illustrated in the following example:

Sub modQueryDefs_ActionQueryParameters() ' example showing how create parameters ' can be executed by program code Dim db As DAO.Database Dim qdef As DAO.QueryDef Set db = CurrentDb Set qdef = db.CreateQueryDef("") qdef.SQL = "PARAMETERS TheStateProvince Text ( 255 ); " & _" UPDATE Customers SET Customers.[ZIP/Postal Code] = Null" & _" WHERE (((Customers.[State/Province])=[TheStateProvince]));" ' There are several equivalent ways to set a parameter qdef.Parameters!TheStateProvince = "MA" qdef.Execute qdef.Close db.Close Set qdef = Nothing Set db = Nothing Debug.Print "Completed : modQueryDefs_ActionQueryParameters" End Sub

The sample database contains a query called qryCustomersForCountry, which has a parameter tied to a control on the frmCustomersForCountry form. The following code shows how to programmatically use this query and supply the parameter from the form that is open:

Sub modQueryDefs_OpenParameterQuery() Dim db As DAO.Database Dim qdef As DAO.QueryDef Dim rst As DAO.Recordset Set db = CurrentDb DoCmd.OpenForm "frmCustomersForCountry" [Forms]![frmCustomersForCountry]![txtCountry] = "USA" Set qdef = db.QueryDefs("qryCustomersForCountry") Dim param As DAO.Parameter For Each param In qdef.Parameters 'Debug.Print param.Name, param.Value param.Value = Eval(param.Name) Next Set rst = qdef.OpenRecordset() Do While Not rst.EOF Debug.Print rst!Company rst.MoveNext Loop End Sub

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics