Solved

How do I programmatically determine if any recordsets are still open?

Posted on 2008-10-26
17
691 Views
Last Modified: 2013-11-28
I continue to get errors like these: The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.
The record has been changed by another user since you started editing it.  If you save the record, you will overwrite the changes the other user made. . .

So how do I programmatically determine if any recordsets are still open before one function ends and another begins?  Is there something else I should be looking for?  Help!
0
Comment
Question by:bobbat
  • 6
  • 3
  • 3
  • +3
17 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 22809294
That message does has nothing to do with open recordsets.  You get it because another user changed the record after you edited it.  You will still get it even if the other user has closed the recordset.  That's how optimistic locking works...Access warns you that the record has changed and gives you the option to overlay the changes (lost update) or not.  

You can change the locking to "Lock Edited Record" to engage pessimistic locking.  In that case, you will be prevented from editing the record if another user has it open.

The underlying problem is that you have two users trying to update the same record at the same time.  Out-of-the-box, Access gives you two ways to handle it, so the choice is yours.  More sophisticated concurrency management is possible, but it falls in a realm that challenges even the experts.


0
 

Author Comment

by:bobbat
ID: 22809325
But I'm the developer and the only person using the database--it's only on my computer.  There is only one instance of Access running.  I am the only person logged in.  How can I troubleshoot these errors?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22809381
try this in the Immediate Pane:

? currentdb.openrecordsets.count

The number returned will tell you the number of recordsets still open
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 22809479
I've seen this happen when you are opening forms with combo box controls set with their recordsource set the same as the form. I've also seen this when you nest recordsets inside of one another.

Perhaps the message isn't the only thing that needs to be debugged. Can you elaborate some more on what is happening when you get the error? Does it always happen?

Careful use of the dbOpenDynaset vs the dbOpenSnapshot should be considered.
J
0
 

Author Comment

by:bobbat
ID: 22809484
GRayl:  Thank you, I think this command would be useful but I get a compile error: Method or data not found. Can you tell how to get it to work?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22809501
if you are doing it in the code window you must use:

debug.print currentdb.openrecordsets.count

down in the Immediate Pane, the compiler accepts '?' in place of 'debug.print'
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 22809685
Just another thought,

This can also happen if you have 2 seperate forms that use the same recordset or operate on the same record.  For example if you have a main form and you open a popup form for the same record to add notes for example you may get this error.  If this is the situation then you may be able to get around it by placing a Me.Refresh command before you open the popup.

Leigh
0
 

Author Comment

by:bobbat
ID: 22809733
For jefftwilley: I use Access 2003 with DAO.  I do not have combo boxes with their recordsource set the same as the form.  I do have more that one recordset open at a time.  For example, after I populate the parent table's record, I leave it open while I populate the child table's record.  I do that so I can set the child table's Foreign Key to the same value as the Parent Table's Primary Key.  The Write Conflict error always happens.  I usually use dbOpenDynaset since I usually have to change values in the recordset.  Could you elaborate on "Careful use of the dbOpenDynaset vs the dbOpenSnapshot should be considered."
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 22809770
Are you using 2007? We've seen this error occur a few times in the past week or so in Access 2007 under conditions where we had not seen it before. I think it has something to do with user edits through forms conflicting or not releasing its lock (posting changes) to the underlying table before my recordset code went to change records using a recordset clone.  I can repro the error by manually forcing the lock conflict, but that shouldn't occur since I post the user's changes before the recordset makes its change attempt. i haven't had users reliabily repro the problem -- though they've tried.

I know the problem is real because it has triggered an error, which invoked our error handler and logged the crash pinpointing the .Edit line when trying to edit a locked record using a DAO recordset.

Odd....

Luke
0
 

Author Comment

by:bobbat
ID: 22809872
For Luke Chung: I'm using Access 2003--is there any advantage to using 2007?  P.S.  I have a few of your products and have found them useful.

For GRayL:  I found another open recordset in the Open event of the form and think I may have others.  I cannot get the command  you sent to work.  There is no Access keyword such as OpenRecordsets [note the letter 'S' at the end of the term OpenRecordSet].  I did a detect and repair. . . any ideas?
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 22809922
Glve you've found our products helpful.  Always pleasant to hear that and always open to suggestions for improvement.

There are many advantages for using Access 2007 over 2003 -- basically you can offer a solution that's much more modern looking, user friendly, and powerful that would be nearly impossible to do in 2003 -- but that has nothing to do with the problem you're dealing with here.

Sounds like you may be causing a conflict within yourself if you're making changes between master and detail records while the form has them open. Is the master record locked when you start modifying the details?  Are you creating a new recordset independent of the subform or using the subform's recordsetclone?  The latter would be better.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22810250
Let me reiterate--open recordsets is not the issue.  You are experiencing a concurrency conflict between two connections trying to update the same record at the same time. Usually that happens from two different users, but it can happen from two connections by the same user, as well.  Closing recordsets does not "undo" changes made over the other connection and therefore is not likely to fix the problem.

I suggest you open all recordsets are over the same connection.  Also, design your application so that you aren't editing two records from the same table at the same time (in different recordsets, recordsources, gridviews, or queries). Do use R/O recordset types when you can.  
 


 
0
 

Author Comment

by:bobbat
ID: 22812460
For dqmq: I will review my work over the last week or so to ensure that Im not editing two records from the same table at the same time (via different recordsets, recordsources, gridviews, or queries) and use read-only recordset types when possible.  I only know DAO (apparently not very well).  Can you convert your statement "I suggest you open all recordsets are over the same connection." to DAO and plain English for a beginner like me?  On the Access options page I have selected "Edited Record" as the "Default Locking Record."  I have checked the box labled "Open databases using record-level locking."

For Luke: I have referential integrity turned on and have enabled cascading updates.  I do have more that one recordset open at a time.  For example, after I populate the parent table's record, I leave it open while I populate the child table's record.  I do that so I can set the child table's Foreign Key to the same value as the Parent Table's Primary Key. Are you saying that I should close the master table recordset, store its Primary Key variable as a module variable and use it to set the foreign key in the details table?
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 22812717
I think you can keep the master record open, but any changes you make to it should be posted before you modify any records that are tied to it through referential integrity.

Not clear if you are making these changes purely in code or if it's happening through a form with detailed subform.  If it's the latter you should use a recordset based on a recordsetclone of the subform to not have conflicts between the two.  And of course save any records that are currently being edited before changing them with your VBA recordset.  Hope this helps.
0
 

Author Comment

by:bobbat
ID: 22812874
For Luke: I am working on a credit memo form/subform that is based on the invoice tables.  The problems I am having is with the inventory tables as I bring items in and out of inventory.  The inventory tables are not related to the invoice tables.  The form provides data like dates, serial numbers, etc. but all the code that makes changes to the inventory tables is done in modules--not code behind the forms which only works with the invoice tables.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22813673
>Open databases using record-level locking

Good. That will minimize the problem

>I suggest you open all recordsets are over the same connection

Instead of this:

Dim Cnn As ADODB.Connection
Set Cnn = New ADODB.Connection
Cnn.Open

Use this:
Dim Cnn As ADODB.Connection
Set Cnn = CurrentProject.Connection
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 22815921
Sorry, that should have been:

? currentdb.recordsets.count

Recordsets is a collection of all open recordset in the database.

Go to VBA Editor and enter Recordsets in the Help window.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now