 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / June 2005Error attempt to change the same data at the same time
In my VB6 application (use ADO 2.8), I get the error "-2147217887 : The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time" in rs.Update when trying to update a value of an Access query. I do not know why I got this error, because it does not seem possible for the same record to be updated by more than 1 user. What are the circumstances to get this error, and how can I avoid or get around it ? Thank you. Here are the VB codes: Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset sSql = "select * from MyQuery" rs.Open sSql, adoCon, adOpenDynamic, adLockOptimistic Do While Not (rs.EOF) rs("Col1") = "SomeValue" rs.Update --> ERROR here rs.MoveNext Loop > In my VB6 application (use ADO 2.8), I get the error "-2147217887 : > The Microsoft Jet database engine stopped the process because you and [quoted text clipped - 3 lines] > the same record to be updated by more than 1 user. What are the > circumstances to get this error, and how can I avoid or get around it ? If you find the solution through some other avenue than newsgroups, please post your findings here. I'd have similar problems in the past and I'd like to see how to solve them. "fniles" <fniles@pfmail.com>'s wild thoughts were released on Thu, 23 Jun 2005 14:26:29 -0500 bearing the following fruit: >In my VB6 application (use ADO 2.8), I get the error "-2147217887 : >The Microsoft Jet database engine stopped the process because you and >another user are attempting to change the same data at the same time" in >rs.Update when trying to update a value of an Access query. >I do not know why I got this error, >because it does not seem possible for >the same record to be updated by more than 1 user. Why do you say that? J >What are the >circumstances to get this error, and how can I avoid or get around it ? [quoted text clipped - 12 lines] > rs.MoveNext > Loop Jan Hyde (VB MVP)  SignatureThe city's water supply was so close to the coal mine that all they had was coaled water. When this was presented to the city council, they said, "Never mined" (Keith Martin). [Abolish the TV Licence - http://www.tvlicensing.biz/] fniles wrote on Thu, 23 Jun 2005 14:26:29 -0500: > In my VB6 application (use ADO 2.8), I get the error "-2147217887 : > The Microsoft Jet database engine stopped the process because you and [quoted text clipped - 3 lines] > the same record to be updated by more than 1 user. What are the > circumstances to get this error, and how can I avoid or get around it ? One issue with Jet that you might be hitting is that it doesn't use row level locking, it's page level. Rows are held in 2kB data pages, so it's possible that you're seeing the result of a deadlock resolution when 2 rows near each other are being updated/added at the same time. When I had this issue, I solved it by dumping Access and migrating to SQL Server - my application had grown to the point where Jet just wasn't up to the job any more and I had SQL Server running on other applications, so it made sense for me to move. Dan ¤ fniles wrote on Thu, 23 Jun 2005 14:26:29 -0500: ¤ ¤ > In my VB6 application (use ADO 2.8), I get the error "-2147217887 : ¤ > The Microsoft Jet database engine stopped the process because you and ¤ > another user are attempting to change the same data at the same time" in ¤ > rs.Update when trying to update a value of an Access query. ¤ > I do not know why I got this error, because it does not seem possible for ¤ > the same record to be updated by more than 1 user. What are the ¤ > circumstances to get this error, and how can I avoid or get around it ? ¤ ¤ One issue with Jet that you might be hitting is that it doesn't use row ¤ level locking, it's page level. Rows are held in 2kB data pages, so it's ¤ possible that you're seeing the result of a deadlock resolution when 2 rows ¤ near each other are being updated/added at the same time. ¤ ¤ When I had this issue, I solved it by dumping Access and migrating to SQL ¤ Server - my application had grown to the point where Jet just wasn't up to ¤ the job any more and I had SQL Server running on other applications, so it ¤ made sense for me to move. This is essentially correct. Minor correction: In Jet 4.0 page-level locking was increased to 4k to support unicode storage. Record level locking can be enabled in Jet 4.0 w/ADO using provider specific settings in the connection string. The following articles have more detailed info: Page-Level Locking vs. Record-Level Locking http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deo vrpagelevellockingvsrecordlevellocking.asp PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60 http://support.microsoft.com/default.aspx?scid=kb;en-us;306435 Paul ~~~~ Microsoft MVP (Visual Basic) Thank you all for the replies. Unfortunately, at this point we are using Access 97 (I know, I know, not my choice), we are going to move to SQL Server in the future. The article "Page-Level Locking vs. Record-Level Locking" from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deo vrpagelevellockingvsrecordlevellocking.asp only works with Access 2000 or later, right ? When I tried sleep 100 and then rs.Update again, the error "-2147217887 : Could not update; currently locked by user 'Admin' on machine 'MyMachine'." pops up on the screen. I would like when getting the error to wait for a while then try rs.Update again. What is the best way to do it ? Thanks a lot. Here are the VB codes: Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Dim adoCon As ADODB.Connection Dim rs As ADODB.Recordset On Error GoTo Timer2_err Set adoCon = New ADODB.Connection adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtpath.Text Set rs = New ADODB.Recordset sSql = "select * from MyQuery" rs.Open sSql, adoCon, adOpenDynamic, adLockOptimistic Do While Not (rs.EOF) rs("Col1") = "SomeValue" rs.Update --> ERROR "-2147217887 : The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time" rs.MoveNext Loop exit sub Timer2_err: If Err.Number = -2147217887 Then 'The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. Sleep 100 rs.Update -->> ERROR "-2147217887 : Could not update; currently locked by user 'Admin' on machine 'MyMachine'." End If Resume Next > ¤ fniles wrote on Thu, 23 Jun 2005 14:26:29 -0500: > ¤ [quoted text clipped - 40 lines] > ~~~~ > Microsoft MVP (Visual Basic) 'Try this.. If Err.Number = -2147217887 Then rs.CancelUpdate Resume End If Thank you for the suggestion. Your codes only rollback the rs.Update, right ? How about if I want to wait for a few second and try the update again ? Thanks. > 'Try this.. > > If Err.Number = -2147217887 Then > rs.CancelUpdate > Resume > End If My codes return rs.Update but after rs.CancelUpdate. CancelUpdate is important here. If you don't use CancelUpdate between Update command error and second Update command, this trigger another error. Sleep (your method) or loop (my method) is not important, but my code try Update more than one. Note: My Englis is very bad. I hope you understand me. Thank you for your reply. Did you mean you do rs.Update, and if it gets the error, do rs.CancelUpdate, then rs.Update again ? Did you mean the following ? On Error GoTo Timer2_err rs.Update --> ERROR "-2147217887 exit sub Timer2_err: If Err.Number = -2147217887 Then rs.CancelUpdate rs.Update Resume End If > My codes return rs.Update but after rs.CancelUpdate. CancelUpdate is > important here. If you don't use CancelUpdate between Update command error [quoted text clipped - 3 lines] > > Note: My Englis is very bad. I hope you understand me. No, after rs.CancelUpdate, Resume already go to "rs.Update" line, and try again. My code: Timer2_err: If Err.Number = -2147217887 Then rs.CancelUpdate Resume '-->go to rs.Update line and try again End If ------------- Your code: Timer2_err: If Err.Number = -2147217887 Then rs.CancelUpdate rs.Update 'this line may be cause an unhandled error (-2147217887 or another error) Resume '-->go to rs.Update line and try again End If Thank you very much for your help. I will try that. > No, after rs.CancelUpdate, Resume already go to "rs.Update" line, and try > again. [quoted text clipped - 17 lines] > Resume '-->go to rs.Update line and try again > End If > Thank you all for the replies. > Unfortunately, at this point we are using Access 97 (I know, I know, not my > choice), we are going to move to SQL Server in the future. > The article "Page-Level Locking vs. Record-Level Locking" from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deo vrpagelevellockingvsrecordlevellocking.asp > only works with Access 2000 or later, right ? > [quoted text clipped - 73 lines] > > > > Page-Level Locking vs. Record-Level Locking http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deo vrpagelevellockingvsrecordlevellocking.asp > > PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60 > > http://support.microsoft.com/default.aspx?scid=kb;en-us;306435 > > > > Paul > > ~~~~ > > Microsoft MVP (Visual Basic) Required reading: How to keep a Jet 4.0 database in top working condition http://support.microsoft.com/kb/303528/ The article contains a link to other Databases (follow the link to #300216). One of the major points to be gleamed from the articles is the fact that MSAccess is a File-based database. Its behavior is a result from the joint psychosis of the Access version, Server O/S (file 'shares'), network configuration, provider/driver, and the data object library in use. There may be a 'fix' that works in one situation, but not in another. All solutions will be dependent on the activity (scale and frequency), hardware, and software in YOUR problem domain. It is also important to appreciate that NO database insures Concurrency. Data Integrity sure, to the best of its ability, but never concurrency. If in fact concurrency ever becomes a problem you need to address it as a separate issue. The most common solution is to create a 'gatekeeper' using either COM+, Transaction or Messaging, or an ActiveX Server. (Record-locking adds a level of robustness, but is not a complete solution.) Which is something to consider even when you 'upgrade'. You are also using the default 'Admin' and should modify your database to use User-Level Security. Allowing others to run as 'Admin' with complete ownership severally limits your ability to recover and repair any problems. (Access97 is more limited in security that newer versions.) hth -ralph |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.