Retrieving the Computer Name, Windows User ID, and Access Workgroup ID by Using VBA Code

This is a sample from the Microsoft Access Protection book by Garry Robinson
Applies to Access 97 or Later

In Microsoft Windows NT or Windows 95 or later, you can use the Win32 application programming interface (API) to retrieve network information, such as the user name, workgroup, and computer name, about the currently running computer.
Access does not offer the built-in functionality to access the computer’s current user name. You can, however, use the Declare statement in a VBA procedure to call a Microsoft Windows function that will return the current user name.

NOTE An application programming interface (API) is a set of routines that application programs use to request and perform lower-level services. The operating system performs these lower-level services.

When you need capabilities that go beyond the core language and controls provided with VBA, you can make direct calls to procedures contained in dynamic-link libraries (DLLs). By calling procedures in DLLs, you can access the thousands of procedures that form the backbone of the Microsoft Windows operating system, as well as routines written in other languages.

As their name suggests, DLLs are libraries of procedures that applications can link to and use at runtime rather than link to statically at compile time. The libraries can be updated independently of the application, and many applications can share a single DLL. Microsoft Windows itself uses many of these DLLs, and other applications call the procedures within these libraries to display Windows and graphics, manage memory, or perform other tasks.
You can find the procedures to retrieve the Windows user ID, computer name, and workgroup ID in a module called basGR8_Startup in the demonstration database.
Probably the most important item of information that you need to retrieve for logging is the Windows user ID. Figure 6-12 shows the Windows XP fast logon form. In this case, the Windows user ID could be either “Garry Robinson” or “Guest.”

Figure 6-12. The Windows XP user ID as shown in fast user switching mode.

Retrieving the Windows User ID

To retrieve the Windows user ID, you have to leave the confines of Access VBA and venture into Windows API programming. Though this may seem a little risky and difficult, I can assure you that these examples are both well tested and will work on all Windows platforms from Access 98 and later. First, you need to make a declaration at the top of the module, as follows:

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

This declaration refers to a file called advapi32.dll. You can search for this file in your Windows software directory. Now the VBA code that retrieves the Windows user ID follows:

Public Function User_FX() As String
On Error Resume Next
Dim lSize As Long
Dim lpstrBuffer As String, trimStr As String
lSize = 255
lpstrBuffer = Space$(lSize)

If GetUserName(lpstrBuffer, lSize) Then
  User_FX = left$(lpstrBuffer, lSize - 1)
Else
  User_FX = "Unknown"
End If
End Function

The following code snippet shows how to use this function:

Msgbox "Your Windows User ID is : " & User_FX

Retrieving the Computer Name

The second useful piece of information that you will want to log is the name of the currently running computer. This name too is retrieved by using a Windows API and requires a declaration at the top of the module. After that declaration, you will find the code for the module ComputerName_FX:

' API declared to find the current computer name.
Public Declare Function GetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function ComputerName_FX() As String

' Function calls the API function and returns a string of the computer name.
 

On Error Resume Next
Dim lSize As Long
Dim lpstrBuffer As String
lSize = 255
lpstrBuffer = Space$(lSize)
If GetComputerName(lpstrBuffer, lSize) Then
  ComputerName_FX = left$(lpstrBuffer, lSize)
Else
  ComputerName_FX = ""
End If
End Function

In the next line of code, I show you how you might use this function in your application:

Msgbox "The Computer that I am using is called: " & ComputerName_FX "

Retrieving the Workgroup User Name

The other useful piece of information that I like to add to the UserLogs table is the Access workgroup user name. The Access CurrentUser method returns the user name, as shown in the following example:

MsgBox("The current user is: " & CurrentUser)

If you are wondering what I am talking about when I say logged into a workgroup or workgroup user name, you probably are in the same boat as all database users who don’t have workgroup file security. In this case, you most likely have used the workgroup file that does not have a password for the default Admin account. When that happens, you never actually have to enter a user name and password and the CurrentUser function returns “Admin.” You may want to test for this Admin account in your software to find users who are using one of these types of workgroups. The following example shows you a test that you can use:

If CurrentUser = "Admin " Then
  msgBox "Please use the correct Access workgroup file."
  doCmd.Quit
end if

Workgroup file security is an integral part of Access security, so you can rest assured that a lot of black ink will be devoted to that topic in Chapters 8 through 11. But for the time being, let’s now explore what we can do to log people who are using our database but have avoided our user surveillance and are opening the database without using our startup software sequences.

Sample Databases

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals