Garry Robinson's Popular MS Access, Office and VB Resource Site


Home  Contact Us

Order Software

Search vb123

 Smart Access  
The Magazine that Access Developers loved to read and write for is back
Article Index Here or
Purchase Here

RSS & Newsletter  
Join our XML/RSS Newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
Sign up here

Get Good Help
If you need help with a database, our Australian Professionals could be the answer
Read More

123 For Office

Great shortcuts, supports Office 2013 to 2003, easy zipping.
Read More

  The Workbench  Find out who has your database open, start the correct version of Access, easy compacting and zip backups, change startup options,  compile, shutdown database
Read and Download

Access > SqlServer 
Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query
translation and web form conversion.
Read More

Like FMS Products?
Purchase them from us and get a free Workbench or Smart Access  More

Garry's Blog
Find out a few other things that Garry has been writing about Microsoft Access. Read more

About The Editor Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia
Contact Us ...



Next Tip  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)
  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)
  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."
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

  Click here for the Access 2002/2003 sample database file
  Click here for the Access 2000 sample database file

Else Click Here To Purchase if you don't own the Toolshed

Find Out More

This topic and more are discussed at length in Chapter 6 of Garry's Book on Access Protection and Security
Read More

Other Pages At VB123.com That May Be Of Interest

Backing up Multi-User Access Databases
Backing Up or Moving Microsoft Outlook
Implementing a Successful Multi-user Access/JET Application

Click on the following button Next Tip to jump to the next page in the protection samples loop.


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