On Open Event for Query to monitor usage

[sesibley]

Is it possible to create an On-Open event for a query? I have a dB with over 500 queries that I have developed over several years. I'd like to know which queries are not being used so that I can delete them - the dB is getting close to the 2G limitation. I'd like to audit which queries are opened, by whom, and when. I know how to audit when data is changed, but not when queries are opened.

I'd like to do something like: create an On-Open event so that when a query is called the user, the query, and the date/time will be captured in a table.

Any suggestions.

[Seth Schrock]

There aren't any events for queries. Also, since queries don't actually store data, deleting a query will only free up a very small amount of space as a query is a just a little text for the query definition. What would really help you would be to split out all your tables to be in one file and your forms, queries, reports, macros and VBA code would be in another file. You can then link to your tables. You can even have your tables in multiple files which would free up even more space.

[sesibley]

All of the tables that I have in my database are linked tables. Since there are no events associated with queries, do you have any suggestions on how I might monitor their usage. Even though removing the queries will not free up that much space in the dB, it will certainly remove clutter - And all the VB associated with calling the various unused queries.

[Seth Schrock]

Depending on what version of Access you have, there is a tool called Object Dependencies under the Database Tools tab in Access 2007 & 2010. I think 2013 also has it, but I don't know about 2003 and earlier. If you open that and then select a query and click refresh using the option Objects that depend on me, it will tell you what queries, forms and reports use that query. You would have to search for the name to find out if any VBA uses the query. I don't know an easy way to find out if a macro uses the query.