Solved

Display Message to All Current Users that a certain table ("form") has been updated

Posted on 2004-10-18
8
215 Views
Last Modified: 2006-11-17
I have a database that will be used by several employees (about 4 - 5). The back end is stored in the server and the front end in the employee's workstation. When one of the users makes a change, I want that change reflected for all the users who have the database open at the time the change occurred. For some of the changes, I would like a message displayed to alert other users about the change.

Thanks for your help,
Cindy
0
Comment
Question by:cindyrod
8 Comments
 
LVL 84
ID: 12337548
One method is to have your frontends continuously poll a particular table (or file, etc) to detect a change in the table or file, and display a message based on the result. For example, let's say you build a form in your frontend and open (and hide) this form on startup. Set the form's Timer to perhaps 1000 ms (1 second) and set the Timer Event to something like this:

Sub Form_Timer(0
  If Dir("PathToYourFile") <> "" Then
    '/found the file, do something
  End IF
End Sub

To send a message, I'd probably use an .ini file ... you can then read various sections of the ini file (and set/retrieve these sections and values pretty easily), thereby providing a way to distribute messages. For example, consider this .ini file

[Message]
Value="This is a test message"

We'll name this file Message.ini and save it at //Server1/Data/YourDatabase

In your frontend, build a new form named frmTimer. Set the Timer interval = 1000 ms (1 second), and set the On Timer event to this:

Dim strMessage As String

strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "Message", "Value")
If strMessage <> "none" Then
  Msgbox strMessage
End If

To set your message, you call WriteINI

WriteINI "//Server1/Data/YourDatabase/Message.ini", "Message", "Value", "Your Message Here"

************************* CODE ***************************************************
Copy/paste the code below into a new, standard module. This allows you to easily get/set values from an INI file:

  Option Compare Database
  Option Explicit

  Declare Function GetPrivateProfileString Lib "kernel32.dll" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
  Declare Function WritePrivateProfileString Lib "kernel32.dll" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpString As String, ByVal lpFileName As String) As Long

Function WriteINI(INIFileName As String, INISection As String, INISetting As String, ValueToWrite As String) As Boolean
'/Purpose:
'/Created: 10/10/2004 07:25 AM
'/Created By: Scott

  Dim retVal      As Long

On Error GoTo Err_WriteINI
  retVal = WritePrivateProfileString(INISection, INISetting, ValueToWrite, INIFileName)


Exit_WriteINI:
  On Error Resume Next
  Exit Function

Err_WriteINI:
  Select Case Err
    'case
    Case Else
     MsgBox Err & ":" & Error$, vbCritical, "basINIRoutines" & ": " & "WriteINI"
  End Select
 
  Resume Exit_WriteINI
End Function

Function GetINI(INIFileName As String, INISection As String, INISetting As String) As String
'/Purpose:
'/Created: 10/10/2004 07:26 AM
'/Created By: Scott

  Dim mvarData       As String  ' receives the value read from the INI file
  Dim lngLength      As Long  ' receives length of the returned string

On Error GoTo Err_GetINI
  mvarData = Space(255)  ' provide enough room for the function to put the value into the buffer
  ' Read from the INI file
  lngLength = GetPrivateProfileString(INISection, INISetting, "none", mvarData, 255, INIFileName)
  mvarData = Left(mvarData, lngLength)  ' extract the returned string from the buffer
  GetINI = mvarData


Exit_GetINI:
  On Error Resume Next
  Exit Function

Err_GetINI:
  Select Case Err
    'case
    Case Else
     MsgBox Err & ":" & Error$, vbCritical, "basINIRoutines" & ": " & "GetINI"
  End Select
 
  Resume Exit_GetINI
End Function
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12337873
LSM, for my own interest's sake, what is the benefit of using an ini file, instead of a table in the backend to store (and retrieve where necessary) the messages?
0
 
LVL 84
ID: 12338010
Really none, other than users NOT in the database can quickly and easily change the message (like the IT guy who needs to inform all users that they'll need to shut down the server at 2:00 for maintenance). Of course, the IT guy could just as easily change the message in the table or use a system function to do this ...

The other benefit is the ability to tailor messages to different groups, or to send messages to individuals. Add a Section to the INI file like this:

[GroupMessage]
DataEntry = "New tax tables will be installed on Feb. 1. Please complete all pending tax-related items prior to this date."
Telecom = "Telecom meeting this Wednesday in Room 2 at 2:00 p.m. - please review NewTeleComProcedures.doc in the common doc folder prior to meeting."

[UserMessage]
smcdaniel = "Scott, your brand new company-supplied Ferrari is now ready."

You'd of course have to alter the code I supplied to check these different sections:

strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "UserMessage", CurrentUser)
strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "GroupMessage", "DataEntry")


Scott (aka LSMConulsting)
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12339982
Scott, thanks for that, much appreciated.

Just one small error that I picked up in your code.

[GroupMessage]
DataEntry = "New tax tables will be installed on Feb. 1. Please complete all pending tax-related items prior to this date."
Telecom = "Telecom meeting this Wednesday in Room 2 at 2:00 p.m. - please review NewTeleComProcedures.doc in the common doc folder prior to meeting."

[UserMessage]
smcdaniel = "Scott, your brand new company-supplied Ferrari is now ready."

You'd of course have to alter the code I supplied to check these different sections:

strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "UserMessage", CurrentUser)
strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "GroupMessage", "DataEntry")


Should read


[GroupMessage]
DataEntry = "New tax tables will be installed on Feb. 1. Please complete all pending tax-related items prior to this date."
Telecom = "Telecom meeting this Wednesday in Room 2 at 2:00 p.m. - please review NewTeleComProcedures.doc in the common doc folder prior to meeting."

[UserMessage]
smcdaniel = "Scott, your brand new company-supplied Ferrari is now ready. They only had bright pink available, hope thats OK"

You'd of course have to alter the code I supplied to check these different sections:

strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "UserMessage", CurrentUser)
strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "GroupMessage", "DataEntry")



0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 84
ID: 12341478
LOL ... good one!!!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12342641
You might want to also compare strMessage to the previously found value of strMessage and only display the message box if the two are different (to stop the message from popping up every second until the INI file's parameter is set to "none".
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12415146
Will that slow down the server or cause other performance issues? I just thought it would be simpler, but I guess not.
Instead of displaying a message, I think I would prefer to just update the tables so that other users can see the updated
data. Let's say that two users (A and B) have the database open and are looking at different records within the same table (using a "bound" form) (Note: they're using their own front-end mdb's, but the same back-end mdb in the server). User A updates record 1 while User B is viewing some other record. User A saves record 1. Then User B views record 1. How come he sees the old data instead of the new data? Do I have to load every record individually? Does using a bound form loads all the data when the form is opened? What can I do so that the new data will be seen by other users without having to close the form or the mdb?

Thank you so much everybody for your help.

BTW, what's an .ini file?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 12417286
RE: data concurrency ... users should immediately see updates made by other users unless your records are very, very small (access gets data in "chunks", therefore is record 1, 2, & 3 are in one chunk, and I edit record 1 while you're viewing record 2, you may - or may not - see my changes). You could try issuing a DBEngine.Idle dbRefreshCache command in your form's Current event ... this might be too much of a performance hit, however, so make sure this is a real issue. Also, until UserA actually saves their changes, UserB will not see any changes ... Access stores changes in a buffer and only writes those changes when prompted to do so ...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now