Multiple User Updating (1 Viewer)

[zooropa66]

I have a database application that i intend to split with the table part on a server and the forms part on each users desktop. It will be used by approx 10 users. While the probability of different users accessing and updating the same form is small, the possibility exists.

Is there a way i can prevent any weird multiple update stuff happening? I'm using Access 2003

 

[Pat Hartman]

It would be extremely unusual for you to have to go beyond Access' standard practice especially since it is not likely that two users will be updating the same record at the same time. Access by default uses what is known as optimistic locking. This means that records are not actually locked except during the physical update process. So userA opens a form to Rec1 and takes a phone call. userB opens a form to Rec1 and updates it. userA gets off the phone and attempts to update the record. He gets a somewhat cryptic message with three options. Make sure your users understand this message. The best choice is to simply discard the change and start again. That way userA will see the changes made by userB before makiing his own changes again.

If your forms show the record selector, you will see it turn to a pencil when you start editing a reord. You will also see it turn to a circle with a slash if someone else starts updating a record you are looking at. These visual clues help understand how updates work.

Access uses a lock file to manage updates. That's how it keeps track of everything.

You should create the confilct so you can show the message to your users. You also have the option of trapping the error but I wouldn't unless it turns out to be more frequent than you anticipate.

 

[zooropa66]

Thanks for your reply Pat. All updates will be via forms. There will be no updating by editing datasheet forms directly. I don't have any bound controls in my application either. Will this prevent any conflicts?

 

[Pat Hartman]

You have no bound controls????? What in the world are you using Access for?

Nothing I said will happen for you. You are totally on your own. You'll need to add code to trap DAO/ADO errors when your updates fail. You may as well not have bothered with Access. You would have less trouble with distribution etc, if you had created web forms or Win forms.

Access is intended to be used with bound forms and reports. There is no other reason to use it. If you simply let it do it's thing, it is a RAD application development environment. You have crippled it and now you're stuck with all the headaches and none of the benefits.

 

[zooropa66]

You're assuming rather a lot about my experience! By Win forms do you mean forms developed in V.B.? My knowledge of Web forms is non existent. With bound controls i never seemed to be able to update values except in really simple cases where a control was just linked to a table. Any complication like multi-table joins and it just wouldn't let me update. Why did Access allow unbound controls if bound controls are so great anyway? At least it's working. I have had a stripped down version in production for over 6 months now with no errors or problems reported!!!

 

[zooropa66]

Admittedly I've got a lot to learn though

 

[zooropa66]

Also, when i say i'm not using Bound Controls what i mean is that i'm not always selecting the RecordSource and RowSource from the dropdown list of Tables, queries and Forms (but sometimes i am). I'm generally setting those via some VBA. Does that still make my controls Bound? So much to learn. It's only thanks to users like Pat giving us a reality check that we can grow!

 

[Pat Hartman]

With bound controls i never seemed to be able to update values except in really simple cases where a control was just linked to a table. Any complication like multi-table joins and it just wouldn't let me update

The key to controlling form updates is the FORM's BeforeUpdate event. No update gets past it so that is where all your null checks and multi-column validation needs to go. Regardless of the action that caused Access to decide to save the current record, the form's BeforeUpdate event is fired and is the LAST event prior to saving the record so cancelling the event, cancels the save --- Cancel = True --- works in any event with a Cancel argument. People exert an inordinate amount of energy flailing around with form and control events trying to intercept all saves when all they needed to do was to camp out in the Form's BeforeUpdate event and let the save come to them.

Two areas of study will help you enormously when working with Access. Events and Functions. Understanding when events fire will help you to understand in which event you need to place your code to achieve the desired result. Studying Functions will keep you from writing unnecessary code to replicate some functionality that is already built in.

Bound forms have a RecordSource. The prefered choice is a query but tables are allowed. Bound controls have a ControlSource that points to a field in the form's RecordSource. If the ControlSource starts with an =, it is a calulated value and is NOT bound.