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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
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...
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Dale FyeOwner, Developing Solutions LLCCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.