count the number of times reports are run

Hi one and all, we have a database with over 150 reports which can be run by any one of our users.  I have built into the database a form where the users can select various criteria for their reports and at this point I can log the number of times each report is opened.  Other users are able to open and run reports and in addition other reports get run as part of an automatic process.  An untold number of reports have been allowed to be made and many are now either duplicated or redundant.  What I would like to achieve is a log table that is able to log the date and time every report gets run, probably by whom.  This is to monitor which reports are not run at all, in order to reduce the size of the database and general housekeeping.  I could put something in the 'on current' event but this would mean that I'd have to do this for each and every report we have.  The solution I'd like, if there is one is one that can log which object is being accessed what its name is and who is accessing it.
Any help as always would be greatly appreciated
Who is Participating?
Dale FyeConnect With a Mentor Commented:
Makes sense to me.  Might want to give this a litte while, then post another titled:

Automate adding code to Report Event

and then explain what you are trying to do.
Dale FyeCommented:
Well, you could do this in the form where the user selects which report to run, but if some of the reports are run automatically, then that option would not be ideal

There may be another way, but the only one that I can think of would be to create a subroutine that does the logging for you, and then use either the Open or Close event of the report (I know, have to do this over 150 times - there may be a way to automate this, but I've never done it).

This might look like:

Private Sub Report_Close


End Sub

Then, subroutine LogReport would simply insert a record into tbl_Reports_Log which includes the report name (passed to the sub) and the current time and user.

buddlesAuthor Commented:
Hi Fyed, thanks for the reply.  I am of the same opinion but before I 'bite the bullett' and get on with it I was wondering if I put it out to you all at EE somebody may have a solution to automate adding code.   if it's ok with you I'll hang this one out for a bit longer
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Jeffrey CoachmanMIS LiasonCommented:
My hope is that you will be feeding this data to a central (back end) database.
...Having this data stored individually for each user might be hard to manage...

...if I am understanding your question...
buddlesAuthor Commented:
Hi Boag2000, no, data is not a problem it is all sitting on a SQL server nicely out of the way of the front end mayhem.  the client and I know that there has been a feeding frenzy in the past in regard to users requesting untold reports.  some, maybe most being now obsolete.  my current task is to clean up the front end which has become bloated over time with unused reports, queries etc,etc.

Hi fyed,
I'm going to take your advice onboard and post again with a different title.  i have written the little snippet to add a log to a table each time a report is opened but I am loath to paste that into 178 reports without at least asking the question :-)
buddlesAuthor Commented:
excellent response and very quick.  many thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.