Access Query Log/Tracking

[catfood49]

I have a legacy system of 400+ queries in an Access database, some of which are still in use, some not and I need to replace these queries (with Reporting Services alternatives) but I do not know which are still used. Asking the users isn't really an option.

Does MS Access keep a log of all query usage, who by and when? Or does anyone know a way of achieving this through some other method?

Any help/advice/guidance gratefully received.

[Seth Schrock]

What version of Access are you using? If the queries that need replaced don't have a form/report linked to the query, then you should be able to use the Object Dependencies tool. In Access 2010, this can be found under the Database Tools tab in the ribbon in the Relationships group. Just click on it to open it (it will pop up on the side of your screen), make sure that "Objects that depend on me" is selected, click on a query name (single click to just select, but not open it) and then click refresh in the Object Dependencies window. This will tell you what forms, reports, or queries use the selected query.

However, this doesn't tell you how often the query is used or by whom, or anything else about usage. It only tells you if it is being used as the datasource for another object.

[zmbd]

There isn't such a log per say; however, there is a partial tracking of each object in the system tables.
Unless all of your queries are ran via a user interface, in which case you can add code to log when used the best that can be done is the following:

I can only verify the following for V2010
You should never access the system tables in the production copy of the database, corrupting these tables may make it difficult or impossible to open your file.
BACK-UP! BACK-UP! BACK-UP!
USE AT YOUR OWN RISK
I highly recommend that you use this on a COPY of the database.
Open a new query, switch to SQL view, copy and paste the following into the window. Save the query as "Qry_ShowMeQueries"

SELECT MSysObjects.Name
   , MSysObjects.Owner
   , MSysObjects.DateCreate
   , MSysObjects.DateUpdate
FROM MSysObjects
WHERE ((Left([name],1)<>"~") 
         AND ((MSysObjects.Type)=5));

This will now open a list of all of your queries within your database and the headers should be self-evident; however, take note that the field [DateUpdate] is the last date that a change was made to and SAVED to the query.

--- There's always the B&M method... delete all of the queries that are not used for a form, report, etc... (use Seth's method to find these).
Then wait for the complaints to show up (the B&M).