Access Analyzer & Documenter

[Peter Hallett]

One thing that would be of great use to me is a really effective Access
analyzer and documenter. The on-board utility leaves much to be desired. Of
particular value would be a utility that lists the usage of all objects. One
of my databases is now quite large. It has been under development for a
number of years and, despite my best efforts, undoubtedly contains a
significant amount of debris. I am sure that not all of the 400 or so
queries are still in use, for example, and I would have great difficulty in
trying to establish the function of each one, or the use of every table.
Meanwhile, what all the procedures and functions do is anyone’s guess! Which
of the latter are passed as query criteria, for instance, and to which
queries? Deleting, or renaming, them to see what happens is not a rewarding
exercise. Variables are a little easier to track but not if you have to
chase them individually.

Does anyone know of any really good commercial Access tools that would do
the job? Several are featured on the Web but the eye-watering price of one
or two of them had me reaching for the tissues. It is not that I object to
paying for a first class tool but to part with several hundred pounds, or
dollars, for something that turns out not to do what I want would represent a
less than happy experience.

Surprisingly, few of the authors, or distributors, of this sort of product
seem interested in discussing, or promoting, their wares, or are prepared to
offer a functional trial version. It is more often a case of, “Show me first
your money.†One trial version I did acquire appeared to have been supplied
by a deaf vendor, when I tried to ask some pre-sales questions.

Has anyone got any suggestions or experiences to share?

 

[Dirk Goldgar]

One thing that would be of great use to me is a really effective Access
analyzer and documenter. The on-board utility leaves much to be desired.
Of
particular value would be a utility that lists the usage of all objects.
One
of my databases is now quite large. It has been under development for a
number of years and, despite my best efforts, undoubtedly contains a
significant amount of debris. I am sure that not all of the 400 or so
queries are still in use, for example, and I would have great difficulty
in
trying to establish the function of each one, or the use of every table.
Meanwhile, what all the procedures and functions do is anyone’s guess!
Which
of the latter are passed as query criteria, for instance, and to which
queries? Deleting, or renaming, them to see what happens is not a
rewarding
exercise. Variables are a little easier to track but not if you have to
chase them individually.

Does anyone know of any really good commercial Access tools that would do
the job? Several are featured on the Web but the eye-watering price of
one
or two of them had me reaching for the tissues. It is not that I object
to
paying for a first class tool but to part with several hundred pounds, or
dollars, for something that turns out not to do what I want would
represent a
less than happy experience.

Surprisingly, few of the authors, or distributors, of this sort of product
seem interested in discussing, or promoting, their wares, or are prepared
to
offer a functional trial version. It is more often a case of, “Show me
first
your money.†One trial version I did acquire appeared to have been
supplied
by a deaf vendor, when I tried to ask some pre-sales questions.

Has anyone got any suggestions or experiences to share?

I don't know if it does *everything* you want, but you should check out FMS,
Inc.'s "Total Access Analyzer". Maybe you already have, as it's pretty
expensive and may have contributed to your "eye-watering".

 

[Peter Hallett]

Thanks for the advice, Dirk.

It was the price of the product you mentioned that had me reaching for the
tissues but if it does its job that is all that really matters. The
eye-watering stops after a while. I will follow up the advice.

 

[Larry Kahm]

You should be able to download and use the FMS product for 30 days. Analyze
your database and see if it tells you what you want to know.

The product has standard 30 day support for a purchased copy, and available
paid (albeit expensive) support options.

I've used the product for several years and appreciate that it points out
the previous developers' mistakes - as well as my own!

I run a weekly "build" for analysis prior to delivering any components to my
clients to ensure that I'm not inadvertently releasing any inconsistencies.

Larry

 

[Peter Hallett]

Thanks Larry. The FMS product certainly appears to be the one to go for and
the price is not as eye-watering as I originally thought. For some reason I
was looking at the five-user version. A recheck shows that I will have to
find $299 rather than $899 – suggesting a need for a much smaller box of
tissues.

As I pointed out earlier, I am hoping to answer the question, “What are
these 400 odd queries used for and where?†They are easy enough to find in
the VBA code but very much harder to associate if they provide the source for
a form, for example. The usage of functions that are passed as criteria to
queries is also very difficult to establish ‘manually’.

From what has been said, I am hoping that Total Access Analyzer will at
least perform these tasks. In the mean time, to paraphrase the well known
invitation, “If anyone knows of any just cause or impediment …,†then do
please speak out. The hammer is now poised above my piggy bank.

 

[Dirk Goldgar]

Thanks Larry. The FMS product certainly appears to be the one to go for
and
the price is not as eye-watering as I originally thought. For some reason
I
was looking at the five-user version. A recheck shows that I will have to
find $299 rather than $899 – suggesting a need for a much smaller box of
tissues.

As I pointed out earlier, I am hoping to answer the question, “What are
these 400 odd queries used for and where?†They are easy enough to find
in
the VBA code but very much harder to associate if they provide the source
for
a form, for example. The usage of functions that are passed as criteria
to
queries is also very difficult to establish ‘manually’.

From what has been said, I am hoping that Total Access Analyzer will at
least perform these tasks. In the mean time, to paraphrase the well known
invitation, “If anyone knows of any just cause or impediment …,†then do
please speak out. The hammer is now poised above my piggy bank.

Being a cheapskate, I've written a couple of procedures of my own to answer
questions of this sort. They're rough and unpolished, but maybe you'll find
them useful and enable you to preserve the piggy bank a little longer:

'------ start of code ------
Sub SearchRecordAndRowSources(strSought As String)

' Search the recordsources of all forms, and the rowsources of all combo
and list boxes,
' for the specified string.
'
' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.

On Error GoTo Err_SearchRecordAndRowSources

Dim db As DAO.Database
Dim doc As DAO.Document
Dim frm As Form
Dim ctl As Control

Dim varTest As Variant
Dim lngFormCount As Long
Dim lngControlCount As Long
Dim lngFoundCount As Long
Dim lngControlFoundCount As Long

Debug.Print "*** Beginning search ..."

Set db = CurrentDb
For Each doc In db.Containers("Forms").Documents
DoCmd.OpenForm doc.Name, acDesign, WindowMode:=acHidden
Set frm = Forms(doc.Name)
With frm
lngFormCount = lngFormCount + 1
lngControlFoundCount = 0
If InStr(.RecordSource, strSought) Then
Debug.Print "Form " & .Name & " RecordSource: " &
..RecordSource
lngFoundCount = lngFoundCount + 1
End If
For Each ctl In .Controls
If ctl.ControlType = acComboBox _
Or ctl.ControlType = acListBox _
Then
lngControlCount = lngControlCount + 1
If InStr(ctl.RowSource, strSought) Then
lngFoundCount = lngFoundCount + 1
lngControlFoundCount = lngControlFoundCount + 1
If lngControlFoundCount = 1 Then
Debug.Print "Form " & .Name & " -- string found
in control"
End If
Debug.Print , "Control " & ctl.Name & " RowSource: "
& ctl.RowSource
End If
End If
Next ctl
DoCmd.Close acForm, .Name
End With
Set frm = Nothing
Next doc

Exit_SearchRecordAndRowSources:
Set ctl = Nothing
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngFormCount & _
" forms and " & lngControlCount & " controls, found " & _
lngFoundCount & " occurrences."
Exit Sub

Err_SearchRecordAndRowSources:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchRecordAndRowSources

End Sub


Sub SearchReportRecordSources(strSought As String)

' Search the recordsources of all reports
' for the specified string.

'
' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.

On Error GoTo Err_SearchReportRecordSources

Dim db As DAO.Database
Dim doc As DAO.Document
Dim rpt As Report

Dim varTest As Variant
Dim lngReportCount As Long
Dim lngFoundCount As Long

Debug.Print "*** Beginning search ..."

Set db = CurrentDb
For Each doc In db.Containers("Reports").Documents
DoCmd.OpenReport doc.Name, acDesign, WindowMode:=acHidden
Set rpt = Reports(doc.Name)
With rpt
lngReportCount = lngReportCount + 1
If InStr(.RecordSource, strSought) Then
Debug.Print "Report " & .Name & " RecordSource: " &
..RecordSource
lngFoundCount = lngFoundCount + 1
End If
DoCmd.Close acReport, .Name
End With
Set rpt = Nothing
Next doc

Exit_SearchReportRecordSources:
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngReportCount & _
" reports, found " & _
lngFoundCount & " occurrences."
Exit Sub

Err_SearchReportRecordSources:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchReportRecordSources

End Sub


Sub SearchQueries(strSought As String)

' Search all queries for SQL containing the specified string.

'
' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.

On Error GoTo Err_SearchQueries

Dim db As DAO.Database
Dim qdf As QueryDef

Dim varTest As Variant
Dim lngSearchCount As Long
Dim lngFoundCount As Long

Debug.Print "*** Beginning search ..."

Set db = CurrentDb
For Each qdf In db.QueryDefs
With qdf
If Left$(.Name, 3) <> "~sq" Then
lngSearchCount = lngSearchCount + 1
If InStr(.SQL, strSought) Then
Debug.Print "Query " & .Name & " SQL: " & .SQL
lngFoundCount = lngFoundCount + 1
End If
End If
End With
Next qdf

Exit_SearchQueries:
Set qdf = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngSearchCount & _
" objects, found " & lngFoundCount & " occurrences."
Exit Sub

Err_SearchQueries:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchQueries

End Sub
'------ end of code ------

Please note that some of the lines in the above code will probably have been
broken by the newsreader, and will have to be fixed before the code will
compile.

 

[Peter Hallett]

Ooh! Now I do like a freebee! I have printed out your code and will
certainly give it a spin. That should keep me out of mischief for some time.
Many thanks.

 

[Albert D. Kallal]

As I pointed out earlier, I am hoping to answer the question, "What are
these 400 odd queries used for and where?" They are easy enough to find
in
the VBA code

You mean the other way around here...right?

In access 2007, You can right click on any form, report or whatever, and
use the "view dependencies option". that option will turn on track bought an
incorrect, and I suggest you turn off after you've done viewing of
dependencies, but this feature is now built into access. also keep in mind
and an upgrade from previous versions of access is only $109, an access 2007
also includes the developer tools for free.

I am confused as to how you think looking through 30,000 or 100,000 lines of
code in a large access application would allow you to determine
dependencies? To find dependencies in code could take years of manpower to
discover dependencies.

eg:

strTable = inputbox("what table to use for history reporting?")

strSql = "select * from " & strTable

In the above the code has to actually be executed and run to ascertain
exactly what tables going to be involved here. in other words any type of
system that scans your code, can just look for table names but is going in
fact I have to actually execute the VBA code.

strTableName = "salesData" & strYear

Again, the above is another example in which the year is appended to some
historical data and reporting. I should point out that it's not a very good
programming practiced to have the table name actually have some particular
meaning to data that's stored inside that "could" be better served by
placing an additional column inside of one table in place of having many
tables. The above is just a hypothetical example, but the point here is that
we often do see code in applications that sets the value of a query or table
or the SQL is generated on the fly, and it's simply not hard coded or
predetermined value like it is for form or report.

So testing a form or report for dependencies on particular queries is rather
easy to do. (as mentioned, this feature is built into access 2007).

It the coding part that is next to impossible to determine the data or table
or query is being used in code......

So, we now have a view dependencies option built into access. For your
coding parts you on your own, and there's no tool on the planet that will
give you a true and accurate picture of the tables used.....

 

[Paul Shapiro]

If you happen to have Visual SourceSafe, you can add the database to
sourcesafe and then use the sourcesafe explorer to search for every
occurrence of those query name. As long as the string exists as text, it
will be identified in any code, query, form, or report. Very useful and
simple. One search for the string finds every occurence anywhere and
presents it as a useful report. Except that 400 is a lot to search for, so
nothing is so simple.

 

[Peter Hallett]

Thanks for the suggestion but Access- DependencyChecker didn't fare too well,
I am afraid. Installation was OK but when asked to check virtually any
object the checker responded with a series of errors of the form, “The
expression ‘X’ you entered as the event property setting produced the
following error: Object or class does not support the set of events.†‘X’
here represents a variable expression. The first was ‘On Click’. A list of
other similar messages then followed.

 

[Peter Hallett]

I don’t have Visual SourceSafe so I looked it up on the Net – and saw the
price! The paramedics have just left.

 

[Paul Shapiro]

It's not worth buying standalone, but I get Visual SourceSafe as part of an
MSDN subscription, so it's "free", meaning I already paid for it.

I think there are some VSS-compatible programs, at least one of which used
to be free for a single-user license. I think those would also work. Here's
one of them, which says it is free for a single user:
http://sourcegear.com/vault/?gclid=CPmc59Ocv5oCFQOuFQod5nYVtA

I haven't used any of the alternatives, but my understanding is the Access
sourcecode control add-in should work with them.

 

[Peter Hallett]

Albert,

I am, as always, grateful for your input but I think my question was
correctly posed in my first post.

There appears to be some confusion over dependency checking and textual
analysis. You will be well aware that, if an object is referenced in a
form’s VBA, that does not constitute a dependency, as far as Access
dependency analyzers are concerned. If I use the MS integral Access analyzer
to display the dependency of, say, “qry_This_Query†it simply tells me that
it uses “tbl_This_Tableâ€. What it does not tell me is that “qry_This_Queryâ€
is invoked in the VBA code of five different forms – but it is this that I am
seeking to determine.

The task is therefore fundamentally one of word processing rather than
dependency checking. I wish to find a given string, or set of strings,
embedded in Access text, whether that be VBA or SQL. It is the latter
requirement that makes the job rather less than straightforward, combined
with the need to automate the search, obviating the need to input 400 query
names, one at a time, or, in a similar situation, to individually input the
names of an even greater number of variables, to ascertain their use.

The ‘mission statement’ is to find a utility to do the job. I don’t know of
a suitable word processor. The Access integral analyzer and documenter does
not provide the answer whilst FMS’s Total Access Analyzer may, or may not. I
am still awaiting a response from the company in respect of a number of
pre-sales questions that I posed it. If I get no answer then that in itself
will determine my next step. Its Northwind database demonstration is of
limited use.

At the moment, Dirk Goldgar’s approach seems the most promising, although if
I can find an ‘off-the-shelf’ utility, even if it is expensive, then that may
yet prove a more cost-effective solution.

 

[Peter Hallett]

Dirk,

“Please note that some of the lines in the above code will probably have been
broken by the newsreader, and will have to be fixed before the code will
compile.â€

Most of the breaks are trivial and easy to repair but there are a couple of
anomalies.

In repairing:-

Debug.Print "Report " & .Name & " RecordSource: " &
...RecordSource

which appears on more than one occasion, the compiler (and me, for that
matter!) are thrown by the double dotted syntax. Knocking out one of the
dots, on reconcatenating the line, certainly keeps the compiler happy but
this does not look like the sort of oddity that the newsreader would have
introduced. Any thoughts?

 

[Albert D. Kallal]

Albert,

I am, as always, grateful for your input but I think my question was
correctly posed in my first post.

There appears to be some confusion over dependency checking and textual
analysis. You will be well aware that, if an object is referenced in a
form's VBA, that does not constitute a dependency, as far as Access
dependency analyzers are concerned. If I use the MS integral Access
analyzer
to display the dependency of, say, "qry_This_Query" it simply tells me
that
it uses "tbl_This_Table". What it does not tell me is that
"qry_This_Query"
is invoked in the VBA code of five different forms - but it is this that I
am
seeking to determine.

Exccllent, glad you cleared that up. It is in fact looking at the VBA that
is quite a diffciult issue here.

The task is therefore fundamentally one of word processing rather than
dependency checking. I wish to find a given string, or set of strings,
embedded in Access text, whether that be VBA or SQL.

Agreed. However, do keep in mind the issues I pointed out. Often the
value(s) of those tables, or queries is determined by code at runtime, and a
simple scanning of the code text will NOT result in a accurate list of
dependences given. Scanning the code is a start but it NEVER CAN be accurate
unless the code is actually executed or interpreted. I have all kinds of
code that calls a form and then sets it data source...and that data source
is determined at runtime and is NOT hard coded...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)





It is the latter

 

[Peter Hallett]

Dirk,

Ignore my earlier posting. Removing the duplicate stop seems to have done
the trick. As a result, I am pleased to say that your ‘cheapskate’ utility
has already started to pay dividends.

I recently deleted a redundant table that had been in use for years and then
spent a lot of time deleting or modifying all the queries that were formerly
dependent upon it. Having compiled your code, I used SearchQueries to look
for the table and, lo and behold, despite my best earlier efforts, it showed
that four references still remained amongst the 397 queries searched
(gratifyingly close to my earlier estimate of 400!). The run time was just a
couple of seconds.

This is admittedly dependency analysis, which might be possible with one of
the Access analyzers but a reverse relationship was being sought – i.e.
rather than supplying the name of a query, and asking for a list of the
tables on which it depended, the name of a missing table was provided with a
request to find the queries based upon it. Since the table in question no
longer exists, I think that I am right in claiming that the integral Access
analyzer would not do the job – at least, I can’t see how to do it. Whether
any of the various alternative proprietary analyzers could be used is unclear.

This one example should demonstrate to anyone still unsure of the reasons
underlying my original question, just how valuable a utility of this sort can
be. I am going to try to add to your code so that, for example, I can test
all the Forms VBA code for the occurrence of nominated strings. I then hope
to extend it further so that it will automatically work its way through the
full list of queries, looking for associated references in the VBA. Whether
my programming skills are up to the task I am doubtful but, if the result
works I will submit it, to allow the really clever people in this forum to
perform an autopsy – or should I say autopsies? There should be at least one
set of criticisms per MVP. (If it doesn’t work, I’ll still submit it. That
will probably double the number of replies!)

 

[Dirk Goldgar]

Dirk,

“Please note that some of the lines in the above code will probably have
been
broken by the newsreader, and will have to be fixed before the code will
compile.â€

Most of the breaks are trivial and easy to repair but there are a couple
of
anomalies.

In repairing:-

Debug.Print "Report " & .Name & " RecordSource: " &
..RecordSource

which appears on more than one occasion, the compiler (and me, for that
matter!) are thrown by the double dotted syntax. Knocking out one of the
dots, on reconcatenating the line, certainly keeps the compiler happy but
this does not look like the sort of oddity that the newsreader would have
introduced. Any thoughts?

Mosty puzzlement. There were no double dots in the code I posted, and I
don't see them in my original post as it appears in my newsreader, but I do
see them in your quote of my post. They must be an artifact of your
newsgroup software, or of Microsoft's web portal for newsgroups. I'm
curious to know whether others see these double dots in my original post.

 

[Dirk Goldgar]

Dirk,

Ignore my earlier posting. Removing the duplicate stop seems to have done
the trick. As a result, I am pleased to say that your ‘cheapskate’
utility
has already started to pay dividends.
Great!

I am going to try to add to your code so that, for example, I can test
all the Forms VBA code for the occurrence of nominated strings.

Hmm, I have another routine, "SearchModules", that searches all the standard
(not forms or reports) for a string. It probably wouldn't be too hard to
adapt it to search all VBA modules in the project, including form and report
modules. Let me see what I can do (unless you'd rather do it yourself).

 

[Peter Hallett]

Perish the thought that I should be seen to be soliciting more free software
(but then, on the other hand, I was never one to look a gift horse in the
mouth!) I will watch this space with interest.

As a footnote to the double-dotted syntax, discussed in the last post, your
original text arrived on my machine showing both stops, which were then, as
you observed, reproduced in all subsequent copies. Strangely, the problem
only showed itself where the same code was split. There were other lines
which wrapped without apparent difficulty. Bit of a mystery.

 

[Dirk Goldgar]

Perish the thought that I should be seen to be soliciting more free
software
(but then, on the other hand, I was never one to look a gift horse in the
mouth!) I will watch this space with interest.

Code posted at the bottom of this message. Warning: this has been only
lightly tested, and is very rough indeed!

As a footnote to the double-dotted syntax, discussed in the last post,
your
original text arrived on my machine showing both stops, which were then,
as
you observed, reproduced in all subsequent copies. Strangely, the problem
only showed itself where the same code was split. There were other lines
which wrapped without apparent difficulty. Bit of a mystery.

It's very odd. I suspect some fault in Microsoft's forum software. The
double dots don't show up in Google Groups, either.

Here are procedures to search VBA code. Again, watch out for line-wrapping.

'------ start of code ------
Sub SearchVBA(strSought As String, Optional bWholeWord As Boolean)

' Search all VBA code -- in standard, class, form, and report modules --
' for the specified string.

' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.

On Error GoTo Err_Handler

SearchModules strSought, bWholeWord

SearchFormModules strSought, bWholeWord

SearchReportModules strSought, bWholeWord

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub

Function SearchModule( _
mdl As Access.Module, _
strSought As String, _
Optional bWholeWord As Boolean) _
As Long

' Search module <mdl> for string <strSought>.
' Optional argument <bWholeWord> tells whether to report only
' occurrences of the string as a "whole word".

' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.

On Error GoTo Err_SearchModule

Dim bFound As Boolean
Dim lngFoundCount As Long
Dim lngStartLine As Long
Dim lngEndLine As Long
Dim lngStartCol As Long
Dim lngEndCol As Long
Dim lngProcType As Long

lngFoundCount = 0
lngStartLine = 0
lngEndLine = 0

With mdl

Do
lngEndLine = 0
lngStartCol = 0
lngEndCol = 0

bFound = .Find(strSought, lngStartLine, lngStartCol, lngEndLine,
lngEndCol, bWholeWord)

If bFound Then

lngFoundCount = lngFoundCount + 1

Debug.Print "Found in " & _
IIf(mdl.Type = acStandardModule, "standard", "class") &
_
" module " & .Name & ", line " & lngStartLine & ", proc
'" & .ProcOfLine(lngStartLine, lngProcType) & "'"

lngStartLine = lngStartLine + 1

End If

Loop While bFound

End With

Exit_SearchModule:
SearchModule = lngFoundCount
Exit Function

Err_SearchModule:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchModule

End Function

Sub SearchModules(strSought As String, Optional bWholeWord As Boolean)

' Search all standard and class modules for the specified string.

' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.

On Error GoTo Err_SearchModules

Dim db As DAO.Database
Dim cnt As DAO.Container
Dim doc As DAO.Document
Dim mdl As Access.Module

Dim bFound As Boolean
Dim lngSearchCount As Long
Dim lngFoundCount As Long
Dim lngStartLine As Long
Dim lngEndLine As Long
Dim lngStartCol As Long
Dim lngEndCol As Long
Dim lngProcType As Long
Dim bOpenedModule As Boolean

Debug.Print "*** Searching standard and class modules for '" & strSought
& "' ..."

Set db = CurrentDb
Set cnt = db.Containers("Modules")

For Each doc In cnt.Documents

lngSearchCount = lngSearchCount + 1

If CurrentProject.AllModules(doc.Name).IsLoaded = False Then
DoCmd.OpenModule doc.Name
bOpenedModule = True
Else
bOpenedModule = False
End If
Set mdl = Modules(doc.Name)

lngStartLine = 0
lngEndLine = 0

lngFoundCount = lngFoundCount + SearchModule(mdl, strSought,
bWholeWord)

If bOpenedModule = True Then
DoCmd.Close acModule, mdl.Name, acSaveNo
End If

Set mdl = Nothing

Next doc

Exit_SearchModules:
Set cnt = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngSearchCount & _
" modules, found " & lngFoundCount & " occurrences."
Exit Sub

Err_SearchModules:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchModules

End Sub

Sub SearchFormModules(strSought As String, Optional bWholeWord As Boolean)

' Search the class modules of all forms that have them,
' looking for the specified string.

' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.

On Error GoTo Err_SearchFormModules

Dim db As DAO.Database
Dim doc As DAO.Document
Dim frm As Access.Form
Dim mdl As Access.Module

Dim lngFormCount As Long
Dim lngFoundCount As Long
Dim bOpenedForm As Boolean

Debug.Print "*** Searching form modules for '" & strSought & "' ..."

Set db = CurrentDb
For Each doc In db.Containers("Forms").Documents
If CurrentProject.AllForms(doc.Name).IsLoaded = False Then
DoCmd.OpenForm doc.Name, acDesign, WindowMode:=acHidden
bOpenedForm = True
Else
bOpenedForm = False
End If
Set frm = Forms(doc.Name)
With frm
If frm.HasModule Then
lngFormCount = lngFormCount + 1
lngFoundCount = lngFoundCount + SearchModule(frm.Module,
strSought, bWholeWord)
End If
If bOpenedForm Then
DoCmd.Close acForm, .Name, acSaveNo
End If

End With
Set frm = Nothing
Next doc

Exit_SearchFormModules:
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngFormCount & _
" forms, found " & _
lngFoundCount & " occurrences."
Exit Sub

Err_SearchFormModules:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchFormModules

End Sub

Sub SearchReportModules(strSought As String, Optional bWholeWord As Boolean)

' Search the class modules of all reports that have them,
' looking for the specified string.

' Copyright (c) 2009, Dirk Goldgar and DataGnostics, LLC
' You may copy and use this code all you want, so long as
' you preserve the copyright and this notice, and don't
' sell it.

On Error GoTo Err_SearchReportModules

Dim db As DAO.Database
Dim doc As DAO.Document
Dim rpt As Access.Report
Dim mdl As Access.Module

Dim lngReportCount As Long
Dim lngFoundCount As Long
Dim bOpenedReport As Boolean

Debug.Print "*** Searching report modules for '" & strSought & "' ..."

Set db = CurrentDb
For Each doc In db.Containers("Reports").Documents
If CurrentProject.AllReports(doc.Name).IsLoaded = False Then
DoCmd.OpenReport doc.Name, acDesign, WindowMode:=acHidden
bOpenedReport = True
Else
bOpenedReport = False
End If
Set rpt = Reports(doc.Name)
With rpt
If rpt.HasModule Then
lngReportCount = lngReportCount + 1
lngFoundCount = lngFoundCount + SearchModule(rpt.Module,
strSought, bWholeWord)
End If
If bOpenedReport Then
DoCmd.Close acReport, .Name, acSaveNo
End If

End With
Set rpt = Nothing
Next doc

Exit_SearchReportModules:
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngReportCount & _
" reports, found " & _
lngFoundCount & " occurrences."
Exit Sub

Err_SearchReportModules:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchReportModules

End Sub
'------ end of code ------