Database Open Close Event Alerts
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 installed on Network for the benefit of few trusted Users. You already knew their Workstation IDs/Network IDs, but you would like to know some one else opens it to explore and when they do catch them red handed. Warn the unauthorized intruder immediately by sending a message to his Workstation informing that he is not in the permitted zone and request him to close it immediately. He will know that you are on watch 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.
You are doing some maintenance work on the Database and you would like to know if some one opens it in the middle of your work. Or you are waiting for the Current User to close the Database to do 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 feed back 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 Users 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 few cases On. You don't need to touch any of your Databases at all for this change over.
After going through the above introduction you may be interested to have a look at it and probably assuming that it is a week-long Project to implement all of this. Or you 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 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 :
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 Server, then it makes 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.
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 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:
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 Module's Declaration area 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 exists:
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 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.
- 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 Server already in use by others. Save the Text File with the name AccsCtrl.txt at the Location on Server specified in the constant declaration line path given above. Databases can be in any location on Server.
- Select New from Notepad File Menu to open a new text File. Save the empty text file on the same location on 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 the history when needed.
This File can be created through code, if not present. But, I thought it is better to leave the option to 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, 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 controls 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.
- 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 turns 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 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 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 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 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 Log file 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 Server.
- Create copies of these Files periodicaly as a backup and keep it in a safe place for restoring it, if the active one got corrupted or deleted by mistake.
- 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
- Write the NetworkID of a known machine in place of the text 'WorkstationID' and a test message replacing the text 'messagetext–.'
- Select Save As from 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 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 a space between the WorkstationID and the Message, then press Enter Key.
When you implement this 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 internal security of Objects and for regulating access rights to 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 twist 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.
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.