Ability to view last run date of query

[Danielle]

When viewing the queries within a database using the "details" view, I would
like to see the date the query was last generated/run. I can see the
modified date and created date, but would like to see or add the "last run"
date if possible. If it can't be done here - is there a way to see this
information otherwise?

 

[Douglas J. Steele]

Access doesn't record that information.

If you require it, you'd have to ensure that all queries are run through a
form that you've created, rather than through the Access interface, and
store the query name and time in your own table each time a query is run.

 

[Danielle]

It would be so helpful to see when the queries were last used - to purge any
queries that are no longer used. If I understand you correctly - there is a
way to create a form that a user would access whenever they wanted to
generate a query. How cumbersome is this to create?

 

[Douglas J. Steele]

Build the query as normal: Access maintains information about when the query
was created. It just doesn't capture information about when it was last run.

 

[Danielle]

I'm sorry - but I was referring to how cumbersome creating a form to generate
the query to track its useage would be. I have not used forms for that
purpose.

 

[Rick Brandt]

I'm sorry - but I was referring to how cumbersome creating a form to
generate the query to track its useage would be. I have not used
forms for that purpose.

It won't be worth the trouble so I wouldn't bother. If you suspect that you
have a query (or any other object) that is not being used then rename it (I add
_DROP to the end of the name). If a bit of time passes with nobody complaining
or reporting errors then the object can be safely deleted. Keeping old copies
(also a good idea) will let you restore the object if it turns out you need it
after deleting it.

 

[Douglas J. Steele]

I'm not suggesting you create a form to use to generate the query. In fact,
I'd suggest you not create such a form. I'm only suggesting you create a
form to run the query.

A form that runs queries can be very simple.

Create a combo box or list box that lists all of the queries. The RowSource
for the control would be:

SELECT Name
FROM MSysObjects
WHERE Type=5
AND Left(Name, 3) <> "~sq"
ORDER BY Name

In the AfterUpdate event of that control, use

DoCmd.OpenQuery

to run the query, as well as running a SQL statement to insert the name of
the current query and the current time into the table you've created to
store the Last Used information.

 

[Danielle]

Thank you Doug and Rick - both of your suggestions are helpful.
- Danielle

 

[betwalk]

...
Create a combo box or list box that lists all of the queries. The RowSource
for the control would be:

SELECT Name
FROM MSysObjects
WHERE Type=5
AND Left(Name, 3) <> "~sq"
ORDER BY Name

In the AfterUpdate event of that control, use

DoCmd.OpenQuery

------------------------------------
Hi Doug-

Well this is nifty - I just love the things I pick up from reading
these boards.

Is there a way to limit the list to just select queries? Examining the
MSysObjects list, I don't see anything that seems a likely tool to
filter on query type. So, to accomplish this, I see that one could name
their queries in such a way that the select or non-select queries could
be identifiable, but is there another way?

Tia,

Betsy

 

[Douglas J Steele]

------------------------------------
Hi Doug-

Well this is nifty - I just love the things I pick up from reading
these boards.

Is there a way to limit the list to just select queries? Examining the
MSysObjects list, I don't see anything that seems a likely tool to
filter on query type. So, to accomplish this, I see that one could name
their queries in such a way that the select or non-select queries could
be identifiable, but is there another way?

Unfortunately, I don't believe there's any way to distinguish what type of
query it is through a SQL statement.

 

[betwalk]

Douglas J Steele wrote:
.... Unfortunately, I don't believe there's any way to distinguish what
type of

query it is through a SQL statement.

---------------------------------------------------------

Hmmm. Too bad. But if I plan ahead, I could use the query naming
technique, I guess. That'd do the trick, but a bit more work up
front...

Thanks!

Betsy

 

[dbahooker]

if you were using SQL Server and Access Data Projects you could run
'profiler' to see when a query was last run

it woudl be QUITE easy to do.

MDB became obsolete back in 1999. Don't listen to these MDB script
kiddies