MS Access Search

I have a need to search the record source for all forms and reports in Access 2007.  All record sources are in SQL 2005. In the past I was able to do something similar to this using the code attached. Can this code be modified to accomplish what I am trying to do? For instance I need to search all Forms & Reports to see if they are using vw_ClaimSupervisor.
Sub rpttest()


Dim rpt As Object
Dim strName As String

'For each report in the database
For Each rpt In CurrentProject.AllReports
  'Set strName = name of the report
  strName = rpt.Name
  'Open the report in design view, hidden
  DoCmd.OpenReport strName, acViewDesign, windowmode:=acHidden
    'If the record source is "Orders" then print the name of the report in the immediate window
    If Reports(strName).RecordSource = "Orders" Then
      Debug.Print strName
    End If
  'Close the report
  DoCmd.Close acReport, strName
Next

End Sub

Open in new window

mburk1968Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
To search queries, the code would look something like:

Dim qdf as DAO.Querydef

for each qdf in currentdb.querydefs
    if instr(qdf.SQL, "vw_ClaimSupervisor") > 0 then
       debug.print qdf.name
    end if
Next
0
 
Dale FyeCommented:
There is always Rick Fischers "Find and Replace" utility.

0
 
mburk1968Author Commented:
How do I execute it? I created a module for the code above but I get the macro dialog box when I try to run it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.