Identifying Front End Filename Accessing the Back End

[GKJR]

Hi All,
I'm trying to fine tune my audit trail to be a little more accurate as to who is using the database. I'm running Access 2013 on Windows 10 with split front- and back-ends and about 8 total users. I am currently using the Environ("COMPUTERNAME") function to populate my [ChangedBy] field in my data macro audit trail. Sometimes people may be logged into someone else's computer at my office so I want to be able identify which front-end file is making the changes from the back-end. The problem is that my data macros are stored in the back-end on one machine (we're on a WorkGroup network), so if someone is at a computer that isn't their own but they are in their own front-end file, the function will return the computer name and not the actual user making the changes. I need some kind of function that returns the name of the file that is accessing the back-end.
Any help is much appreciated...
Thanks in advance.

[jforbes]

You could use:

Application.CurrentProject.FullName
or
Application.CurrentProject.Path

to return the path/filename of the executing database.

Another useful piece of information is the Currently logged in Windows Username. This code is floating around in various forms on the Internet:

' Place this in Module
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Public Function GetWindowsUser() As String
    On Error GoTo ErrorOut
 
    Dim lngResponse As Long
    Dim strUserName As String * 32
 
    lngResponse = GetUserName(strUserName, 32)
    GetWindowsUser = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
 
ExitOut:
    Exit Function
ErrorOut:
    MsgBox Err.Description
    Resume ExitOut
End Function
 

[zmbd]

I would suggest using J's second block of code to determine which user is logged in to the PC and it is essentially the same code I use for such things.

File names and the system environment variables can be altered.

This is the code I borrowed from Dev Ashish ages ago.
Following his lead, I needed the name assigned to the PC so I wrote another function based on Ashish's example. Notice that I use the "PtrSafe" in the declaraions to account for 64Bit installs... if for some reason this chokes try deleting it.

This has been tested several times and is in a production database I use:

Option Compare Database
Option Explicit
 
'******************** Code Start **************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
'
'ZMBD: Added the PtrSafe - have some 64bit installs that need this
Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
'
'ZMBD: I want the PC name too - :)
'ZMBD: Added the PtrSafe - have some 64bit installs that need this
Private Declare PtrSafe Function apiGetComputerName Lib "kernel32" Alias _
    "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function
'
'
'ZMBD: Based on the above and from the Win32API_PtrSafe.txt
Function fOSPCName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strPCName As String
    strPCName = String$(254, 0)
    lngLen = 255
    lngX = apiGetComputerName(strPCName, lngLen)
    If (lngX > 0) Then
        fOSPCName = Left$(strPCName, lngLen - 1)
    Else
        fOSPCName = vbNullString
    End If
End Function
 
'******************** Code End ************************