Create List of Queries, Tables & Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a list of all queries, tables and reports in my db. Since a
simple copy/paste does not work, is there a quick way of querying this
information?
 
If you must want a list of the objects, you could query them from the
MSysObjects table:
SELECT MSysObjects.Type, MSysObjects.Name
FROM MSysObjects
WHERE ((Not ((MSysObjects.Name) Like "MSys*"
Or (MSysObjects.Name) Like "~*")))
ORDER BY MSysObjects.Type, MSysObjects.Name;

There is a built-in documenter:
Tools | Analyze | Documenter

Jeff Contrad has a more comprehensive one here:
http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html
 
Thanks Allen. Your SQL was exactly what I was looking for. For future
references, is there a way that I can, for example, take the reports
generated from this SQL and include the report's supporting query name?
 
You need to open the report to get its RecordSource.

This example shows how to loop through all your forms or reports, printing
the Name and RecordSource of each one. Pass True for reports:

Public Function ShowRecordSource(bReports As Boolean)
Dim accobj As AccessObject
Dim strDoc As String

If bReports Then
For Each accobj In CurrentProject.AllReports
strDoc = accobj.Name
If accobj.IsLoaded Then
Debug.Print strDoc, Reports(strDoc).RecordSource
Else
DoCmd.OpenReport strDoc, acDesign, WindowMode:=acHidden
Debug.Print strDoc, Reports(strDoc).RecordSource
DoCmd.Close acReport, strDoc
End If
Next
Else
For Each accobj In CurrentProject.AllForms
strDoc = accobj.Name
If accobj.IsLoaded Then
Debug.Print strDoc, Forms(strDoc).RecordSource
Else
DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
Debug.Print strDoc, Forms(strDoc).RecordSource
DoCmd.Close acForm, strDoc
End If
Next
End If
End Function
 
Thanks Allen. I'll give this a try.

Allen Browne said:
You need to open the report to get its RecordSource.

This example shows how to loop through all your forms or reports, printing
the Name and RecordSource of each one. Pass True for reports:

Public Function ShowRecordSource(bReports As Boolean)
Dim accobj As AccessObject
Dim strDoc As String

If bReports Then
For Each accobj In CurrentProject.AllReports
strDoc = accobj.Name
If accobj.IsLoaded Then
Debug.Print strDoc, Reports(strDoc).RecordSource
Else
DoCmd.OpenReport strDoc, acDesign, WindowMode:=acHidden
Debug.Print strDoc, Reports(strDoc).RecordSource
DoCmd.Close acReport, strDoc
End If
Next
Else
For Each accobj In CurrentProject.AllForms
strDoc = accobj.Name
If accobj.IsLoaded Then
Debug.Print strDoc, Forms(strDoc).RecordSource
Else
DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
Debug.Print strDoc, Forms(strDoc).RecordSource
DoCmd.Close acForm, strDoc
End If
Next
End If
End Function
Click to expand...
 
Back
Top