Avatar of Zack
Zack
Flag 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.
Microsoft AccessVBA

Avatar of undefined
Last Comment
Zack

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
John Tsioumpris

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott McDaniel (EE MVE )

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)

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.
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jim Dettman (EE MVE)

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)

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.
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.