Guide to Tracking User Activity in Your Access Databases

At times you will find it very handy to know what a user was doing in your application at a specific time.  This information is always useful for debugging or security.     

Use a table and this simple set of code to record all the users transactions that you want to track.  Once you create the table and the module then you simply need to add two lines of code to each transaction like a form open or a form close event.   

Follow these simple steps and you will have a history of activity in your applications.  Step one and two only have to be done once.  Step 3 is needed for every event you want to track.   

1  Create a table to hold the user log information and name it USysLog.    Add these fields   
Event    as    Text  
EvTime   as   date/time   
Save the form and click No when asked to create a Key field.  
 
2  Create a module and name it and add the following code:   'Dimension a public variable at the top of the module.
  Public LogEvent As String   
  'Next add this code in the module  
  Public LogEvent As String    
  Public Function LogEvt()  Dim SQL As String      
  SQL = "INSERT INTO UsysLog ( Event, EvTime ) SELECT '" & LogEvent & "' AS x1, #" & Now() & "# as x2;"  
End Function    
Close the module and save it.   
 Note: in the section of code above you must make sure that the single quote characters are encapsulted in between a pair of double quotes.

3  Next add these two lines of  code to each event that you want to record a time for and edit the first line for the appropriate form name and event.  In this example, you would see the text Logon Opened and an associated time in the UsysLog table.      
  LogEvent =  " Logon Opened " 
  LogEvt    

If you want to log a form opened time then put these lines in the form open event and likewise if you want to log the form close time put it in the form closed event and edit the wording on the first line accordingly.  

Remember that Access has a convention that a table that has a name that starts with Usys will be a hidden table.  You need to go turn on the option to view hidden objects.    On the Access menu go to:  Tools/Options on the View tab check on Hidden Objects and system objects then you will see the table USysLog. 
   
Please take a look at  http://www.accesshelpebook.com/ and see if you can't take advantage of all of the examples found in our Access Help Ebook to help you make your databases more robust.    Also, See our main web site for more details on our services and other Free Access Tips.  http://www.biomationsystems.com  

Our Office Automation Services include solutions used by both large and small companies that are affordable and easy to use.  Our products and services are proven to enable our customers to provide  better service to their customers while saving them money in the process.  Our customers find that their return on investment is most often realized after using their applications just a few times.  You can find more helpful ideas for Access at   http://www.accessdatabasehelp.com   http://www.biomationsystems.com/AccessTips.htm