Cleaning Up Access

[Frankie1947]

Team:
I have developed a unique production labor effectiveness
system using access. I want to go back a re do some
queries (156 so far) and I want to delete some reports
(300 so far) and make the system more user friendly. I
have made many of the query names unique and I have no
logic to the names since I was experimenting with many of
queries to make them work. Now I want to clean the system
up.

Is there a graphically way to do this in access so when I
point to a report it can give me the query it is linked to?

Thanks

 

[John Vinson]

Is there a graphically way to do this in access so when I
point to a report it can give me the query it is linked to?

Not within the program itself; there are some third-party tools that
let you crossreference objects in a database.

Free: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

 

[david epsom dot com dot au]

It is often handy to be able to see more information at
a glance. FWIW, here is code to set the report 'description'
property to the recordsource value. The description
property is what you see in the details view of the
database window. You need to refresh the database window
by changing panes to see the new values.

Sub Add_rpt_description()
Dim i As Integer
Dim iRptCount As Integer
Dim sRptName As String
Dim sRptSource As String

iRptCount = CodeDb.Containers("reports").Documents.Count
For i = 0 To iRptCount - 1
sRptName = CodeDb.Containers("reports").Documents(i).Name
DoCmd.OpenReport sRptName, acDesign
sRptSource = Reports(sReportName).RecordSource
DoCmd.Close acReport, sRptName, acSaveNo
SetProperty "Description", sRptName, "reports", dbText, sRptSource
Next i

End Sub


Sub SetProperty(ByVal strPropName$, ByVal sDocName$,
_ByVal sContainer$, ByVal varPropType, ByVal varPropValue)
On Error Resume Next
CodeDb.Containers(sContainer$).Documents( _
sDocName).Properties(strPropName).Value = varPropValue
CodeDb.Containers(sContainer$).Documents(sDocName).Properties.Append _
CodeDb.Containers(sContainer$).Documents(sDocName).CreateProperty( _
strPropName, varPropType, varPropValue)
End Sub