I have a databse with approximately 180 queries in, which have been added over about two years.
I am attempting to reconcile the system and delete any unnecessary queries. To save time I was wondering if there is a way of monitoring when users open queries/forms etc so that you can decide if they are needed
Thanks in advance
Peter

For when they were used, that's not possible. For when they where last modified, that is possible.

In Access XP (2002) if you open the database, you should see the toolbar with the following buttons: Open, Design, New, Delete, Large Icons, Small Icons, List, Details.

If you choose details and then click the "Modified" header, the queries will sort by date last modified. From there you can get an idea of its last usage if the query is usually modified before being used.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

You can also run the following query if you want them to be displayed in a datasheet view by querying the MS Access system table named MSysObjects:

SELECT MSysObjects.Name, MSysObjects.DateUpdate
FROM MSysObjects
WHERE MSysObjects.Name Not Like "~sq_*" AND MSysObjects.Type=5
ORDER BY MSysObjects.Name;

If I recall correctly, queries with prefixes of "~sq_" are internal SQL statements used within form and report properties. Using the "Not Like" clause in this SQL will show query objects only.

You can list objects by type by using the following WHERE condition in the same SQL:

WHERE MSysObjects.Type = n

Substitute values for "n" gives you:

1 = Native Access Tables
4 = ODBC-Linked Tables
6 = MS Jet/Access Linked Tables
5 = Query Objects
-32768 = Form Objects
-32764 = Report Objects
-32756 = Data Access Page
-32766 = Macro Objects
-32761 = Module Objects

Selecting the column name "Connect" in your SQL statement will give you the connection string information for an ODBC-Linked table.

Selecting the column name "Database" will give you the MS Access/Jet database, path, filename, and remote-linked table name for a MS Access/Jet linked table.

Selecting the column name "DateCreate" will give you the date and time that the object was created.

Selecting the column name "ForeignName" will give you the remote linked table name for an ODBC-linked table.