Multiuser database updates

[Mark Kledzik]

Are there any set rules for programmers to code for updating the same record at the same time in a multiuser environment?
For example:
Two users of the system display the same details on each of their screens.
User A makes a change to the record and presses update. User B does not make a change to the record but still presses update. Should the record on the database have User B's details?
Are there any hard and fast rules for record locking in multiuser environments?
Both users in the example above should be allowed to view the record. But how should the system cope with the updates?

[bashfirst]

As far as Access goes, User B in your example would get an error stating that the record had changed.. not that you couldn't code around that. However, in general, you don't want to allow a user to unknowingly make a change.

As far as multi-user environments... there are a couple of articles in the Knowledge Base about multi-user concerns. Basically, you have to anticipate all the possible errors that can occur, and then handle each error in a graceful manner. The interesting question is usually how you will recover when a user attempts to update a record that has been updated already. Unless your application polls the database periodically to see if the record is changed (which would be a performance hog), you won't know you have a problem until you attempt to update. The nice thing to do is to allow your user to see the newly updated record but also not lose the changes they've already made. Then allow them to discard their own changes, overwrite the old record or merge the two. I don't think I've seen a good generic way to present this to a user, so usually you just block the update, show them the record and make them start over if they still want to make their change.

Also, be aware that with page-locking (ala Access and others) records may be locked which are not actually being edited. You will have to deal with that too.

Good luck.

Bash

[JHausmann]

Access can work as a multi-user database in a pinch but its not the best tool for the job. SQL Server, Sybase, Oracle, Informix, etc. exist because they are more robust when it comes to dealing with the issues you cite.

Locking strategies, concurrent access and security are weaker in Access, for multiple users than in a "SQL Server". At some point, a migration from Access becomes imperative, a good reason for using ODBC (or DAO, ADO, RDO) and "generic" SQL. You can pretty much swap out the database at any time...