Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Other topics FAQ

VBA and Custom Functions

Search through all Access queries for text in SQL string by GeekGirlau
Posted: 23 Jul 02

As a contractor working predominantly on databases I haven't developed, it can be a daunting task trying to find references to tables, fields or functions within a list of several hundred queries.

The following code is attached to a command button on a simple form listing all queries in the database. The record source for the form is

  SELECT DateCreate, Name FROM MSysobjects WHERE Type=5 ORDER BY DateCreate DESC

There is a text box where the user enters the text to search for. The code builds a table containing the SQL string of every query, then searches for the required text, and displays those that match.

Your form also needs a procedure the set the record source back to the default.

There are off-the-shelf applications such as Speed Ferret which perform this sort of function, however some employers are too cheap to purchase them!

sub cmdFilter_Click()
    Dim db As Database
    Dim rs As Recordset
    Dim rsFilter As Recordset
    Dim tdf As TableDef
    Dim strSQL As String
    Dim strQdf As String
    On Error GoTo ErrorHandler
    If Me.txtSearchSQL = "" Then
        MsgBox "You must enter some search criteria.", _
             vbInformation, "No Search Criteria"
    End If
    DoCmd.Hourglass True
    strSQL = "SELECT DateCreate, Name FROM"
    strSQL = strSQL & " MSysobjects WHERE Type = 5"
    strSQL = strSQL & " ORDER BY DateCreate DESC"
    Set db = CurrentDb()
    Set tdf = db.CreateTableDef("tblQuerySQL")
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    With tdf
        .Fields.Append .CreateField("DateCreate", dbDate)
        .Fields.Append .CreateField("Name", dbText)
        .Fields.Append .CreateField("SQL", dbMemo)
    End With
    db.TableDefs.Append tdf
    Set rsFilter = db.OpenRecordset("tblQuerySQL", _
    Do Until rs.EOF
        strQdf = rs!Name
        With rsFilter
                !DateCreate = rs!DateCreate
                !Name = strQdf
                !sql = db.QueryDefs(strQdf).sql
        End With
    Me.RecordSource = "SELECT * FROM tblQuerySQL"
    Me.Filter = "SQL Like '*" & Me.txtSearchSQL & "*'"
    Me.FilterOn = True
    DoCmd.Hourglass False
    Exit Sub
    Select Case Err.Number
        Case 3010
            db.TableDefs.Delete "tblQuerySQL"
        Case Else
            MsgBox Err.Number & ": " & Err.Description
            GoTo CloseFilter
    End Select
End Sub

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close