Check Activity on a Query

[LadyDi]

Is there any way to see when the last time a query was used? I know there are some queries in my database that have not been used for a while. However, I do not know which ones they are and I do not want to remove the wrong query. I can find the date the query was last modified, but I would like to know the last time the query was run. Does anyone know of a way to do this?

 

[PNGBill]

Change the query name. put an X in front and wait for the crash

Not so good on a multi user data base but it will pick up if the query was being used.

There is code to search your database for where a name exists. Check out Bob Larson's web site.

I just recently went through my forms and reports and where they were using a query, copied and pasted same into the documents record source and then deleted the query.

Save a backup before you do this in case another form or report used the same query and watch out for stacked queries. try and use just tables in your queries and if another query, then it should be an important one. But try not to have queries 3 or 4 layers deep

 

[SOS]

A better way is to not let users access queries directly but only through a form. If you do that you can append the date/time and who used it to a table.

If you can't do that, you could add a function to each query to call the update but then it might render a particular query as non-updateable.

 

[LadyDi]

How can I append the date, time, and who used a query to a table?

 

[JANR]

Well you can create a function in a standard module that insert the queryname and Date/Time when it is opend, something like this:

Function LogQuery(AnyString As String)
Dim strSQL As String
strSQL = ""
strSQL = strSQL & " INSERT INTO tblLogQuery ([qryName],[DateTimeUsed])"
strSQL = strSQL & " SELECT '" & AnyString & "', now()"
CurrentDb.Execute strSQL, dbFailOnError
End Function

Then call this function by adding an extra field in the query that calls this function.

expr: LogQuery("NameOfQuery")

This probably dosen't work on append or update queries, but simple select queries you'll be fine, just test that the query runs as normal.

JR

 

[LadyDi]

That works perfectly. Thank you very much.