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.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

ZackGeneral IT Goto GuyAuthor 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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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 GuyAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.