Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Enumerating Queries (Access 2000)

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

    Thanks

  2. #2
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerating Queries (Access 2000)

    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 <font color=blue>checking a name for a certain string</font color=blue>
    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

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Enumerating Queries (Access 2000)

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

    <pre> 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
    </pre>

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating Queries (Access 2000)

    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 (<A target="_blank" HREF=http://www.rickworld.com>http://www.rickworld.com</A>) and use that to update the changed object names.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerating Queries (Access 2000)

    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.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Enumerating Queries (Access 2000)

    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:
    <pre>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
    </pre>

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

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Colorado, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerating Queries (Access 2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating Queries (Access 2000)

    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.
    Charlotte

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Enumerating Queries (Access 2000)

    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.

    <pre>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</pre>


  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating Queries (Access 2000)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    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
    Charlotte

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Enumerating Queries (Access 2000)

    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 "~".
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •