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

End Sub

Open in new window

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
    end if
Dale FyeCommented:
There is always Rick Fischers "Find and Replace" utility.

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.
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.