Possible to detect incoming connections to an Access db? (2 Viewers)

[burma]

Does anyone know a way to detect whether an incoming connection is made to an Access db, or if a someone links to it? Thanks

 

[theDBguy]

The laccdb file could tell you all the computers currently connected to it.

 

[burma]

The laccdb file could tell you all the computers currently connected to it.

I know about that, I was was wondering if there might be a way to do it in code. I'm particularly interested in links. So if someone links to the db, a way to detect that even if they don't open the linked table.

 

[theDBguy]

I know about that, I was was wondering if there might be a way to do it in code. I'm particularly interested in links. So if someone links to the db, a way to detect that even if they don't open the linked table.

The be cannot tell you about any linked connection until it tries to connect. To programmatically check those connections, look up user roster.

Sent from phone...

 

[The_Doc_Man]

Perhaps a starting point would be the Workspace.Connections collection.

Workspace.Connections property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

The elements found in the .LACCDB or .LDB file will identify the computer name of the computer that initiated a connection to the FE. I believe there is also a BE equivalent but to be honest, I so rarely used it that it was not something I studied too hard.

 

[Edgar_]

I know about that, I was was wondering if there might be a way to do it in code. I'm particularly interested in links. So if someone links to the db, a way to detect that even if they don't open the linked table.

Try this script.

# Set the folder path to monitor
$folderPath = "\\PATH\To\Shared\Folder"

# Create a FileSystemWatcher
$watcher = New-Object System.IO.FileSystemWatcher
$watcher.Path = $folderPath
$watcher.IncludeSubdirectories = $true
$watcher.EnableRaisingEvents = $true

# Define the event action
$action = {
    $changeType = $event.SourceEventArgs.ChangeType
    $path = $event.SourceEventArgs.FullPath
    $fileName = [System.IO.Path]::GetFileName($path)
    $timeStamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    Write-Host "${timeStamp} - ${changeType}: ${fileName}"
}

# Register the events
Register-ObjectEvent $watcher "Created" -Action $action
Register-ObjectEvent $watcher "Changed" -Action $action
Register-ObjectEvent $watcher "Deleted" -Action $action
Register-ObjectEvent $watcher "Renamed" -Action $action

# Keep the script running
do {
    Start-Sleep -Seconds 1
} while ($true)

Here's a screenshot of what happened once I opened a form bound to a linked table. As you can see, it detected the creation of a .laccdb file and its changes. Once I added some records, it detected changes as well, but now on the .accdb file. Finally, when I closed the form, it detected the last change on the .laccdb file and then it detected its deletion.

To use it, just open your notepad, paste the code, save as .ps1 and when you want to run it, right click and choose with power shell. If it asks if you want to change some policy choose yes.

Now, of course, this is just a proof of concept, but you will see what's happening in that folder by using this script. You might want to modify it to do other things based on what it detects.

 

[arnelgp]

you can also view the connection using Computer Management.

 

[NicolasLee]

Hi there! Great question.
Detecting incoming connections to an Access database can be a crucial aspect of maintaining security and monitoring access. One method you might explore is utilizing event logging and auditing features within Access itself. By setting up appropriate logging mechanisms, you can track when connections are established or attempted, helping to identify any unauthorized access attempts or links. Additionally, you may consider implementing network-level monitoring solutions or employing third-party database management tools that offer enhanced visibility into database activity. I hope this helps!

 

[ebs17]

So if someone links to the db, a way to detect that even if they don't open the linked table.

You should set your expectations for a file backend very, very low. A file itself is stupid. What the database engine (Jet/ACE) cannot do will then not be possible. User-dependent control was abolished with Acc2007.

If you have the above-mentioned and other wishes, you should look into an ACTIVE database management system. This is a service, a program. It accepts requests and treats them specifically according to the conditions set and the authorizations granted.

 

[The_Doc_Man]

You are quite correct, @ebs17 - for a passive back-end (native Access .MDB or .ACCDB or the DE or DR variants), you have to go through the Windows file system to see who is touching the file. For an active back end, such as but not limited to SQL Server or ORACLE, you have at least the potential of asking the back-end's "keeper" to track that for you.

 

[ebs17]

to see who is touching the file

However, you don't yet know whether a specific table is being read - the primary aim was to notice whether certain tables are being read.

 

[The_Doc_Man]

That is exactly the problem. Windows does not do record locking. It only does FILE locking. Any finer granularity is up to the application. For a passive Access back-end file, Windows connection data stops at the file's "boundary" - i.e. the raw inbound connection, from Windows's point of view, is to the WHOLE FILE. Only the device drivers of the machine hosting that DB know exactly where within the file there is any activity, and that knowledge is ephemeral at best. Access itself tracks that information, which it ONLY does for very short intervals - particularly short if you have Optimistic Locking rather than Pessimistic Locking. (And it doesn't track it at all if you have No Locks.)

The question being asked may be impossible to answer for a "pure" Access back-end file because all of the "real" connection data is in the Access workspace, a memory structure inside an Access process, except for whatever record is kept in the .LACCDB or .LDB file. Assuming you could even identify the computer having another connection, and THEN assuming you could track that back to another process, you still have a security issue.

The problem with asking another process where it is connected is that Windows security rules will not let you touch the inner workings of an external process. For those who remember the old military "Orange Book" that sets security standards for operating systems that are approved for U.S. government use, the C2 standard includes "inter-task isolation" as one of the four critical requirements. A newer and more stringent standard exists now, but it still includes that requirement.

Therefore, the ONLY way for anyone to EVER find this out for Access is to write the app to leave traces when a connection is made because otherwise, you cannot get there from here. Now, if we are talking about some active SQL engine on a server, the active server system can track that for you better - but again, only if it is set up for that. The problem in a nutshell is that, in general, for most operating systems I know, you CANNOT find out what someone else's process is doing unless it is set up in the first place to tell you what it is doing. Even with privileged processes running as SYSTEM, there is the problem of knowing WHAT to ask and HOW to ask it - and catching it at the right time for the info to be available.

This idea of having the app leave traces is exactly in line with my "Old Programmer's Rule #2" - which is that Access won't tell you anything you didn't tell it first, or at least tell it HOW to tell you. If you are going to require usage information then remember to tell Access how to TELL you that information ahead of time.

 

[Pat Hartman]

I agree, you have no way of identifying which tables are being accessed. However, Data Macros would be able to log updates.