Track Usage of a Report

[Todd]

Is there a simple way to keep track of how many times a
report is run?

I've created a new database, with a whole bunch of
reports, but I only want to maintain the reports that are
useful to people (ie the ones that get used). So I'd like
to be able to track how many times per day, or week, or
month that reports are run by anyone that uses the db.

 

[Allen Browne]

You could use the Open event of the report to record the usage in a table.
Something like this:

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String

strSQL = "INSERT INTO tblUsageLog ( LogDate, DocName ) " & _
"SELECT Now() AS LogDate, " & Me.Name & " AS DocName;"

dbEngine(0)(0).Execute strSQL, dbFailOnError
End Sub

 

[Todd]

Thanks. I tried that, but I keep getting an error saying
Syntax Error (missing Operator) in query expression.

Any ideas?

 

[Allen Browne]

Watch the word wrap.

Which line generates the error?

Add the line:
Debug.Print strSQL
After running the code, open the Immediate Window (Ctrl+G), and see what it
drops there. Does the statement make sense?

If you have trouble generating the SQL statement, mock up a query, switch to
SQL View (View menu in query design), and look at the example of what you
need.