Enumerating Queries (Access 2000)

Topic

New Reply

How does one step through all the queries in a database checking a name for a certain string?

Thanks

Reply | Quote

Replies

Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb
For Each qdf In db.QueryDefs
If Left(qdf.Name, 4) “~sq_” Then
Debug.Print qdf.Name
End If
Next
Bill this will enumerate thru the queries, but I don’t understand what you mean by checking a name for a certain string
Are you looking at Field Names or at a specific field for a particular string in a string or all fields looking for a string in a string.
Paul

Reply | Quote

Thanks for the reply. That does exactly what I want. Some of my queries begin with qryEmpty. I need to go through the list and run only these. I know how to get that part, it was just a matter of geting the list. I guess I was a little surprised to find that “Queries” isn’t a member of the Containers collection nor is AllQueries a member of the CurrentProject object.

Should I take from the examples provided that there is no way to get this list using ADO instead of DAO?

Thanks again.

Reply | Quote

Hi,
Yes there is – you’ll need to use the Views collection of the ADOX.Catalog object (you may need to set a reference to MS ADO Ext. if you haven’t already). Something like:

Function listviews()
    Dim catCurrent As New ADOX.Catalog, vwTemp As ADOX.View
    Dim cnnCurrent As ADODB.Connection
    Set cnnCurrent = CurrentProject.Connection
    Set catCurrent.ActiveConnection = cnnCurrent
    For Each vwTemp In catCurrent.Views
        Debug.Print vwTemp.Name
    Next vwTemp
    Set cnnCurrent = Nothing
    Set catCurrent = Nothing
End Function

will print a list to the immediate window.
Hope that helps.

Reply | Quote

After asking the question, I discovered that AllQueries is a member of the CurrentData object. Here’s what I did and it seems to work.

dim dbs as Access.CurrentData
dim accQuery as Access.AccessObject

set dbs = Application.CurrentData

For Each accQuery in dbs.AllQueries
…
Next accQuery

this seems to work.

HTH

Reply | Quote

AllQueries is only available in an MDB. In an ADP, you have to use AllViews and AllStoredProcedures, since there are no queries in SQL Server.

Reply | Quote

Any idea why ADOX is so gosh-darned slow??

Anyway, I wanted to extract my queries from two databases for comparison, and I discovered that the Views collection did not include all of them. Here’s my new code. Because the text overflowed the Immediate window, I put it into a Word doc. Some manual futzing is going to be required to get all the queries into a table, side-by-side, but I think it’s manageable from here.

Sub ListAllInNewWordDoc()
' Requires references to:
'  ADOX - Microsoft ADO Ext. 2.x for ...
'  Word - Microsoft Word x.x Object Lib
Dim catCurrent As New ADOX.Catalog, vwTemp As ADOX.View
Dim procTemp As ADOX.Procedure, cnnCurrent As ADODB.Connection
Dim wrdApp As Word.Application, wrdDoc As Word.Document
 
' Set connection property to current DB ADODB Connection string
Set cnnCurrent = CurrentProject.Connection
Set catCurrent.ActiveConnection = cnnCurrent
 
' Connect up with Word, starting it if necessary
On Error Resume Next
Set wrdApp = GetObject(, "Word.Application")
If Err.Number = 429 Then    'Word was not open...
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Err.Clear
ElseIf Err.Number > 0 Then
    MsgBox Err.Number & ": " & Err.Description
    Exit Sub
End If
On Error GoTo 0
 
' Create a new document to copy queries into
Set wrdDoc = wrdApp.Documents.Add
 
' Retrieve standard Select queries
For Each vwTemp In catCurrent.Views
    wrdDoc.Range.InsertAfter "{StartQuery}" & vwTemp.Name & ":" & _
        vbCrLf & vwTemp.Command.CommandText & "{EndQuery}" & vbCrLf
Next vwTemp
 
' Retrieve Union, Summary, Make Table, Update, Delete, etc.
For Each procTemp In catCurrent.Procedures
    wrdDoc.Range.InsertAfter "{StartQuery}" & procTemp.Name & ":" & _
        vbCrLf & procTemp.Command.CommandText & "{EndQuery}" & vbCrLf
Next procTemp
 
'Clean up
Set cnnCurrent = Nothing
Set catCurrent = Nothing
Set wrdDoc = Nothing
wrdApp.Activate
Set wrdApp = Nothing    'does not quit Word
End Sub

Reply | Quote

The other collection you’ll have to work with is the Procedures collection. That’s where you’ll find the parameter queries and all the action queries. Interestingly enough, the names of all the QueryDefs can be found in the Tables collection.

Yes, ADO against the Jet provider is slow … and cumbersome when trying to deal with the Jet object model. DAO is optimized for Jet and is still faster for this kind of thing. Actually, the fastest way I can thing of would be something like: dbs.QueryDefs(strQryName).SQL

Reply | Quote

Just an additional suggestion. You might want to filter out the system created temporary queries by checking the name property to see if it starts with a “~”.

Reply | Quote

Not sure what you want to look for, but you can search each query’s SQL for a specified string using something like this:

 Sub SearchQuerySQL(strFindMe As String)

    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    Set db = CurrentDb
    
    For Each qry In db.QueryDefs
        If InStr(qry.SQL, strFindMe) > 0 Then
            Debug.Print "QUERY: " & qry.Name & vbCrLf & "SQL: " & qry.SQL
        End If
    Next qry
    
    Set db = Nothing
    Set qry = Nothing

End Sub

This will print both name of query and its SQL string where match is found. If you only need to identify the query, modify the Debug.Print statement accordingly.

Reply | Quote

If you’re trying to find the name of a table, field or other object so you can change it, you would be better off to get an add-in like Rick Fisher’s Find & Replace (http://www.rickworld.com) and use that to update the changed object names.

Reply | Quote