Database Usage Statistics 1

[jcarmody]

I recently spent about 4 months developing databases for a business unit to track their activities. The project has not yet been completed. However, before I throw more time at it I would like some sense of how often they are using what I've given them thus far. Is there any utility in Access (or an add-in I could purchase) that would log number of updates, adds and inquiries on an Access database?

Thanks -

JC

 

[BobStubbs]

There is a straightforward way to log record inserts and updates, if your users always work through forms.

Create a table called tblUsage, with these fields:

TableName - text, 20 chars, primary key
InsertCount - long integer
UpdateCount - long integer
DeleteCount - long integer

Create a record in this table for each user table - my example table is tblTest1

In the AfterUpdate event of each user form, write this code:

Private Sub Form_AfterUpdate()

Dim strSQL As String

strSQL = "UPDATE tblUsage SET tblUsage.UpdateCount = tblUsage.UpdateCount + 1 "
strSQL = strSQL & "WHERE tblUsage.TableName = 'tblTest1'"

DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)

End Sub

Write similar code into the AfterInsert and After Del Confirm events, to update the other counters.

You will now find that every time a user inserts or updates a record, the appropriate counter in the Usage table increments by 1.

You could extend this technique to other database events - e.g. if users run a report from a command button, you can put similar code in the button's On_Click event to count the number of times the report runs.

You can report on the usage table, reset the counters to zero each month etc. as required.

I hope that this helps.

Bob Stubbs

 

[jcarmody]

Thanks Bob-

This works like a charm!

JC