Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 24th, 2007, 10:46 AM
Registered User
 
Join Date: Mar 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multiple Front End Requery?

Hello everyone! I am fairly new to the world of Access and VBA but, learning more everyday. I currently have a database with three users (Front Ends). Each front end has a screen that keeps a list of patients awaiting transfer.

At the moment each seperate front end does an OnTimer requery of the back end to refresh it's screen and keep the data current. There is talk of increasing the number of users to possibly 10 or 12. I am wondering how well it's going to work having several machines querying the back end every few seconds? I'm not a network guy so not sure what kind of traffic that causes.

What I'd like to know is if it's possible to have a module in the back end (or anywhere for that matter) that forces all the front ends to requery on data change and if so how?

 
Old March 24th, 2007, 11:53 AM
Authorized User
 
Join Date: Mar 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi medic

That's the way client-server systems work - data is only pulled to the client when it requests it, there is no way to originate data transfers from the server. By moving your Access database to a separate machine and letting multiple users access it from their own PCs, you have turned your system into a client-server model (that's not a criticism - there's no other way of doing what you want to do). So the basic answer to your question is "no".

How critical is the requirement to keep the on-screen data up to date? You could easily include a command button that would initiate a refresh whenever the user wants an update - this would mean that data only flows over the network when it is needed.

Bigger client-server systems (i.e. not Access!) are often written with "optimistic locking", that resolves multi-user issues (where one user is trying to modify a record on thier PC that has already been updated by another user). This is usually done by having a date/time field on each table, that is updated every time a record is modified. This value is passed to the front end along with the rest of the record, and returned to the server with the update, so any update which has an old timestamp should be rejected (because someone else has modified this record in the interim). Hope that all makes sense - I've covered quite a lot of ground in a short paragraph!

If it is very important that the on-screen data is current (which makes sense for a medical database!), then you will have to adopt a strategy similar to the one you describe, regardless of the network traffic problems. I've not tried to do this, so I can't say whether 12 users would be unworkable - my recommendation would be to suck it and see. You could look at generating some kind of unique value for each PC, and basing the re-query time on that, so they don't all re-query at the same time (are your PC clocks kept synchronised to a standard time by a central server? Many organisations do this nowadays).

Hope that helps...

Richard

 
Old March 24th, 2007, 01:09 PM
Registered User
 
Join Date: Mar 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Richard, this helps a lot (keeps me from chasing something that doesn't exist :) ). It will be extremely rare that any two clients should ever need to access the same record at the same time. Mainly the data is taken by a user at one department then passed to another department for disposition.

Thanks again for the info!

 
Old March 26th, 2007, 07:27 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Your on timer event should put the form into a non-usable state that forces the user to click a button to get the form back into a usable state, and that button click can do the refresh.

You might want to consider upsizing to SQL.

mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Front end Vs Back end ricmar Access VBA 3 May 27th, 2008 02:36 PM
Multiple SQL Databases for Same Access Front End darrenb Access 0 May 27th, 2008 12:07 AM
Ms Access front End with Oracle 10g Back End rahul123 Oracle 1 July 9th, 2007 01:03 AM
Oracle back-end MS-Access 2003 client front-end Corey Access 2 February 16th, 2007 08:31 AM
Oracle Back End - MS Access Front End - Multi User ckaliveas Oracle 1 February 1st, 2007 06:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.