Find string into macros

[AlexTardif]

Hey there,

I have a form that search for a string I enter in all the forms/modules, the queries and the reports in my Access database. It returns me the name of the Access objects in which the string has been found.

So, I can see where a certain field name or table name or query is used.

Now, I'd like my form to also search into the macros but, I can't figure out how. So it could tell me if a certain query is opened by one of my macros.

Anyone can help?

Alex

 

[dhookom]

Tony Toews has a listing of third party utilities.
Crystal Long also has an analyzer.

Duane
Hook'D on Access
MS Access MVP

 

[AlexTardif]

Hi,

Thanks for the links but, nothing is matching what I'm looking for.

But, I found a way to get the information from the macros.

It's probably not the most optimal but that's the only solution I got.

Sub cmdSEARCH_Click()
Dim cnxLocal As ADODB.Connection
Dim rstForm As ADODB.Recordset

Dim objAccess As AccessObject

Dim oFSO As FileSystemObject
Dim oFS As TextStream

Dim strFileContents As String
Dim strName As String

   Set cnxLocal = CurrentProject.Connection
   
   Set rstForm = New ADODB.Recordset
   rstForm.Open "SELECT * FROM tbl_SEARCH", cnxLocal, adOpenDynamic, adLockOptimistic

   For Each objAccess In Application.CurrentProject.AllMacros
         'If the string is found in the macro name
         If InStr(1, objAccess.Name, txtWord.Value) > 0 Then
            rstForm.AddNew
            rstForm!NAME_QRY = objAccess.Name
            rstForm!TXT_QRY = objAccess.Name
            rstForm.Update
         End If
    
         strName = objAccess.Name
         If Len(strName) <> 0 Then
            'Save the content of the macro in a file
            Application.SaveAsText acMacro, strName, "c:\temp.txt"
               
            Set oFSO = New Scripting.FileSystemObject
            Set oFS = oFSO.OpenTextFile("c:\temp.txt")
            strFileContents = ""
               
            Do Until oFS.AtEndOfStream
               strFileContents = strFileContents & oFS.ReadLine
            Loop
               
            Set oFS = Nothing
            Set oFSO = Nothing
            Kill "c:\temp.txt"
            
            'If the string is found in the macro text
            If InStr(strFileContents, txtSEARCH.Value) > 0 Then
               rstForm.AddNew
               rstForm!NAME_QRY = objAccess.Name
               rstForm!TXT_QRY = objAccess.Name
               rstForm.Update
            End If
         End If
      Next objAccess
End Sub

Hope this helps someone else...

Alex