Lock A Record In An Access 2007 Database Using VB.NET
Posted on 2010-08-13
I am developing a Quotation Management System using VB.NET (Visual Studio 2008) and an Access 2007 Database. One Of The major auditing requirements is that each quotation detail record has to be uniquely identified by a Quotation Number which has to be generated by the application at the time the quotation detail record is saved.
Thiis Number (known as NextQuoteNo) is stored in a Table NextQuote and is the Only Entity in the table. When I access the table, I retrieve NextQuoteNo, allocate it to the New quotation detail record and I increment the NextQuoteNo by One and I save (Update) the Dataset. And this all works like a dream.
My problem however is: What do I do in a Multi-User environment to ensure the Uniqueness of this Quotation Number. This was very easily achieved in VB6. It is not so easy using ADO.NET since the Dataset in question is Disconnected from the Database.
The probability that a Lock will be needed frequently is very Low but it may very well be needed. I have done a search with Google regarding Record Locking in ADO.NET but the information I gather is not very convincing.
For this reason, I hope that someone can shed some lite on the problem for me. I have to ensure the Uniqueness of the number in question.