Remove unused queries from database

[TammyK]

I was given a database and there a MANY queries. I don't think that even half of them are being used as data sources. Being proactive myself, I have always deleted the unusable queries as I go...How can I identify what queries are not being used so I can delete them?

 

[hneal98]

You might be able to use the documenter. If you are using Access 2000, go to Tools|Analyzer|Documenter. Not sure where it is in the earlier versions. It has been a long time since I used them.

 

[hneal98]

ONe other note. You would use this to identify what the queries are doing.

 

[JonFer]

This SQL will tell you what tables and queries are used in another query. I've found it to be extremely valuable to check before deleting anything.

SELECT o.Name AS QueryName, q.Name1 AS Uses, q.Expression as SQL
FROM MSysQueries AS q LEFT JOIN MSysObjects AS o ON q.ObjectId = o.Id
WHERE q.Attribute=5
ORDER BY q.Name1;

You would still need to check reports and forms for their record sources as well as sources for listboxes before deleting a query. Some VBA code could search the reports, forms, and form controls.


John

http://www.interautomateconsulting.com

 

[RoyVidar]

The manual approach, would probably be to create a copy of the db, then start deleting and testing;-)

Another way of finding/searching could be using the approach described by bboffin here thread705-751080 to dump all objects in the database (except the tables) into searchable text files. Then you could simply use the standard search on the forder using the "A word or phrase in the file" option on them.

Then the next step would be to use some kind of naming convention on the queries, identifying theire usage and "scope", for instance qrytmpBlahBlah for temporary thingies, qryxxxBlahBlah for queries marked for deletion, qryfrmMainForm, qrycboMainFormCustomer...

Roy-Vidar

 

[BobStubbs]

Two suggestions for you ...

1. Turn the Name Autocorrect services off, via the Tools ... Options ... General menu option. Now rename any 'likely to be obsolete' queries and reports, for example by adding 'OLD' to the name. Thus:

rptAllSalaries becomes OLDrptAllSalaries

With Name autocorrect off, Access won't carry this name change through to related queries and reports. If a user complains that their report won't run any more, you can just rename it back again. Any item which is unused for maybe a month or more can then be deleted (keep a backup copy of the database made before doing any deletions, as a final 'safety net').


2. If you run queries and reports from a control such as a menu, button, drop-down list etc, you can log the Last Run date in a table. Here is some example code from my database:

dim strSQL as String
dim strReportName as string
 
'-----------------------------------------------------
'- Update the last run date                          -
'-----------------------------------------------------
strReportName = "My Report Name"

strSQL = "UPDATE tblReportNames SET tblReportNames.LastRunDate = '" & Format$(Now, "dd/mm/yyyy") & "' "

strSQL = strSQL & "WHERE tblReportNames.[ReportName] = '" & strReportName & "'"
    
DoCmd.SetWarnings (False)
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings (True)

Over time, this date information will show you what's being used frequently / occasionally / never!


Bob Stubbs

 

[MichaelRed]

I would some variation of BobStubbs 's 1st approach -perhaps also employing some of the other techniques to identify potential tasrgets. One issue which can confuse many ot the auto discovery techniques is the use (execution) of nestred queries from within a procedure (such as generating complicated report sources). Instantiating (namned) queries via procedures means that they ARE used, but generally do not show up in generic search routines, particularly if the programmer has used some (naming) convention for the group of queries such that the complete name never appears as a single "word" within the procedures.



MichaelRed

 

[TammyK]

Thank you all for the suggestions!! I may just try them all!