Run Single Update In Multi User Environment

abbaddon223

Registered User.
Local time
Today, 07:07
Joined
Mar 13, 2010
Messages
162
Hi,

I'm hoping someone can help with what has been a long standing issue.

I have a multi user database which is deployed over terminal services to end users - all is well on this front.

The issue is, I need my database to run a macro (with a couple of queries in) every two minutes. I can get this to happen fine with On Timer Event.

Here's the issue, unless I create a seperate front end (Admin_FE), then any user logged into the database on the form which triggers the update will trigger it (which can be 20 - 30 users per app). I only want this update to run once every two minutes - not every two minutes per user!

Any help appreciated!!
 
If there is no logic to specify only one machine actually run the timer events, then yes ALL machines will end up running the timer events.

Is there some way that you can in the VBA code identify the one machine you want to run the timer events? If so, then key off of that attribute and only enable the timer on that client instance. For all the rest of the clients, skip over the timer event setup as the logic did not ring true for those other client machines.
 
http://www.vb123.com/toolshed/05_access/remotedesktop.htm
I am looking up some other links,
This Access site could really use a T/S Citrix - group.
As Access 2013 comes out with all of its limitations, there will probably be a huge surge in T/S or Citrix deployment.

Here is the bat file that Bob Larson was nice enough to share. It works on our Citrix environments.
http://www.access-programmers.co.uk/forums/showthread.php?t=196020
See attachment:
Each time a user logs in, a copy of the "Distribution" is made in that user's folder. Each user logs into their personal folder.

The developer keeps a version of what is under development. When they are ready to release, they go to one folder on the network, change the name to match the bat-file, and replace the old one.
The next time anyone logs in, they automatically have the current Distribution.

The trick is, if new linked tables are create to replace an old table - the this must be done when everyone is off line.
However, if it is just updating forms, queries, reports - connected to the same linked tables on the back-end, it can be mid day.

Let me know if this is the kind of information you are looking for.
My first Access 97 DB was distributed via Citrix in 1999 over dial-up to Long-Term Health centers across the nation. It makes version control and release very simple. Would encourage any network enterprise operation to look into it. It saves all that messy ODBC settings and MS Office patch management business.
 

Attachments

  • Citrix DB Release public.gif
    Citrix DB Release public.gif
    66.7 KB · Views: 98
Last edited:
As for the timer event. My users are nation wide. They use portable PC and Apple in hotel rooms and while on-site (cell wireless data) during audits and inspections.
A new problem arose (thanks to emerging wireless technology) where they just close the lid and didn't log off. Sometimes awaiting for a transaction confirmation.
The session is on hold back on the server waiting for them the next time they get on.

Well, what if I want to release a new version while they have a session on hold?

I built a timer into each Users Front End copy running on their profile folder (see chart on attachment above). After a few minutes of no activity, it warns them with a model messagebox - to log out - it re-appears every 4 minutes to be "pesky".
But, if they have not logged out for an hour after that message started, then the message comes on every 30 seconds. Now, that is "Pesky".
It has worked like a charm.

In the X:\Admin\Profiles\<username>
There is a copy of each of the several Access front end databases the user can use.
If the user is currently logged in to one - there is the matching Locking DB.
The matching Locking DB goes away once the user logs off.
This provides a way to peek and see who is logged on.
This way, if I have a major version release and need "everybody out of the pool", I can locate the few who are ignoring my message ... with a pesky phone call.
This level of release might be something on the order of combining three tables and forms into a new single form/table.
 
Your query runs against the Back-End every two minutes per each user's front-end.
Where my timer only affects the users front-end, it appears that your question has to do with running a timer (somewhere) against the common back-end.
If each user's Front-End is running a timer (randomly every two minutes times 20 users) it is not the desired result.
Probably not the time to make a case to move to SQL Server and build a timed task? LOL
Had a case to consider adding a Maintenance Front-End and put it on the same folder as the Distribution folder. The idea is for you the developer to log into it - it would link to the back end and perform various task based on a timer.
It would just run all by itself, unattended with a few activities such as the timer.

Am I on the right track?

A second method might be to create a very small table in the Back-End that has a field "Last time Query Ran"
Each front-end version checks that time-stamp every 4 minutes (build a random generator in case all machines have a sync time, so they all don't check at exactly the same time).
If the time stamp is not 20 minutes old - then that machine does not run the query. The first machine that validates the 20 minute old will:
1. Update the table time stamp
2. run the query

This kind of Polling method will allow only 1 machine at a time to run the query.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom