Can I create a global message in Access 2007/SQL Server 20 user, one location network?

[MacroScope]

Is there a way that I can get a message to pop simultaneously on all computers in a network? I have no problem creating the conditions to trigger it, but I don't know if there's any command that will get all stations to respond at the same time. Any help would be appreciated.

 

[dhookom]

How does this question relate to MS Access?

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

I have a network system using MSAccess and I was hoping there was a command within Access that would allow a global message to all stations.

 

[dhookom]

If everyone has an Access application open you can use a timer on a form.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

Yes, everyone has an Access application open. How would the form timer work in this situation?

 

[dhookom]

The timer event on a form can be set to run code at some interval. The code can check the table for a value that is set by someone. If the value in the table has a particular value, it can open a form to display a message.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

OK, thank you. I already have a similar feature created, and it works fine. The timer interval I have is set for one minute, and it does exactly as you have suggested, checking for certain conditions and then popping messages as needed. I just thought it might be possible to actually send a global message command to pop messages system-wide at the same time.

 

[dhookom]

I doubt Access is the preferred tool for network messaging. Your network guys should have some type of tool for this that has nothing to do with Access.

Duane
Hook'D on Access
MS Access MVP

 

[MacroScope]

You're right. Access is not the preferred tool for this, but the message is triggered by events within Access. When a web submission is sent it goes into a queue, and from there anyone on the floor can see that the submission has arrived and can, with a simple double-click, create a new record with all the data fields imported into it. The problem we've had is that we're still debugging the web form, and people still submit data that fails in the SQL Server. I needed a method of notifying people that a submission didn't come through (in which case they have to look at the email submission and manually create a record). Things like invalid birth dates or other data that wasn't correctly formatted would cause a failure, and we're patching each hole as it becomes evident.

In the meantime, we need to know if something failed, and I devised a two table system for web submissions. The first thing that happens is a new table is populated with the WebID and submit time, and there's also a bit field called Confirmed. By default that field is set to False. Since the fields are computer generated they're not subject to human error in data entry and they go through to that table without ever failing.

Then the regular submission occurs going into a different table, and the very last step in that process sets the Confirmed field in the first table to True. By this method, the only way that a record can exist in the first table with its Confirmed field set to False is if it failed to complete.

Now it's a simple matter of checking the WebID table for any records whose Confirmed field is False, and that in turn triggers the message that I had hoped to control globally. Since it doesn't seem to be something that Access does I am using the method you mentioned, which is the event timer on a form that opens with the database in hidden mode so that it can't be accidentally closed by a user. It has existed for several years, runs every 60 seconds, and checks many different things in about a dozen different steps, but the clock is independent for each work station. That's why I wanted the global message.

I appreciate the help, and it will work this way. We are now notified within seconds of the failure. I just wanted a single unified message command that all stations would react to if it was something that could be done.

BTW, when that routine runs it does a time comparison between current time and submit time, and when that exceeds 60 seconds it sets the Confirmed field to True. The assumption is that everyone has been notified and that stops the message from repeating indefinitely.