Search for a string in the form modules in multiple databases

<<Could someone give me guidance on how to do this?>>

 While certainly doable, if this is a one off ad-hoc type of thing, or will seldom be used, I would suggest just using a file editor that has an search function (like UltraEdit for example).

 Unless your DB's are encrypted, the editor would be able to see the string in the DB file fine without even opening the DB through Access.

But to more directly answer the question, you would use Dir() to loop through directories, locate DB's, then with DAO, do an OpenDatabase(), then loop through the modules collection and scan the module lines to find a string.

I've got the 1st part of that here (locating and opening DB's), which I can post.   The second half wouldn't be too hard to add.

I'd go the file editor route though rather then re-invent the wheel, especially if this was a one off.

Jim.

https://www.experts-exchange.com/questions/22127554/Extract-db-code-to-text-files.html?anchorAnswerId=18342952#a18342952
has code to recurse through a selected folder tree and get all form report and module code, and store to a database table and to text files.

In response to JDettman above:
I wrote the code shown below following your directions and it works great for the Standard Modules.  However it does not include the Form Class Modules.  What do I need to change to have it look at the Form Class Modules?

Private Sub FindString(SearchString)
On Error Resume Next
    Dim db As DAO.Database
    Dim ctr As Container
    Dim doc As Document
    Dim rs As DAO.Recordset
    Dim iselecfile
    Dim mdl As Module
    Dim lngSLine As Long, lngSCol As Long
    Dim lngELine As Long, lngECol As Long
    Dim strModule
   
    Set rs = CurrentDb.OpenRecordset("DatabaseModule")
    iselecfile = Dir("F:\AccessApps\Application Source\*.mdb")
   
    Do While iselecfile <> ""
        txtDataBaseName = "F:\AccessApps\Application Source\" & iselecfile
        Set db = OpenDatabase(txtDataBaseName)
   
        Set ctr = db.Containers("Modules")
        For Each doc In ctr.Documents
            doc.Properties.Refresh
            strModule = doc.Name
            DoCmd.OpenModule strModule
            Set mdl = Modules(strModule)
            If mdl.Find(SearchString, lngSLine, lngSCol, lngELine, lngECol) Then
                rs.AddNew
                rs!DatabaseName = txtDataBaseName
                rs!ModuleName = doc.Name
                rs.Update
            End If
        Next
       
        Set ctr = Nothing
        db.Close
        Set db = Nothing
        Set doc = Nothing
        iselecfile = Dir()
    Loop
End Sub

Your close, but a bit off.   You don't want to use the containers collection.  The containers collection basically is the grouping you see in the database container window.

What you want instead is the Module object, which is part of the module collection.  This collection contains all the modules in a DB, even those attached to a form or report. You'd then use the Find method of the module object to search for your string.

 Below is some code from the on-line help that will get you going.

Jim.

Function FindAndReplace(strModuleName As String, strSearchText As String, _
      strNewText As String) As Boolean
      Dim mdl As Module
      Dim lngSLine As Long, lngSCol As Long
      Dim lngELine As Long, lngECol As Long
      Dim strLine As String, strNewLine As String
      Dim intChr As Integer, intBefore As Integer, intAfter As Integer
      Dim strLeft As String, strRight As String

      ' Open module.
      DoCmd.OpenModule strModuleName
      ' Return reference to Module object.
      Set mdl = Modules(strModuleName)

' Search for string.
      If mdl.Find(strSearchText, lngSLine, lngSCol, lngELine, lngECol) Then
            ' Store text of line containing string.
            strLine = mdl.Lines(lngSLine, Abs(lngELine - lngSLine) + 1)
            ' Determine length of line.
            intChr = Len(strLine)
            ' Determine number of characters preceding search text.
            intBefore = lngSCol - 1
            ' Determine number of characters following search text.
            intAfter = intChr - CInt(lngECol - 1)
            ' Store characters to left of search text.

strLeft = Left$(strLine, intBefore)
            ' Store characters to right of search text.
            strRight = Right$(strLine, intAfter)
            ' Construct string with replacement text.
            strNewLine = strLeft & strNewText & strRight
            ' Replace original line.
            mdl.ReplaceLine lngSLine, strNewLine
            FindAndReplace = True
      Else
            MsgBox "Text not found."
            FindAndReplace = False
      End If

Exit_FindAndReplace:
      Exit Function

Error_FindAndReplace:

MsgBox Err & ": " & Err.Description

FindAndReplace = False
      Resume Exit_FindAndReplace
End Function

I wrote the code shown below to read through the modules in the active application.
It reads through all of the modules, including the class modules but it only finds the search string in some of them.  For example, I entered a search string of "Shell" and it only found it in three modules.  There are dozens of modules that have the word Shell in them.  Can anyone tell me what I am doing wrong?

Private Sub FindString3(SearchString)
    Dim i As Integer
    Dim modOpenModules As Modules
    Dim mdl As Module
    Dim rs As DAO.Recordset
    Dim lngSLine As Long, lngSCol As Long
    Dim lngELine As Long, lngECol As Long
     
     DoCmd.RunSQL ("DELETE DatabaseModule.* FROM DatabaseModule")
     Set rs = CurrentDb.OpenRecordset("DatabaseModule")
     Set modOpenModules = Application.Modules
     For i = 0 To modOpenModules.count - 1
        strModule = modOpenModules(i).Name
        Set mdl = Modules(strModule)
        If mdl.Find(SearchString, lngSLine, lngSCol, lngELine, lngECol) Then
            rs.AddNew
            rs!DatabaseName = txtDataBaseName
            rs!ModuleName = mdl.Name
            rs.Update
        End If
     Next
     rs.Close
End Sub

Your only getting the first instance in each module.   After the first call, you must set the start past the end to search the rest of the module.

Also, you'll want to clear the variables when hitting a new module.

Jim.

Jim,
   I applied the changes you suggested and it found the string in a lot more modules.  However, it still missed some.  I discovered that the problem is in the following line of code:
       For i = 0 To modOpenModules.count - 1
   When I run debug and check the value of modOpenModules.count it is 329.  There are over 700 modules in this database, most of them being form class modules.  So, my code quits searching when there are still around 400 modules left to search.  
   Any ideas on why modOpenModules.count does not pick up the correct count?
Dave

Dave,

  My apologies as I've given you a slightly bum steer.  It's been many years since I worked in this area and just didn't remember things right.

 The modules collection is for *open* modules, which means they have to be loaded.  The state doesn't matter, but they need to be loaded.  So unless you have a form or report open, you won't see those modules.

 But let me double check something before we go further.  I'm not sure if you actually need to open a form or report, or if you can just set a reference to the objects module and it will load.

 I believe the form or report needs to be open in some way (design or running), but let me double check.

Jim.

Jim,
Sorry for the slow response.
Thank you for the code sample.  I'm out of the office for a few days so can't test it right now but am anxiious to try it when I get back.
Will let you know as soon as I can try it.
Dave

Not a problem...enjoy the time off (or the travel maybe).   I did some limited testing here and it found everything I threw at it, so I believe it's good.

Jim.

Jim,
  I loaded your code and it works!  As you suggested, I still need to do a little tweaking.  It works good on smaller databases but on a large one (over 700 forms) it just sort of locks up. If I kill the process and then try to open the table that I am writing the search results to I get the message "System Resosurce exceeded".  If I exit out of access, go back in and check the table I see that it did actually find all occurances of the search string.
  Anyway, I think I've got something I can use.  Would still like to read through a list of databases as stated originally rather than just the current one but hopefully I can figure out how to do that.
  I really do appreciate all of the time and effort you put into this.  Thank you very much and have a Happy New Year!
Dave

<< It works good on smaller databases but on a large one (over 700 forms) it just sort of locks up.>>

 Try adding Set mdl = nothing on each pass.  See if that frees anything up.  Same thing with ctr.

  Beyond that, I can't think of anything else to solve that, other then to do multiple passes (ie. one for forms, one for modules, one for reports) and closing Access between each one.  There's no way to close a module once it's been loaded that I'm aware of.

<<Anyway, I think I've got something I can use.  Would still like to read through a list of databases as stated originally rather than just the current one but hopefully I can figure out how to do that.>>

1. Use the attached sample for searching directories.

2. For the CBF however, because the object needs to open in design view, you would need to start an instance of MSACCESS.EXE with OLE automation and then control that You would use OpenCurrentDatabase to open the db.  Would look like this:


Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase  "myDB"

then everything like opening a form/module:

objAccess.DoCmd.OpenForm doc.Name, acDesign, , , , acHidden

But your actually getting into some real nooks and crannies of Access and VBA.  Not sure how much sucess in the end you will have.

Jim.
FindDBs.mdb