Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Unanswered: "find" X in all Access queries

    Hey guys, anyone know a quick/easy way to search all of an Access db's queries for a certain string? Basically I just want to do a find, like in notepad/wordpad etc. (Search query1, query2 etc, containing "tblUser")

    Could I use an Access macro, or VB/asp code or something similar? Any ideas?

    Thanks!

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    robg69,

    I think this should do what you're looking for....

    Sub qdfcheck()
    Dim qdf As QueryDef, strsql As String, lngFound As Long
    Dim strSearch As String
    strSearch = "tblUser"
    'loop through all the queries
    For Each qdf In CurrentDb.QueryDefs
    'get the sql string for each query
    strsql = qdf.SQL
    'look for the compare string
    lngFound = InStr(1, strsql, strSearch)
    If lngFound > 0 Then
    'string was found, take whatever action here
    MsgBox qdf.Name
    End If
    Next
    Set qdf = Nothing
    End Sub
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2003
    Posts
    21

    Cool yea dude, you rock

    Worked like a charm, you rock! Hey some of the querys that came back contained: "~sq_" blah blah. Any idea what these are? They aren't in the query list, so are they system tables or something?

    Thanks!

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: yea dude, you rock

    No, these queries are actually deleted, but Access still shows them.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Jul 2003
    Posts
    21
    oh ok, cool. Thanks.

  6. #6
    Join Date
    May 2002
    Location
    London
    Posts
    87
    actually the "~sq_" results are returned where the search string if found the recordsource of a report or form or of a control on a report or form.

Posting Permissions

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