Problems with updating records in a multi-user environment

[earnoldmi]

I have made an application in VB 6.0 and use DAO to link to data in an Access 97 database. The application allows the user to quickly access data of certain records and cause updates to the data. When the form activates, the user sees on the form, the current data in the database. When the user moves off that form, whatever changes the user made, are saved back to the database. The trouble comes when more than 1 user opens the same record. At this point user1 and user2 are both looking at the same info. If user1 makes a change and saves it, user2 does not yet see this change. Then if user2 also makes a change, and saves it, then User1's changes are wiped out. What is the proper way to fix this? Through coding, should I ensure that only one user is allowed to edit a record at a time? I have made code which stores in the database, if a user is on a particular record. This way I can ensure that no one else can edit the record, though they can view it. Also, is the an API call of some sort, which would allow me to know which user has a record locked, and be able to shut that user down if necessary?

Hope this makes sense. Any help is greatly appreciated. Thank you.
Eve

 

[dario123]

When you create the connection you need to specify which type of cursor and lock you will use:

In multiuserenvironment you need to specify cursortype KeySet and Lock: Pessimistic to your connection.

this is with ADO though. Create a reference to ADO on your project and then create a connection specifying the name of the server, and cursortype keyset, and locktype pessimistic.

Hòpe it helps.