Unsecured Database and Users Log ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

When performing data entry or editing important database tables, it is common practice to save a timestamp along with the user name for each record to mark the event. This is typically implemented using the form's Before Update event procedure. A sample procedure is provided below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     Me![EditedBy] = CurrentUser
     Me![EditedDt] = Now
End Sub

These fields will be added to the Data Entry/Editing Form from the Table, but will be kept hidden or disabled to prevent manual changes. The familiar =Now() function retrieves the current date and time, while the CurrentUser() Function provides the username of the person currently logged into the database. In this context, we are focusing on the usage of the CurrentUser() function.

The CurrentUser() function returns the correct username in a secured database—that is, a database configured with Microsoft Access security features and shared on a network. When an authorized user opens an instance of the database on their workstation, they must enter their authenticated username and password to gain access. Consequently, the value returned by CurrentUser() will always correctly identify the logged-in user.

**The Admin User.**

However, if the database is not secured with Microsoft Access Security, the CurrentUser() function will always return the value Admin. Any user who opens an unsecured database on a network is automatically logged in by MS-Access as the Admin user, a member of the Admins group, without being prompted for a user name or password.

We will not delve into Microsoft Access Security issues here (I have already dedicated about nineteen pages to this topic under the “Microsoft Access Security” section on the Main Menu of this site). Instead, we will focus on how to capture the network user name and workstation name correctly when working with an unsecured database shared on a network.

Fortunately, there is a simple trick to capture the network user ID and workstation ID and record these values in your table fields. No lengthy VBA program is required. But first, let’s see where this information is stored and how you can retrieve your own network user ID and workstation ID from your computer’s memory.

Finding Computer Name

Select Run from the Start Menu.
Type Cmd and click OK. A DOS window will open up.
Type SET and press the ENTER key.

A long list of environmental variables will appear in the DOS (Disk Operating System) window, the core system underlying the Windows Operating System. These variables are loaded into memory when you start your computer or after logging off and back on, and they play an important role in the smooth execution of your day-to-day tasks.

However, for our purpose, we are interested in only two specific values from this list. Look for the following entries:

COMPUTERNAME=your Computer Name

USERNAME=your Network User ID

These two entries may not appear close to each other in the list of Environment Settings — they can be located anywhere within it. If necessary, use the scroll bar to move up or down through the list until you find them. Once you’ve confirmed their presence, type EXIT and press ENTER to close the DOS window.

In Microsoft Access, there is a built-in function called Environ() that allows you to retrieve these values directly from memory and use them anywhere in your database — such as in forms, reports, or VBA procedures.

Example-1: X = ENVIRON("USERNAME")

This will bring the Network User ID of the User from memory.

Example-2: X = ENVIRON("COMPUTERNAME")

This will get the WorkstationId (Computer Name) from the Environment String that we have seen in Memory.

In fact, we can simplify the use of the Environ() function by creating two small, reusable functions and adding them to a global module in our Function Library. Once defined, these functions can be used anywhere in our application — just like the built-in CurrentUser() function — to retrieve the Network User Name and Workstation (Computer) Name directly from memory.

Since CurrentUser is a built-in function, we will use something different that we can memorize easily. We can even use the Parameter Values of Environ() function as our own function names.

Add the following Functions in the Global Module (Standard Module) of your Function Library Database or in the shared database itself:

Public Function UserName() As String
    UserName = Environ("UserName")
End Function

Public Function ComputerName() As String
    ComputerName = Environ("ComputerName")
End Function

After adding these Functions to the Global Module, you can call them from wherever you want, like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
      If Err then
        Cancel = True
      Else
       Me![EditedBy] = UserName
       Me![EditedDt] = Now
      End IF
End Sub

OR

If Msgbox("Hi, " & UserName & ", Shutdown Application..?", vbQuestion+vbDefaultButton2+vbYesNo,"Shut Down")=vbYes then
     docmd.Quit acQuitSaveAll
End If

Similarly, the ComputerName() Function gets the Workstation ID of the User from Memory.

Monitoring Intrusions

These are also useful to monitor unauthorized use of third parties, or somebody not entrusted to maintain the open database, from the Network Drive, by sending an Alert Message to you from the Startup Screen or the Main Switch Board Form's Open Event Procedure, like the example given below:

Private Sub Form_Open(Cancel As Integer)
       Call Shell("NET SEND YourNetworkID from: " & ComputerName & " User: " & UserName & " opened: " & Currentdb.Name & " at: " & Now())
End Sub

Note: NET SEND was a Windows XP Command; its new Version is MSG Command.

Refer to the following Posts for more advanced techniques:

Record the Open/Close events, and activity of Databases in a log Text File and send alerts to your machine in a pop-up message box, which you can turn on or off as you need them.
Database Open/Close Event Alerts.
Sending Alerts to other User Workstations with useful information that they need to know about, immediately after processing activities take place on one side of the User Groups.
Sending Alerts to Workstations.
How many users are currently online using a particular database, and how to communicate with them by sending Alerts to their Workstations?
Who is online?
Send E-Mails to remote Users with attachments of important Reports in Snapshot Format.
Automated E-Mail Alerts