Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag for Australia

asked on

Logging/Auditing MS Access network connections.

Hi EE,

How do you make an audit trail remote connections to an MS Access db 2007 to 2013, is this possible?

The more variables that can be captured the better, the queries being entered will primarily be select queries.

Any assistance is welcome.

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you're looking to capture traffic between your Access Frontends and your Access Backend - i.e. queries, performance data, etc - then you're out of luck except for the stuff that John suggests. Access is a file server database, and doesn't have the audit/logging capabilities like SQL Server.
How do you make an audit trail remote connections to an MS Access db 2007 to 2013, is this possible?

 Not possible, unless you build a file monitor into the OS where the DB resides.

 You can build something into an app to log at startup, but that won't tell you if someone connects to the DB from outside of the app (say with Excel).

Jim.
Avatar of Zack

ASKER

Hi Guys,

Cheers for the assistance guys after some research found a terrific link on stackoverflow which gives me exactly what I need:

https://stackoverflow.com/questions/15899930/how-to-see-who-is-using-my-access-database-over-the-network

Thank you.
Zack,

  But that is not what you asked for in your question.  

  Using the JET Show User Roster function only tells you who is in the database at a given point of time.  It's better than reading the .LDB file directly, but is not all that much different.  

  It doesn't "monitor" for connects and disconnects.  You would need to have a task running constantly in order to actually log who touches the database.   And it doesn't do anything beyond that... so it doesn't tell you who ran what queries, forms, reports, etc, which you asked about in your question.

Jim.
Keep in mind that with JET/ACE, the db engine code is executing client side.

There is no central server process as there is with SQL where users can be monitored.  That's why it's just about impossible to do what you asked.

 With JET/ACE, the best you can do is to build something into the app, but that doesn't stop someone from connecting to the DB with something like Excel.

 You would need something on the "server" where the DB resides monitoring who touches the file, and even that would only tell you who and not what.

Jim.
Avatar of Zack

ASKER

Hi Jim,

Correct but its big a step in the right direction a have the db log connections constantly collating the info and back track from there putting logging triggers where I need via vba. The environment in question is that multiple apps are accessing these databases at when specific events occur we are not aware of an excel reports running ad hoc queries against these databases.  

Cheers for the additional clarifications I appreciate it .

Thank you