[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
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:…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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