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

 

[fredg]

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

This will cycle through all your reports.
*** Watch out for line wrap in the second debug print line.***
Public Sub GetRecordSource()
' Print out the RecordSource for each report.
Dim doc As Document
Dim cont As Container

With CurrentDb
For Each cont In .Containers
If cont.Name = "Reports" Then
For Each doc In cont.Documents
DoCmd.OpenReport doc.Name, acViewDesign
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

 

[Guest]

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

 

[fredg]

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

You can adapt the same code I sent you.
The commented out lines are for Reports.
The new lines are for forms.
I've placed the Form lines at the left margin to make them easier to
find.
Make sure you include that extra comma in the OpenForm method line.

With CurrentDb

For Each cont In .Containers
' If cont.Name = "Reports" Then
If cont.Name = "Forms" Then
For Each doc In cont.Documents
' DoCmd.OpenReport doc.Name, acViewDesign, , , acHidden
DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
' Debug.Print "Report Name: " & doc.Name
Debug.Print "Form Name: " & doc.Name
' Debug.Print "Record Source: " &
Reports(doc.Name).RecordSource

Debug.Print "Record Source: " & Forms(doc.Name).RecordSource
Debug.Print
' DoCmd.Close acReport, doc.Name
DoCmd.Close acForm, doc.Name
Next doc
End If
Next cont
End With

 

[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

 

[fredg]

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

Why not just use the built in Documenter?
Tools + Analyze + Documenter

Select all the forms.
Click on Options.
Check Code.
Uncheck anything else.
Click OK

 

[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