VBA Module to Get Logged In User Name from Windows API

LoggedInUserName.bas

Attribute VB_Name = "LoggedInUserName"
Option Explicit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function GetLoggedInUserName() As String
    Dim MaxBufferLength As Long
    MaxBufferLength = 255

    Dim UserNameBuffer As String
    UserNameBuffer = String$(MaxBufferLength - 1, 0)

    Dim APIResult As Long
    APIResult = apiGetUserName(UserNameBuffer, MaxBufferLength)

    If (APIResult > 0) Then
        GetLoggedInUserName = Left$(UserNameBuffer, MaxBufferLength - 1)
    Else
        GetLoggedInUserName = vbNullString
    End If
End Function

Public Sub UnitTest_LoggedInUserName()
    Debug.Print GetLoggedInUserName()
End Sub

UserName.sql

SELECT GetLoggedInUserName() AS UserName
FROM [UserTable];

The function GetLoggedInUserName will return the logged in user's username, using the Windows API. The API call far less likely to be spoofed than the otherwise comparable method of reading the USERNAME environment variable via Environ("USERNAME").

GetLoggedInUserName is defined in a module, rather than a class, so the function is accessible in queries, such as in "UserName.sql".

If you wish to look up the username for use in a Data Macro, you need to:

1. Create a SQL query that calls GetLoggedInUserName and selects from an actual table that returns at least one row. For example: "UserName.sql".
2. Create a LookupRecord macro step that calls the SQL query.
3. Create a SetLocalVar macro step within the LookupRecord group that stores the SQL query column into a local variable.