Microsoft Access VBA Macro Programming

Qds.Append Qd

Application.RefreshDatabaseWindow

Set Qd = Nothing

Set Qds = Nothing

End Sub

This example assumes you do not already have a query set up called MyNewQuery. This

code can be run from any module within Access.

This code sets up two objects using theDimstatement:Qdto hold the new query

definition, andQdsto represent theQueryDefscollection for the database.

TheQueryDefname is set to MyNewQuery, and a simple SQL statement is added based

on the Orders table.

The new query definition is appended to theQueryDefscollection and the Database

window is refreshed so that the new query shows in the Navigation pane.

If you look at the list of queries, you will see that your new query is now set up and

available for use.

Deleting an Existing Query

You can also use VBA to delete queries from theQueryDefscollection. You can see what is

in this collection by looking at your queries within the Navigation pane of Access. The

QueryDefscollection reflects exactly what appears in the pane. There are no warning

messages when this happens, so beware of the use of this code:

Sub RemoveQuery()

CurrentDb.QueryDefs.Delete "MyNewQuery"

Application.RefreshDatabaseWindow

End Sub

This code simply deletes the query you created in the previous section and then refreshes

the database window so the query no longer shows in the Navigation pane.

Updating a SQL Query

You can easily change the SQL in an Access query by using the SQL property of the query

definition. You can effectively change the query type depending on what the SQL does:

Sub ChangeQuery()

CurrentDb.QueryDefs("MyNewQuery").SQL = _

"update orders set customer ='Company Unknown' where

customer='Company D'"

Application.RefreshDatabaseWindow

End Sub

294 Microsoft Access 2010 VBA Macro Programming