Logging/Auditing MS Access network connections.

Zack
Zack used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
Well there are many "solutions" regarding this..if you do a little searching here in EE you should find various implementations...
Usually they fall in 3 categories
1 ."Simple" ...just read the locking file ...you retrieve info about the users using the database
2. "A bit more advanced" ...looking for the Open Files from Shared Folders(right click on My computer -->Manage) you get info about Users-Databases-Locks
3. " More Advanced" custom solutions based on logging each and every activity to a table ...using standard Form/Report events (e.g OnOpen event you log that the user [User] from [WorkStationX] opened this form...and of course you can expand it to control events and so on..for an example look at "Creating an Audit Log" from Allen Browne

There are also commercial solutions like Ms Access Total Admin from FMS
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ZackGeneral IT Goto Guy

Author

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
ZackGeneral IT Goto Guy

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial