Database Muiti user Saving

Hi All

Our application is a multi user database app, using Access or SQL server database.

Currently I handle uses saving the same product data using a time stamp.

In that when I load the data to the screen I read a last Saved date.

When the user saves the product, it first looks at the product lastsaved date on screen and the lastsaved date in the database and if they are the same then it allows the save, If not, then an message is displayed to the user that someone else has saved this product while they were editing and will have to refresh their data and make the changes again.

This has worked very well for the past 4 years.

However I now reluctantly have to change this, so that as soon as a user starts to edit a products data, it will lock that product in some way

Any user trying to edit the locked product will be notified that the product is being edited and is locked.

My first thought was to create a new table called product_locking,  and enter the product ID as a row as soon as a user starts editing.

When the product is saved or cancelled it would delete the row.

There may be delay issues here as ADO has a lazy save delay.

I could use a text file with the file name as the product ID, saved on a shared folder on the server, and that would be used instead of the table.

Has any one used a better method that has been successful that they would like to share with me?

Many Thanks

Mark

Many Thanks Mgampi for your reply

I will give this some thought; we have never used SP, all SQL selects, Updates etc are all in code, and because we had to support access as well as SQL server are limited.

I guess our data stamp method in use at the moment is similar to what you use, in that it stops a save if some one has previously saved that product.

But the change I have to implement is to give a user warning that the current record he is about to edit is locked, so that they don't make changes that will never get saved, which is what happens now.

Thanks again
Mark