How to identify used Queries in access 2k

[ZAMBER25]

Hi all. I got many queries in access 2000 . I want to identify those that are used by my forms and reports with out going trough each form and report.Is there a way to achive this since i got like 90 queries .Thanks

 

[GingerR]

Hi. here's some code that will write all form and report Record Source's to a table. First make a table:

Table Name: TestRecordSources
Field: ObjectType (Text)
Field: ObjectName (Memo)
Field: RecordSource (Memo)

Then in a code module, paste this:

Function RecordsourceList()
'List recordsources of all Forms and Reports
    CurrentDb.Execute ("Delete * from TestRecordSources")
    Dim objCurrent As AccessObject
    Dim frmCurrent As Form
    Dim rptCurrent As Report
    Dim strFormName, strReportName As String
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * from TestRecordSources")
    
    For Each objCurrent In CurrentProject.AllForms
    
        DoCmd.OpenForm objCurrent.name, acDesign, , , acFormPropertySettings, acWindowNormal
        Set frmCurrent = Application.Screen.ActiveForm
        strFormName = frmCurrent.name
        
            rs.AddNew
            rs!ObjectName = frmCurrent.name
            rs!ObjectType = "Form"
            rs!RecordSource = frmCurrent.RecordSource
            rs.Update
        DoCmd.Close acForm, strFormName, acSaveNo
        
    Next objCurrent

    For Each objCurrent In CurrentProject.AllReports
    
        DoCmd.OpenReport objCurrent.name, acViewDesign, , , acWindowNormal
        Set rptCurrent = Application.Screen.ActiveReport
        strReportName = rptCurrent.name
        
            rs.AddNew
            rs!ObjectName = rptCurrent.name
            rs!ObjectType = "Report"
            rs!RecordSource = rptCurrent.RecordSource
            rs.Update
        DoCmd.Close acReport, strReportName, acSaveNo
        
    Next objCurrent


    Set rs = Nothing
End Function

make sure that in the code menu item TOOLS+REFERENCES, you have Microsoft DAO 3.6 checked.

Run the function. It will first delete any existing records in the table, then fill it with the recordsources of all forms and reports. It won't do charts that are embedded in any forms or reports, but if you need that, maybe you can fiddle around and figure it out. Look for code that loops thru form and report controls.

Hope this helps.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html