Multi-User Design

[tyrobrio]

Hello I was looking for some advice on a Multi-User Data Entry DB that I'm developing at the moment. The idea came up because of an issue I ran into with my previous project.

My previous project involved a simple database with 15 or so employees doing Data Entry with their own copy of FE. The FE was just one form with a linked table to the BE. However every now and then a record would be locked and everybody would be forced to exit out of the FE to free up the table to allow additions. I read that maybe it had to do with a memo field??? As a fix I broke the link and just programmed with ADO for Data Entry.



My Current Project is a DB that allows Users to create and test procedures. A manager will create a procedure and list 20 steps on how to finish the task. The employee will follow each step writing comments and approving the step. So taking my paranoia from the last project I want to program everything with ADO to avoid records locking. The weakness with this design is that somebody grabs a record (Procedure) and edits it while someone else is accessing at the same time. The last edit submitted would be reflected in the DB while the first person might have made a critical change.

Any thoughts?

[pbaldy]

What I do with unbound forms is have a field in the table to track the "version" or whatever you want to call it. Let's say it's at 1 right now, and you and I both retrieve the record to edit it. The update will say "update table...where field = 1" (using the value when the record was retrieved). It will also increment the number. If I save first, your update won't work because the number is 2 at the point you try to save. Testing whether the update worked lets you inform the user their update didn't work.

[tyrobrio]

Thanks for the reply Paul. That method is quite useful and will be using it to complete this project. Thanks

[pbaldy]

No problem.