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

Introduction

When Data Entry or Editing actions are performed on important Tables in Databases a TimeStamp with User Name is normally saved in each record to mark that event. This is done through the Form Before Update Event Procedure. A sample procedure is given 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 function =Now() gives the Date/Time Stamp value and the Current User built-in function provides the User Name, from the current instance of the database opened in the Workstation. We are focusing on the usage of the CurrentUser() method.

The CurrentUser function can return the User Name value correctly from a secured database (from a database that is implemented with Microsoft Access Security features) shared on a Network. When each authorized User attempts to open an instance of the database in her Workstation she has to provide her authenticated User Name and Password before getting access to the database or its other Objects. Consequently, the values returned by the CurrentUser() Function will always point to the correct User.

**The Admin User.**

But, if the database is not implemented with Microsoft Access Security then the CurrentUser() function will always return the value Admin. Any user who opens an Unsecured Database in a network will be silently logged in by MS-Access as Admin User, as a member of the Admins Group Account, and will never prompt for any User Name or Password.

We are not going to explore the Microsoft Access Security issues involved here (I have already dedicated about nineteen Pages on this issue under the Microsoft Access Security topic on the Main Menu of this Site) but how to get the Network User Name and the Workstation Name correctly if we want to record those values, in an unsecured database shared on a Network?

Yes, there is a simple Trick that you can use to capture their Network User Names and Workstation IDs and record those values in the Table fields. No, we don't need any lengthy VBA program to do this. But, first, let us see from where we will get this information. We will try to find your own Network User ID and Workstation ID stored in your computer's memory.

Finding Computer Name

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

A lengthy list of Environmental Values is displayed in the DOS (Disk Operating System, the main driving force behind the Windows Operating System) Window. These are loaded into memory when you turn on the Computer or after Logoff/Logon actions and influence the smooth running of your data-to-day tasks. We are interested in only two values among them and look for the following Values:

COMPUTERNAME=your Computer Name

USERNAME=your Network User ID

These may not be in the nearby lines in the Environment Settings but can appear anywhere within the list, if necessary, use the Scroll Bar to move the list up or down, when you are sure you have spotted these values then type Exit and press the ENTER key to come out of the DOS window.

There is a built-in function ENVIRON() in MS-Access that we can use to capture these values from Memory and use it where we want them in our Database.

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 write two simple Functions with the Environ() Function and add them to our own Function Library in the Global Module. This will simplify the usage of this Function and we will only need to use the Function Name that we have defined, like Current User, to get these values 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 will get 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 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 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 popup 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 with 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