Database Open Close Event Alerts ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

When your application is installed on a network and shared by multiple users, it’s often useful to monitor database activity, such as which user opens or closes the database, and when these events occur.

In some cases, your database may not be secured (i.e., anyone with access to the shared location can open it). Even if it’s primarily intended for a few trusted users whose Workstation IDs or Network IDs you already know, it’s still possible for unauthorized users to explore it out of curiosity.

To address this, you can implement a monitoring and alert system within the database. When someone opens the database, the system records their workstation or network ID, along with the login time. If an unauthorized user attempts to open it, the system can immediately send a message to that workstation. The intruder will receive a warning message to close the database immediately. This action makes it clear that their activity is being monitored, discouraging future unauthorized access attempts.

You can also use the same mechanism for legitimate administrative purposes. For example:

To receive an alert when someone opens the database during maintenance work, so you can prevent data conflicts.
To detect when the current active user has closed the database, allowing you to safely make updates.
To send a message to an active user directly from your system — for example, requesting them to close the database so you can apply fixes, or notifying them that maintenance tasks are complete, without needing to contact them by phone or in person.

It’s both interesting and highly practical to automate certain administrative tasks and receive timely feedback from your databases. This allows you to plan and execute maintenance or coordination activities more efficiently, keeping in constant touch with both your users and databases.

All of these capabilities become possible once your databases can send alert messages directly to your workstation whenever users open or close them. For implementation details, refer to the article titled “Sending Alerts to Workstations Based on Process-Related Activities within a Database.” Once you know each user’s workstation ID, you can even send messages back to their PCs directly from your desktop whenever you need to communicate with them.

You can also explore the related article “Who Is Online,” where we discuss alternative approaches to these same monitoring concepts.

If you manage multiple applications across the network, you may occasionally receive too many alert messages. That’s not a problem; you can easily turn specific alerts on or off without having to modify any of your databases individually.

At first glance, this might sound like a large or complex project, but it’s actually quite simple to implement. You can set it up in just about ten minutes, and you’ll immediately see it in action. All you need is a Windows network environment in your office, nothing more.

The Alert Message Contents

The Alert Messages must be loaded with the following information when received:

User Workstation-ID
Date and Time
Database PathName
Status info (Open/Close)
User Network ID
MS-Access User ID

Our Network Administrator was kind enough to set my Last Name (PILLAI) as my WorkStation ID. The images of sample Network Messenger Alerts that come to my WorkStation are given below.

The Database Open Event Alert Message.

Right-click to open Large Image in New Tab/Window.

Database Close Event Alert Message

Right-click to open Large Image in New Tab/Window.

All we need for this interesting Trick is a VBA Routine (given below) and 3 text files. The VBA Program must be copied to the Global Module of your Database and called from the Main Switchboard (Control Screen) in the On Open and On Close Event Procedures.

If you have a Library Database already attached to your Databases from a Network Location, then place the main Code in the Library Database only. Then you need to install only two lines of code in the On Open and On Close Event Procedures in the Main Switchboard Form (Control Screen) in your Databases.

Prepare for a Trial Run

But for now, let us try it in one of your Databases installed in a Network Location shared by Users. When you are ready to implement it in other Databases, you can do so following the same procedure. The Names and usage of the three Text files (all of them are Optional), created for your control purposes, are given below:

File-1: AccsCtrl.txt - List of Databases where the Message Alert Programs are running.

File-2: AccLog.txt - Message Alert Log entries for reference purposes.

File-3: Message.BAT - For sending messages and alerts from your PC to your Database Users' Workstation.

Copy and paste the following VBA Code into a Global Module of your Project or Library Database and save it:

The OpenCloseAlert() Function

Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt"
Public Const acclogtxt As String = "h:\inhsys\comlib\AccsLog.txt"

Public Function OpenCloseAlert(ByVal mstat As String)
'-----------------------------------------------------------
'Author: a.p.r. pillai
'URL   : www.msaccesstips.com
'Date  : 20/05/2008
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim t As Double, WorkStationID As String * 15
Dim netUserName As String * 15, accUserName As String * 15
Dim str As String, loc As Integer, AlertMsg As String
Dim AlertFlag As String,  flag As Boolean
Dim chk As String, dbpath As String, m_stat As String * 1
Dim status As String * 8, dttime As String * 22

On Error GoTo OpenCloseAlert_Err

dbpath = CurrentDb.Name
m_stat = mstat
str = Dir(accmsgtxt)
If Len(str) = 0 Then
'accsctrl.txt File Not Found   
'ignore ON/OFF flag checking   
'proceed to Alert Message Section
   GoTo nextstep
End If

'open the control file and check the Status
Open accmsgtxt For Input As #1
AlertFlag = " ": flag = False
Do While Not EOF(1)
Input #1, chk
If flag = False And Left(chk, 5) = "ALERT" Then
   flag = True
   chk = UCase(chk)
   loc = InStr(6, chk, "OFF")
   If loc > 0 Then     'Turn off Alerts from All Databases
     Exit Function
   Else
     GoTo readnext
   End If
End If

loc = InStr(1, chk, dbpath)
If loc > 0 Then'database file found, take the flag value
  AlertFlag = Right(Trim(chk), 1)
  Exit Do
End If
readnext:
Loop
Close #1

If AlertFlag = "0" Or Len(AlertFlag) = 0 Then   'Turn off Alert in this Database
   Exit Function
End If

nextstep:

WorkStationID = Environ("COMPUTERNAME")
netUserName = Environ("USERNAME")
accUserName = CurrentUser

status = IIf(Left(mstat, 1) = "O", "OPEN", "CLOSE")
dttime = Format(Now(), "mm/dd/yyyy hh:nn:ss")

AlertMsg = LCase(CurrentDb.Name) & " OPEN/CLOSE Status : " & vbCr & vbCr & "STATUS............: " & IIf(Left(mstat, 1) = "O", "OPEN", "CLOSE") & vbCr & "WORKSTATION ID....: " & WorkStationID & vbCr & "NETWORK USERNAME..: " & netUserName & vbCr & "ACCESS USERNAME...: " & accUserName & vbCr 

'insert your Workstation ID replacing the text
 Call Shell("NET SEND  " & AlertMsg, vbHide)

str = Dir(acclogtxt)
If Len(str) = 0 Then
  GoTo OpenCloseAlert_Exit
Else
  Open acclogtxt For Append As #1
  Print #1, status; dttime; WorkStationID; netUserName; accUserName; CurrentDb.Name
  Close #1
End If

OpenCloseAlert_Exit:
Exit Function

OpenCloseAlert_Err:
MsgBox Err.Description, , "OpenCloseAlert()"
Resume OpenCloseAlert_Exit
End Function

The first two lines in the above code must be placed in the Global Declaration area of the Module, below the Option Declarations, as indicated below.

Option Compare Database
Option Explicit
Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt"
Public Const acclogtxt As String = "h:\inhsys\comlib\AccsLog.txt"

Copy the following lines in the VBA Module of the Main Switchboard (Control Screen) Form or copy only the middle line into their respective Event Procedures, if they already exist:
```
Private Sub Form_Open(Cancel As Integer)
     OpenCloseAlert "Open"
End Sub

Private Sub Form_Close()
     OpenCloseAlert "Close"
End Sub
```
We will create the Text Files AccsCtrl.txt and AccsLog.txt in a common location on the Server, where all your Application Users have Access Rights. Change the Server Path h:\inhsys\comlib in the Constant Declaration lines to the common location Path on your Server. Do not use UNC Path specification \\Server\Folder\database.

Note: The term Path represents the location address of a file without the File Name, but Path Name denotes the location Address including the File Name and File Name Extension.
Create Text Files
Open Windows Notepad (Start -> Program Files -> Accessories -> Notepad). Copy and paste the following lines into the blank Notepad File:
```
ALERT=ON
H:\FolderName\FolderName\MYDB1.MDB=1
G:\FolderName\MYDB2.MDB=0
```
Change the second and third lines to point to your Databases on a Server already in use by others. Save the Text File with the name AccsCtrl.txt at the Location on the Server specified in the constant declaration line path given above. Databases can be in any location on the Server.
Select New from Notepad File Menu to open a new text File. Save the empty text file in the same location on the Server with the name AccsLog.txt. The Log details of the Alert Messages that you receive will be saved in this file, too. You can open and view history when needed.

This file can be created programmatically if it doesn’t already exist. However, I decided to leave that choice to you, depending on whether you want to maintain it or not. If you have multiple applications running with this alert mechanism enabled, the log file may grow rapidly, consuming a significant portion of your disk quota.

You can experiment with it to observe the frequency of logged events and determine whether ongoing logging is necessary. It’s also a good practice to periodically review and manage the file’s contents — either by deleting older entries or by archiving them to another file on your local drive to keep disk usage under control.
Create Desktop Shortcuts to these Files so that you can open them quickly, and edit or view the contents when needed. Right-click on the Desktop, highlight New, and select Shortcut from the displayed menu. Browse to the location of the AccsCtrl.txt file and select it, click OK, click Next, then click Finish.

Let us look into the Logic of the Main Program OpenCloseAlert() and how the File AccsCtrl.txt is interpreted, and control the execution of the Program.
- If you have not created the File AccsCtrl.txt at all (as per the declaration Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt" In the specified location, you will still receive the Alert Messages, but you cannot control them by switching them off or on at will.
- If you have created the File AccsCtrl.txt but forgot to add the Database Path Name with flag 1 at the end, then the Alerts will not trigger for that Database.
- If the first line in this file is ALERT=OFF, then further lines in this file will not be processed and turn off the Alerts from all Databases.
- Any value after the word ALERT= other than OFF (ON or anything else except space) will be ignored, and will proceed with the individual Database level flag checking. We opted out of checking the ON flag setting because we are doing it at the individual database level to trigger the Alerts. Besides, this line was introduced to switch off all alerts from all databases with one change. If you prefer to control with an individual file-level flag setting (which is explained below), you don't need to put this line at all in this File.
- If you place the ALERT=OFF statement after a few lines of the Database Path Names (see Step 4) in the Text File, then the program always checks the lines above this statement and executes the program OpenCloseAlert(). This depends on the logic setting (1 or 0) at the Database level. But, Path Names after this line are ignored and never checked.
- We have added two sample Database Path Names after the first line with Alert status flag =1 and =0 at the end (1= Alerts ON, 0=Alerts OFF). You may add any number of Databases in this way, with flag value 0 or 1 after the equal (=) sign, after installing the Program OpenCloseAlert() in the Database.
- When the program encounters the Database Path Name, it extracts the last character from that line to check the status. Ensure that you are not adding any other characters after 0 or 1 except white spaces.
- If the flag is 0, then the Alert Message will be turned off for that Database. Any other value will send it.
- By adjusting the values in this file, you can control Alerts coming to you without touching any of your Databases.
- If you have not created the Logfile AccsLog.txt, the program will not generate any error, but the message history will not be maintained. You can delete old records from this file to keep the file size small on the Server.
- Create copies of these Files periodically as a backup and keep them in a safe place for restoring, if the active one gets corrupted or deleted by mistake.
The DOS Batch File.
We will create a DOS Batch File on your Desktop for sending messages to your Database Users as and when the need arises. Open Windows Notepad and write the following line:
```
NET SEND WorkstationID messagetext
```
Note: The NET SEND Command works under Windows XP only. Later versions of Windows use the MSG Command. C:\MSG /? - The Command displays the details of different Parameters that can be used with this Command.
Write the network of a known machine in place of the text 'WorkstationID' and a test message, replacing the text 'message text--.'
Select Save As from the File menu and browse to your Desktop (C:\Documents and Settings\your network ID\Desktop) and save it with the name MESSAGE.BAT. Select All Files in the Save As Type control before saving it. The MESSAGE.BAT name will appear on your Desktop.
Since this file is on your Desktop, you can always right-click on it, select Edit from the displayed menu and open it in Notepad and change the WorkStationID and message text, and close it.
Double-click on the Desktop icon of MESSAGE.BAT File to send the message after making changes. Batch Files are treated as programs, and if you face any security issues, please check with your System Administrator. Alternatively, you can send Network messages directly from the DOS Prompt. Open Run from the Start Menu. Type cmd and click OK. Type the NET SEND command followed by WorkstationID and your Message Text, giving a space between the WorkstationID and the Message, then press Enter Key.

When you implement this method correctly, you can keep track of your Databases, and the log file (AccsLog.txt) will record the history of activities. If you need, you can link this Text File to a Database for analysis purposes and find out the frequency and User-level time spent, and so on.

When you communicate with your Database Users, asking them to open or close the Database, they will also know that you are watching over their activities. You can open and check the AccsLog.txt file contents to find out who is active and for how long, etc.

You must consider securing your Databases for the internal security of Objects and for regulating access rights to different categories of Users if you have not done so already. Read the Pages Microsoft Access Security for details on this very powerful feature. It may take some time to understand all the twists and turns of this feature. But if you start using it, you can learn it over a period of time by experimenting. Believe me, if you are a serious MS-Access Developer, you must learn it. Doesn't matter how long it takes to understand the rules. You can post your doubts over Security issues in the Comments Section. I will extend all possible help that I can give you.

The Trial Run.

Now the stage is all set for trial Runs. Ask one of your Application Users to open the above Database from their Workstation. You must get a similar Pop-up message, like the sample image given above, during both Opening and Closing events. Ask more Users to open the same Database from their PCs, too. You must get Alerts from each instance of the same Database.

You can implement the same Code in other Databases also and add their Path Names in the AccsCtrl.txt file.