Unsecured Database and Users Log
When Data Entry or Editing actions are performed on important Tables in Databases a Time-Stamp with User Name is normally saved on each record to mark that event. This is done through the Form’s 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 from manual changes. The familiar function =Now() gives the Date/Time Stamp value and the CurrentUser built-in function provides the User’s Name from the current instance of the database opened in User's Workstation. We are focusing on the usage of CurrentUser function.
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 from 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.
But, if the database is not implemented with Microsoft Access Security then the CurrentUser function will always return the value Admin. Any user 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 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 into 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 UserID and WorkstationID stored in your computer's memory.
- Select Run . . . from Start Menu.
- Type Cmd and click OK. A DOS window will open up.
- Type SET and press ENTER key.
A lengthy list of Environment Values are 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 influences 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 UserID
These may not be in nearby lines but can appear anywhere within the list and if necessary use the Scroll Bar to move the list up or down and when you are sure you have spotted these values then type Exit and press 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 UserID 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 CurrentUser, 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.
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 WorkstationID of the User from Memory.
These are also useful to monitor intrusions from third parties, or somebody not entrusted to maintain the database is opening it 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
Refer the following Posts for more advanced techniques like:
- Record the Open/Close events activity of Databases in a log Text File and send alerts to your machine in a popup message box, which you can turn it on or off as you need them.
- Sending Alerts to other User Workstations with useful information that they need to know about, immediately after processing activities taken place at one side of the User Groups.
- How many Users are currently online with a particular database and how to communicate with them by sending Alerts to their Workstations?
- Send E-Mails to remote Users with attachments of important Reports in Snapshot Format.