Does anyone have or know of any code that will go through all of the queries that you have and tell you if they are being used and where? I think that I have a lot of unused queries in my database, but I am afraid to delete them since I don't know if they are used or not, and there are so many queries/reports/forms that I don't really want to manually look up each one.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Auditing Code
- Thread starter Hmadyson
- Start date
You can use the documenter to give you a printout of all your database objects and how they are defined. Then you'd have a piece of paper you could scan to look at. Click tools-->Analyze-->Documenter to check it out.
HTH Joe Miller
joe.miller@flotech.net
HTH Joe Miller
joe.miller@flotech.net
MichaelRed
Programmer
This has been discussed in some depth in these forums previously. It is - at best a large and difficult task, with no 'caned' soloution - at least not for free. There is a commercial product "Speed Ferret" which has an outstanding reputation for working with Ms. Access databases and doing strange things. I do NOT know if it includes a complete cross reference, but that is what you would need to determine whic objects are 'not used'. You CAN build the cross reference, but it takes a large amount of code.
Be aware that even with a cross reference, it still requires some effort to determine which objects are really not required. You will still need to go through each form and report and determine that they are Useful / Required / Rubbish ... In one job, I added a LastUsed Table and had each form and report "log" itself in the table. Periodically I would "Archive" the forms and reports which had not been used for XXX weeks. After 6 or 7 MONTHS! - a manager from a department which I did not know even used the db called and complained that "HIS" report was missing!
On a pratical level, if you are going to persue this, you need to create the cross reference.
Start with the collections. Itterate through the forms and reports, adding each to a table.
With the table of forms and reports, (programatically) open each in design mode. Include the 'ReordSource' in a table. Itterate through the controls collection, and list all ControlSource references whch are not from the RecordSource (all which include "=" as the first character?)
Now, the going get a bit tougher. For each RecordSource and ControlSource, determine if it is a Query. If it is, you need to open it as a QueryDef and get the Source objects (Stuff in Where clause(s)) - add them to the recordsource / controlsource lists - RECURSIVELY, until you are back down to a TABLE. Where a recordsource is a table, just add it to the list. If it is just a calculation or format function, ignore it.
Go back to the forms and reports collections, check to see if it has code. If there is code, you have real issues. Does the code dimension and instantiate any nammed recordsets? With the advent of ADO, it gets to be somewhat riskier, but you need to collect the NAMMED recordsets used by / referenced in the modules (and any referenced procedures - perhaps in other modules) as recordsources used in your app. In particular, you need to find any tables or queries placed in 'lists' (arrays) and used in code which references the list. As I recall, this is about as far as I got when trying this. It works very well, finding 95%+ of the objects in the database which are referenced from a forn or report. Unfortunatly, 95% is an abject failure in an industry where perfect is barely a passing grade.
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
Be aware that even with a cross reference, it still requires some effort to determine which objects are really not required. You will still need to go through each form and report and determine that they are Useful / Required / Rubbish ... In one job, I added a LastUsed Table and had each form and report "log" itself in the table. Periodically I would "Archive" the forms and reports which had not been used for XXX weeks. After 6 or 7 MONTHS! - a manager from a department which I did not know even used the db called and complained that "HIS" report was missing!
On a pratical level, if you are going to persue this, you need to create the cross reference.
Start with the collections. Itterate through the forms and reports, adding each to a table.
With the table of forms and reports, (programatically) open each in design mode. Include the 'ReordSource' in a table. Itterate through the controls collection, and list all ControlSource references whch are not from the RecordSource (all which include "=" as the first character?)
Now, the going get a bit tougher. For each RecordSource and ControlSource, determine if it is a Query. If it is, you need to open it as a QueryDef and get the Source objects (Stuff in Where clause(s)) - add them to the recordsource / controlsource lists - RECURSIVELY, until you are back down to a TABLE. Where a recordsource is a table, just add it to the list. If it is just a calculation or format function, ignore it.
Go back to the forms and reports collections, check to see if it has code. If there is code, you have real issues. Does the code dimension and instantiate any nammed recordsets? With the advent of ADO, it gets to be somewhat riskier, but you need to collect the NAMMED recordsets used by / referenced in the modules (and any referenced procedures - perhaps in other modules) as recordsources used in your app. In particular, you need to find any tables or queries placed in 'lists' (arrays) and used in code which references the list. As I recall, this is about as far as I got when trying this. It works very well, finding 95%+ of the objects in the database which are referenced from a forn or report. Unfortunatly, 95% is an abject failure in an industry where perfect is barely a passing grade.
MichaelRed
mred@att.net
There is never time to do it right but there is always time to do it over
- Status
Similar threads
- Locked
- Question