Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Unanswered: search in access 2007

    I want to be able to do a search in the sql of reports and queries to look for the following words separately:
    'delete', 'insert', 'update'.

    I have not had success in this area so. I do know that there are 'insert' statements becuase I can find them when I open up the queries.

    Thus can you tell me what I am doing wrong and/or tell me another way to find the individual words?

    Here are the steps I have completed so far:

    1. I open access 2007 and select the 'home' tab.
    2. I select queries in the navigation pane,
    3. In the 'search' box, I type the word 'insert'.
    After this point there is button to click on to say to complete the search.

    Thus can you tell me how in the sql I can find the words 'insert', 'delete' or 'update' in access 2007?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can paste this function into a module then call it:
    Code:
    Sub SearchQueries(Optional ByVal Words As String = "DELETE;INSERT;UPDATE")
    
        Dim varWords As Variant
        Dim qdf As dao.QueryDef
        Dim dbs As dao.Database
        Dim i As Long
        
        varWords = Split(Words, ";")
        Set dbs = CurrentDb
        For Each qdf In dbs.QueryDefs
            For i = 0 To UBound(varWords)
                If InStr(qdf.SQL, varWords(i) & " ") > 0 Then
                    Debug.Print qdf.Name
                    Debug.Print , qdf.SQL
                    Debug.Print
                End If
            Next i
        Next qdf
        Set dbs = Nothing
                
    End Sub
    Have a nice day!

Posting Permissions

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