Lock A Record In An Access 2007 Database Using VB.NET

Posted on 2010-08-13
Medium Priority
Last Modified: 2013-11-26
Hi Everybody

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.

Many thanks.

Question by:Nolanc
  • 2

Author Comment

ID: 33427606

I omitted a very important piece of information for you viz. that the Quotation Number I generate is the Primary Key for the Quotation Detail record. Here is a snippet of my code:
Form Class Frm............
   Private dsDtl As New Dataset
   Private daDtl As OleDb.OleDBDataAdaptor
   Private dsNum as New Dataset
   Private daNum As OleDBDataAdaptor
Form Load Event  ......................
  sSQL = "Select * From NextQuote"
  Call dbConnect()
   daNum = New OleDb.OleDbDataAdapter(sSql, QtCon)
   daNum = Fill(dsNum, "QtNum")
  ....... Similarly For The Quotation Detail Dataset
   Call DbClose()
 BtnSave Event ..............
  Call GetNextNumber()
   ..... Create The Quotation Detail Record And Insert sNumber As The Quote Number
   .... Add The New Quotation Record
End Sub
Private Sub GetNextNumber()
   Dim cb As New OleDb.OleDbCommandBuilder(daNum)
   sNumber = dsNum.Tables("QtNum").Rows(0).Item("NextQuoteNo")
   dsNum.Tables("QtNum").Rows(0).Item("NextQuoteNo")  =   dsNum.Tables   ("QtNum").Rows(0).Item("NextQuoteNo")  + 1
   daNum.Update(dsNum, "QtNum")
End Sub
I trust that I have now provided sufficient information to warrant the necessary help.
Many thanks.

LVL 16

Accepted Solution

13598 earned 500 total points
ID: 33433356
You could retrieve the quotation number and increment by one from your table NextQuote when the user presses the BtnSave button (or whatever in your logic triggers the save event). That only takes less than a second. The chances that several users will be trying to access it at once are low but you could have a retry routine. Surround the code that retrieves the next quote number and increments by one with a try/catch/finally statement. Trap the concurrency error and retry. Or if you want to lock it, lock it and then check for a lock record error and keep retrying. With the locking though you are 'holding up the traffic' since you are unable to move forward until the lock is released. If the lock is legit it wouldn't be long but if it is due to a system issue that left the lock hanging it could lock other users as well.
I would also do the same in your code that saves the record to your quotation detail table. I would check for the duplicate key error and if so do something. If it fits your flow automatically retrieve the next quote number and try saving it again.
These are uncomplicated basic ideas based on your statement that the chances of it happening are low.
LVL 11

Assisted Solution

ladarling earned 500 total points
ID: 33433448
Since you are working with datasets and -especially- since you are using Access as the back end data store you will be much more likely to get the functionality that you need if you modify the database such that the NextQuoteNo is NOT the primary key of the table.
More robust database systems allow you to specify a field as an Identity, which is an auto-incrementing number that allows you to perform scalar operations on them to insure the uniqueness of said number. Access does not have this functionality, however, and locking someone out of the -only- record that could allow them to generate a new number is not a good design.
Instead, allowing clients to request and increment the SQL MAX() of the NextQuoteNo field will prevent lock-wait conditions, and if your code checks, increments, and immediately updates this value you wont run into duplicates (especailly if the target table IS using the QuoteNo as the primary key, since you can then be guaranteed that it will generate a duplicate error on insert and allow you to modify that number before insert)
That is more work, I know, but its the reality of coding against Access databases.
(Just FYI: MySQL is free, full featured and has none of the DB size limitations, and you can code against it just as you would Access with the MySQL connectors for .NET)

Author Closing Comment

ID: 33435614

I would like to thank both you guys for your comments and suggestions. It sounds very logical to me. I will certainly implement them as soon as I get back into the Work place on Monday. But I am accepting it and thus awarding the points to you. If I encounter any problems I will initiate a new question and get back to you.

In the mean time, my grateful thanks to you.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month16 days, 4 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question