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

Introduction.

Your application is installed on a Network and shared by different Users. You would like to monitor the activities of this Database, like which User Opens or Closes it, and at what time? Or your database is not a Secured one (which means anybody can open it) and is installed on a Network, for the benefit of a few trusted Users. You already knew their Workstation IDs/Network IDs, but you would like to know someone else is opening it to explore and when they do catch them red-handed. Warn the unauthorized intruder immediately by sending a message to his Workstation informing him that he is not in the permitted zone and requesting him to close the database immediately. He will know that you are observing the activities of over your Database and will not attempt a second time when you are around! If he responded positively by closing the Database then you will get an Alert message from the Database transmitted by the Closing Event.

Or you are doing some maintenance work on the Database and you would like to know if someone opens it in the middle of your work. Or you are waiting for the Current User to close the Database to make some changes. Or you would like to send a message to the Active User (without lifting your Telephone), asking her to shut down the Application so that you can fix some bugs or want to inform her about the completion of the maintenance tasks by sending a message to her PC.

It is interesting and very useful to automate some of these tasks. Get timely feedback from your Databases so that you can plan your actions and execute them conveniently, keeping in touch with your Users and Databases all the time.

All these tricks are possible when you get Alert Messages from your Databases to your PC immediately after the User(s) opens or closes it. Visit the Page Titled: Sending Alerts to Workstations based on process-related activities within a Database. Once you get the User's Workstation ID you can send messages back to them from your Desktop, if you need to communicate with them. Read the earlier Article Titled: Who is Online, where we have approached some of these issues differently.

You have several Applications on the Network. Alert Messages coming to you are too many, then switch them Off or keep a few cases On. You don't need to touch any of your Databases at all for this changeover.

After going through the above introduction you may be interested to have a look at it and probably assume that it is a big project for implementing all of these features. Or you have already come to the conclusion that the whole thing is going to be so complicated you don't want to touch it at all. Relax, it is so simple you will love it, I am sure about it. If you have 10 minutes of your time to spare, you can implement it and try it out. All you need is a Windows Network in your Office.

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 comes to my WorkStation are given below.

The Database Open Event Alert Message.

Database Close Event Alert Message

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 call it from the Main Switchboard (Control Screen) in the On Open and On Close Event Procedures.

If you have a Library Database already linked with your Applications from a Common Location on the Server, then it makes it much easier to implement by placing 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 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 on the Global Declaration area of 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 like 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 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 through code, if not present. But, I thought it was better to leave the option for you to decide, whether you need it or not. If you have several Applications running with this Alerts mechanism the log file can grow very fast and can shrink the allocated space on your Disk Quota. You may try this out and watch the frequency of events. You can periodically review the contents and delete old entries or copy them into another file on Local Drive.
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 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 contents are 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, still you will receive the Alert Messages but you cannot control it by switching 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 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() depending 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 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 is not 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 got 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 which 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 DOS Prompt. Open Run from Start Menu. Type cmd and click OK. Type the NET SEND command followed by WorkstationID and your Message Text, giving 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 a different category of Users if you have not done that so far. 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 could 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 Popup 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.