Access VBA Macro Programming

Click Create in the Access menu and then click the Table Design icon in the Table group

of the ribbon. Create a single text field calledQueryName. Save this astblSearchResults.

You do not need a primary key since it will only be used to hold the results of your search.

Add the following code to a module:

Sub SearchQueries()

Dim RecSet As Recordset, Qdef As QueryDef, SearchStr As String

CurrentDb.Execute "delete * from tblSearchResults"

Set RecSet = CurrentDb.OpenRecordset("tblSearchResults")

SearchStr = "orders"

For Each Qdef In CurrentDb.QueryDefs

If InStr(Qdef.SQL, SearchStr) Then

RecSet.AddNew

RecSet!QueryName = Qdef.Name

RecSet.Update

End If

Next Qdef

Set RecSet = Nothing

Set Qdef = Nothing

End Sub

This code sets up objects for a recordset, a query definition, and a string using theDim

statement. The tblSearchResults table is cleared out of any previous results using anExecute

statement.

TheRecordsetobject is then set to point to the table tblSearchResults and the SearchStr

is loaded with the word “orders”. This search word could be anything, such as a table name,

query name, or field name. Here you place whatever you want to search for within the query

collection.

This particular example will search for any query containing the word “orders”. In Access,

this is not case-sensitive, so “orders” will still return results in spite of the fact that the orders

table is called Orders.

The code then iterates through the query collection and uses theInstrfunction to test the

SQL for each query to see if the search string is present. If it is present, it then adds a new

record to the table tblSearchResults and sets the QueryName field in the table to the name of

the current query.

Run this code and then open the table tblSearchResults. This holds the names of all the

queries that contain your search string. You can now print the list out and work through it

deciding what action to take.

One word of warning: It is very dangerous in an Access application to delete a query or

table, no matter how sure you are that it is now redundant. It could have unforeseen

repercussions that you had not noticed and could cause immense problems in your

298 Microsoft Access 2010 VBA Macro Programming