Multiple Users (1 Viewer)

[Pat Hartman]

Here is a link to the MS Access documentation regarding Refresh Interval AND ODBC refresh interval. They DO refresh the data shown on the form which is of course not the same as requerying it so you wouldn't see new rows and deleted rows would show up as #deleted#

Help Access run faster - Microsoft Support

support.microsoft.com

 

[nonakag]

Doc, thanks for confirming that. Learning a lot about multiuser environment efficiencies. Yes, I'm having overhead issues updating the dashboard. The application is used over the internet via VPN from home users and in the office on a network. I'm trying to find the best timer setting that satisfies both environments.

 

[nonakag]

Pat, thanks! Good article and will implement some of the ideas.

 

[The_Doc_Man]

The application is used over the internet via VPN from home users and in the office on a network.

As long as nobody is using a Wi-Fi connection, this might be slow but should be stable. Be warned that the moment Wi-Fi slips into the picture, you have potential for radical instability that would lead to file corruption. Ask your home users to use a hard connection to their home routers if they are not already doing that.

 

[nonakag]

Every FE automatically refreshes a form when you take any action on that form that changes records - like navigating to another record as one example. But you are right - there is no passive auto-requery on a form that isn't doing anything at the time. This is why Access supports Timer settings on a form. Even so, setting the timer to a small number (like, <15 seconds) is generally not a good idea because of overhead issues.

Doc, I've been doing more experimenting. I turned off the Access Auto Refresh by setting it to zero. For some reason the other FEs with this set don't get updated. I also tried the Form Timer Event and Timer Interval Property with both a me.refresh and me.requery. and the other FEs still don't update. All they do is blink and never update. My main Switchboard Form is also a Dashboard for status and would like all the FE Dashboard's to be updated when there is change to the BE tables. Is this a Form Focus issue where if the form does not have the focus the updates won't happen? Thank you, for the help.

 

[nonakag]

Doc, I leave my Dashboard (Switchboard) form open on my desktop. The form timer is set to requery and refresh every 4-minutes. However, it seems if I leave the access app, and work something else, the timer events seem to buffer if the form looses focus when I use another MS application on my desktop. When I reenter the access applications it then seems to try to catchup and blinks rapidly multiple times and then takes 10-15 minutes to display any data in the controls. Is there a way to have the Dashboard keep up with the timer event if I'm working in another application? Thank you.

 

[The_Doc_Man]

The holdup may be (not guaranteeing this) that when another app gains Windows focus (i.e. Access is no longer the current app), something in your code tries to update the screen - but can't because it doesn't control the screen at that point. So some of that updating information might be buffered and backlogged. Then when things can happen to the screen from Access again, you play a frenzied game of "catch up" until you actually DO catch up. I would bet in that context that the system would be less responsive for a brief time.

There are a couple of events that might help, but it would require you to do a little bit of detective work to figure out the culprit. IF (and it is a very big IF) I am right that the problem is that the screen has become non-current, look at the Form_Exit event and the Form_Enter event. They fire when your form loses the screen and when it regains the screen. If you can figure out what is being updated via Repaint, Refresh, or Requery, or via any kind of automatic navigation, you could use Exit/Enter to set/clear a flag that would block that display action. Then that crazy "catch-up" operation might not occur.

I have had many timer events running under circumstances where other screens became active. If your code looks OK and responds OK when Access is "on top" then the odds are that the timer event itself can probably keep up. It is something that the timer is trying to do that interacts with the screen that it doesn't own at the moment (one of the perils of a multi-tasking system). That may be your problem.

Something you said needs clarification. There may be several facets to the problem of having the dashboard "keep up" with the timer event. If by that phrase you mean that the dashboard display would continuously update anyway, that is something that might be problematic. But if you used the Exit/Enter event, you could have the Enter event trigger ONE update cycle immediately to bring your display up to date.

And I could be wrong on this. Every time I turn around, I find that yet another Windows improvement has been made that obviates something I learned a long time ago. But I have, on Win10, seen situations where a progress bar just stops because other things happen behind the scenes that have nothing to do with Access. Then after an unpredictable time, everything unfreezes and jumps to catch up. So I think it is systemic with Windows itself.

 

[nonakag]

The holdup may be (not guaranteeing this) that when another app gains Windows focus (i.e. Access is no longer the current app), something in your code tries to update the screen - but can't because it doesn't control the screen at that point. So some of that updating information might be buffered and backlogged. Then when things can happen to the screen from Access again, you play a frenzied game of "catch up" until you actually DO catch up. I would bet in that context that the system would be less responsive for a brief time.

There are a couple of events that might help, but it would require you to do a little bit of detective work to figure out the culprit. IF (and it is a very big IF) I am right that the problem is that the screen has become non-current, look at the Form_Exit event and the Form_Enter event. They fire when your form loses the screen and when it regains the screen. If you can figure out what is being updated via Repaint, Refresh, or Requery, or via any kind of automatic navigation, you could use Exit/Enter to set/clear a flag that would block that display action. Then that crazy "catch-up" operation might not occur.

I have had many timer events running under circumstances where other screens became active. If your code looks OK and responds OK when Access is "on top" then the odds are that the timer event itself can probably keep up. It is something that the timer is trying to do that interacts with the screen that it doesn't own at the moment (one of the perils of a multi-tasking system). That may be your problem.

Something you said needs clarification. There may be several facets to the problem of having the dashboard "keep up" with the timer event. If by that phrase you mean that the dashboard display would continuously update anyway, that is something that might be problematic. But if you used the Exit/Enter event, you could have the Enter event trigger ONE update cycle immediately to bring your display up to date.

And I could be wrong on this. Every time I turn around, I find that yet another Windows improvement has been made that obviates something I learned a long time ago. But I have, on Win10, seen situations where a progress bar just stops because other things happen behind the scenes that have nothing to do with Access. Then after an unpredictable time, everything unfreezes and jumps to catch up. So I think it is systemic with Windows itself.

Doc, Thanks for the advice. Yes, that is what I meant by the Dashboard using the Form Timer Event to update periodically. So, far, as long as I Access has the screen focus the Dashboard updates every ten-minutes with no issues. Thank you.