dsoderstrom
asked on
Search for a string in the form modules in multiple databases
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?
Could someone give me guidance on how to do this?
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.
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.
ASKER
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("D atabaseMod ule")
iselecfile = Dir("F:\AccessApps\Applica tion Source\*.mdb")
Do While iselecfile <> ""
txtDataBaseName = "F:\AccessApps\Application Source\" & iselecfile
Set db = OpenDatabase(txtDataBaseNa me)
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
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("D
iselecfile = Dir("F:\AccessApps\Applica
Do While iselecfile <> ""
txtDataBaseName = "F:\AccessApps\Application
Set db = OpenDatabase(txtDataBaseNa
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(strModuleNa me 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
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(strModuleNa
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
ASKER
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("D atabaseMod ule")
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
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("D
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.
Also, you'll want to clear the variables when hitting a new module.
Jim.
ASKER
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
ASKER
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
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.Appli cation")
objAccess.OpenCurrentDatab ase "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
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.Appli
objAccess.OpenCurrentDatab
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
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.