[RESOLVED] Theoretical Simple Database Question

Being that some of this is quite new to me, I have been struggling with a concept and would appreciate some input from you advanced programmers.

The whole concept of downloading a database to a recordset and doing data manipulation is quite nice, but I was thinking about a multiuser situation and wanted to present the following scenario for your comments:

Bob and Sue both actively update records in a database. It is a simple mailing list Database. They both arrive at 9:00am and have been entering/updating data all morning. Bob updates a record for John Smith and change John’s spelling to Jon. In the meanwhile (keep in mind this is all hypothetical), Sue has to change the same record for the same Mr. Smith record. Her change requires the zip code to be changed from 08088 to 08099. Having said that, since Bob and Sue both came in at the same time, and they both loaded the initial database into their recordsets at the same time, doesn’t that mean when Sue pulls up her version of the Smith record from the recordset for Mr. Smith that it will still show “John” instead of “Jon”? Additionally, when she saves the record with the new zip code, won’t it overwrite Bob’s previously saved record and chance it back to “John” from” Jon”?

I am thinking about this the wrong way, or am I missing something here, or is there a better approach relative to VB and recordsets?


In my old world of programming, we interacted directly with the database for each record. Thus we would find the record, lock it, update it, and unlock it, and move on.

Thanks again for your input.

Ussally A record is locked while editing .Edit and .Update set and releases this lock so changing the record simutaniously isn't possible (connected ituation, ADO, DOA)

Having said that you can also change data disconnected (ADO.Net)
To handle simutaniously edited records you need to write some error handling
to determine what fields were changed and what to do with diffrent changes of the same field, ussually last modification count will do nicely

And I'll recommend using a transaction in these situations.

Hope it helps....

This is called data concurrency and it can be a big issue in a multiuser environment. The easiest way to solve it is to put a lock on the record when someone begins editing it and take the lock off when they're done.

The other option is to place a lastedited field on each record (Date/Time) and when ever a record is asked for inclued that field. Now when you go to save the record that field in the database for that record must match the one you downloaded. If they are the same you save the record, update the lastedit field with the current date/time. If they are different you enform the user that they must refresh thier data and re-make any changes in order to save the record.