Results 1 to 2 of 2
  1. #1
    goatamus is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    9

    Search through all Access queries for text in SQL string

    Hello all,


    I found this thread about creating a simple form with a text box and a button that would create a table with the SQL from every query in my database, so I could make changes to certain varibales and make sure I catch every instance of it to make the change as smooth as possible.

    I've tried doing what it says, but I can get it to work correctly. I notice the post is back from 2002, I'm working in Access 2007 so maybe there's something I need to tweak, I'm not quite sure.

    I'm still a little new to Access, only been using it for a couple months now, so if anyone has any insight on how I can tweak this to make it work in my 2007 Access database I would be extremely grateful.

    The instructions are as follows:

    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



    Thanks everyone!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    What 'variables' are you modifying - field names? This code handles query statements but what about references in controls on forms and reports and in code?

    Recreating the wheel. I use Rick Fisher's Find and Replace for this. Cost about $50. Saved my sanity more than once.

    Why doesn't it work - error message, wrong results, nothing happens?

    Have you step debugged? Refer to link at bottom of my thread for tutorial on debugging techniques.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Can access take one text string and split it into two?
    By hobsondm01 in forum Database Design
    Replies: 2
    Last Post: 06-09-2011, 09:09 AM
  2. Replace a string/text in ms-access db
    By anziga in forum Queries
    Replies: 4
    Last Post: 12-31-2010, 06:40 PM
  3. Search string for 2nd, 3rd, 4th... occurance
    By broecher in forum Queries
    Replies: 10
    Last Post: 10-26-2010, 12:03 AM
  4. Replies: 6
    Last Post: 10-14-2010, 08:33 AM
  5. How to Search a Text Field in Access
    By cnbhold in forum Access
    Replies: 1
    Last Post: 01-11-2010, 05:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums