Solved

Search for a string in the form modules in multiple databases

Posted on 2013-12-19
13
453 Views
Last Modified: 2013-12-31
I have a directory containing several Access databases.  I would like to write an application in vba that would read through the databases in this directory.  For each database I would like to read through the form modules and search for a specified string.  If the string is found I would like to write the name of the database and the name of the form module to a table.
Could someone give me guidance on how to do this?
0
Comment
Question by:dsoderstrom
  • 7
  • 5
13 Comments
 
LVL 57
ID: 39729287
<<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.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39729862
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22127554.html#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.
0
 

Author Comment

by:dsoderstrom
ID: 39732018
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
0
 
LVL 57
ID: 39732059
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
0
 

Author Comment

by:dsoderstrom
ID: 39736084
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
0
 
LVL 57
ID: 39736777
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.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:dsoderstrom
ID: 39736980
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
0
 
LVL 57
ID: 39737000
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.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39740001
All right...turns out:

1. You do need to use the document collections.   AllForms and AllReports might be a better way to go, but using the document collections work for modules, forms, and reports.

2. A form or report must be opened to read it's module.  If you try and open the module first without the object being open, you get an error.

 So as a result, here's the code that works for me.  There's still some things you can do to it.  For example, on a form, should use .IsLoaded to figure out if it's already in an open state, in which case you'd just look at the module and then not close it.

 Also, you might want an option switch to check for each type (Modules, CBF, or both) when doing your search.  

 So with a possibly a few tweaks, this should get you going.

 Sorry for the delay and the run around.

Jim.

Public Sub FindString3(SearchString)
  Dim i As Integer
  Dim db As DAO.Database
  Dim ctr As Container
  Dim doc As Document
  Dim strModule As String
  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 db = CurrentDb()
  Set rs = db.OpenRecordset("DatabaseModule")

  ' Check forms
   Set ctr = db.Containers("Forms")
  For Each doc In ctr.Documents
    strModule = "Form_" & doc.Name
    DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
    If Forms(doc.Name).HasModule = True Then
    DoCmd.OpenModule strModule
    Set mdl = Modules(strModule)

    lngSLine = Empty
    lngSCol = Empty
    lngELine = Empty
    lngECol = Empty

    Do Until mdl.Find(SearchString, lngSLine, lngSCol, lngELine, lngECol) = False
      Debug.Print mdl.Name, lngSLine, lngSCol, lngELine, lngECol
      lngSLine = lngELine
      lngSCol = lngECol

      lngELine = Empty
      lngECol = Empty

      rs.AddNew
      'rs!DatabaseName = txtDataBaseName
      rs!ModuleName = mdl.Name
      rs.Update
    Loop
    End If
    DoCmd.Close acForm, doc.Name
  Next
 
  ' Check standalone standard and class modules
  Set ctr = db.Containers("Modules")
 
  For Each doc In ctr.Documents
    strModule = doc.Name
    DoCmd.OpenModule strModule
    Set mdl = Modules(strModule)

    lngSLine = Empty
    lngSCol = Empty
    lngELine = Empty
    lngECol = Empty

    Do Until mdl.Find(SearchString, lngSLine, lngSCol, lngELine, lngECol) = False
      Debug.Print mdl.Name, lngSLine, lngSCol, lngELine, lngECol
      lngSLine = lngELine
      lngSCol = lngECol

      lngELine = Empty
      lngECol = Empty

      rs.AddNew
      rs!DatabaseName = txtDataBaseName
      rs!ModuleName = mdl.Name
      rs.Update
    Loop
  Next
 
  rs.Close

End Sub
0
 

Author Comment

by:dsoderstrom
ID: 39742097
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
0
 
LVL 57
ID: 39742514
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.
0
 

Author Closing Comment

by:dsoderstrom
ID: 39749042
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
0
 
LVL 57
ID: 39749088
<< 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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now