buddles
asked on
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
Any help as always would be greatly appreciated
ASKER
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
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...
...Having this data stored individually for each user might be hard to manage...
...if I am understanding your question...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :-)
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 :-)
ASKER
excellent response and very quick. many thanks
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
LogReport me.name
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.