List the Data Sources of All Reports in DB

[GeyikBaba]

In Access 2003 I am trying to both weed out unused queries and/or determine
everywhere a given query is used.

I've been able to use db.QueryDefs.SQL to programatically loop thru each
query, print the SQL to a text file, then do a search. It has worked very
well.

I'd like to do something similar with all reports - that is, loop thru each
report and list the RecordSource for each report, be it a table, query, or
sql query. Is there some kind of a collection, or other method, I could use
to do this? I did not see anything likely in the database collection.

Many thanks
Mike Thomas

 

[Jerry Whittle]

Rich Fisher's excellent Find and Replace add-in might be just the ticket. If
you register it (one of the best $37 I've ever spent), the cross-reference
report can find what queries use which reports and forms. It's a good way to
find orphans.

http://www.rickworld.com/

 

[Jeff Conrad]

My free CSD Tools add-in will do that and a whole bunch more.
You can output the reports to Word or Excel.

http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html

--
Jeff Conrad - Access Junkie - MVP
Co-author - Microsoft Office Access 2007 Inside Out
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

in message:

 

[GeyikBaba]

Many thanks Fred, that was exactly what I was after. I rewrote it slightly,
then go thru the resulting file with Regular Expressions. It works very well
and is very quick.

Do you know of a way to do the same with forms, but printing out the code
fore each form?

Many thaks
Mike Thomas

Public Sub GetReportSource()
' Print out the RecordSource for each report.
Dim doc As Document
Dim cont As Container
Dim strFile As String
Dim filenumber As Integer

filenumber = FreeFile ' Get unused file number.
strFile = "c:\Work\CSharpUtilities\TextFile\ReportSource.txt"
Open strFile For Output As #filenumber ' Create file name.

With CurrentDb
For Each cont In .Containers
If cont.name = "Reports" Then
For Each doc In cont.Documents
DoCmd.OpenReport doc.name, acViewDesign
Print #filenumber, "zzzz" & Trim(doc.name)
Print #filenumber, " " & Trim(Reports(doc.name).RecordSource)
Print #filenumber, ""
' Debug.Print " Report Name " & doc.name
' Debug.Print "Record Source " & Reports(doc.name).RecordSource
DoCmd.Close acReport, doc.name
Next doc
End If
Next cont
End With
Close #filenumber ' Close file.

End Sub

 

[GeyikBaba]

Thanks Jerry, I have that utility, but my copy does not work with Access
2003 - I may upgrade.
Mike Thomas

 

[GeyikBaba]

Fred,

Thanks again. This time I was looking for the VB code in the form's module,
not the recordsource.

I need to fine tune it, but it will be something like:

Forms(doc.Name).Module.Lines(1,1)

I just need to get it one line at a time reading until all of the lines are
read.


Mike Thomas

 

[GeyikBaba]

Jeff,

Many thanks - I did not notice this first time around. I downloaded the
tool - haven't installed it yet, but read the instructions. Looks really
helpful.

MIke Thomas