How to track users that open a table in Microsoft Access

Question

I am trying to do an audit trail in Microsoft Access and I need a way to track when users open a table, not a form. Is there a way to run a function every time a table is opened?

Answer 1

Unfortunately, no events are fired when opening tables or queries in a MS Access database. If you do need to have this functionality, there are two options that I can think of, both involve hiding or restricting direct access to the tables:

1. Build a dashboard form, with a command button to open each table and use the Click event of the button to perform your auditing procedure. you'll be able to audit the opening of each table, but not closing. Use database table triggers to do the audit of data updates.

2. Create datasheet view forms of all your tables, and give access to these forms to the user instead of the tables. You'll be able to use all the supported form events to perform your audit, such as opening, closing, data updates.

You can build a combination of both to achieve your goal.

Answer 2

Unfortantly this would have to be done at the UI or application level, not the data engine level. Access since 2010 does have table events and procedural code that can run at the engine level (store procedures). These table triggers and procedural code run independent of Access, and VBA code. In fact if .net or outside applications (non access) even update Access tables with ODBC, then these table triggers and store procedure code will run.

So, these features in the Access data engine (ACE) would allow some kind of audit trail and logging system to be created.

However, JUST WHO opens a table? No, that feature is not available in even SQL server, and most data engines don't have this ability (with the exception of trace features, but these are for testing, and debugging, and cause significant slow downs if such kinds of "logging" is turned on. In fact, ACE also has what is called show-plan. This will show the query plan used by ACE data engine for each and every query. But again, as noted, this kind of logging is for debugging and not suitable for tracking the "act" of a table open.

However, like SQL server, the Access data engine does have table event triggers, but they only run for updates, inserts, deletes.

Even with SQL server, you are limited to such table events. To be fair, SQL server does also have events for "DML" operations (ACE does not). So, drop of tables, or even schema modifications can fire events, the Access ACE engine cannot.

All in all? Even with SQL server, to track/log who opend what table? You quite much need to do this at the UI/applicaiton level and not the data engine level.

If the Access application is built well, then users will never open a table directly anyway - and this holds true for any well written program - you never open/use tables directly anyway the instant you build a application in Access as opposed to just editing and view some tables like how one would in say Excel.

So, you would have to attach some code in the forms on-load. It is quite common to do this, and in several applications, we for example have a "edit" button on a form. The form can be viewed, but to edit, you have to first un-lock. When you click on un-lock, then that event is logged. As a result, we can see a nice list of all users (people) who editing that record. But, we don't log "just" the act of having opened and view the form that displays data from the table.

However, it certainly would be possible to log, and track such actions, but as noted, this would occur at the application level - not data engine level.