INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

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"
        Me.txtSearchSQL.SetFocus
    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", _
        dbOpenDynaset)
    rs.MoveFirst
    
    Do Until rs.EOF
        strQdf = rs!Name
        
        With rsFilter
            .AddNew
                !DateCreate = rs!DateCreate
                !Name = strQdf
                !sql = db.QueryDefs(strQdf).sql
            .Update
        End With
        
        rs.MoveNext
    Loop
    
    Me.RecordSource = "SELECT * FROM tblQuerySQL"
    Me.Filter = "SQL Like '*" & Me.txtSearchSQL & "*'"
    Me.FilterOn = True
    
    
CloseFilter:
    rs.Close
    db.Close
    DoCmd.Hourglass False
    Exit Sub
    
    
ErrorHandler:
    Select Case Err.Number
        Case 3010
            db.TableDefs.Delete "tblQuerySQL"
            Err.Clear
            Resume
            
        Case Else
            MsgBox Err.Number & ": " & Err.Description
            Err.Clear
            GoTo CloseFilter
    End Select
End Sub

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

My Archive

Resources

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