Valid MS Error message - 3197 - any experience? 2

[jjob]

I get the message shown below in the notes section (3197 although in my log it is appearing as a negative error number, but same text)

When it appears,it is logged at the same place in the same code each time, BUT it is infrequent, and I've checked the back and front end databases for corruption, but find none. One the one occaision I have managed to get the message (my user gets them maybe once a fortnight) I had a number of related forms open, and had been doing lots of differenct things, unfortunately as it took some time to get the error, I can't remember the exact sequence of events (it may even be time related) and now can't reproduce the error.

Has anyone any experience of this error when it is not due to database corruption, and have they any more information than those shown in the 'Comments:' section below, as to how I may reproduce it regularly; ie has anyone any examples of 'similar operations' that may lead to the error, and save me having to plough through the apparently infinite combinations possible with the multitude of forms and controls on these forms?

TIA

John

Notes:

3197 - "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time"

Comment:

This error can occur when you are at one form, open another form based on that same table as the previous form, make a change to the data and not refresh the original form. Or similar operations.

 

[RJFost]

I haven't seen that one in a while, but used to get it when there was bad/bogus data in a Memo-type field. We were always able to clear it by doing a repair/compact on the database.

Per the Microsloth KB, here is/are the relevant article(s): 109953 (Jet 2.0), 279334 (Jet 3.5), 209137 (Jet 4.0). They probably recommend updating your Jet engine version &, if that doesn't fix it, trying to repair the database.

 

[jjob]

I'm aware of all the corruption cicumstances, and have taken every step to eliminate them, the problem is that only 1 user out of seven gets the errors, he is the man who enters most varied and largest quantities of data, the others enter bits and pieces but tend to report and view mainly. He gets the error intemittently, BUT always in the same form amd piece of code, and over the past months I have logged, checked, compacted and repaired to my hearts content, but found no corruption - I even went to the lengths of viewing EVERY memo field and it's contents - no corruption. BUT I did manage to get the Error once when opening almost every form possible, at the same time, and editing those which appeared related Once I got the error ,after closing forms, and OK'ing the message boxes, the app ran OK. As it is a genuine error message, I thus assumed that there must be a circumstance when it will occur and wondered if anyone had experienced it.

I'll repeat my 'all forms' experiment, but this time try and record everything I do, as I'm convinced it is a genuine case that Access is trying to write two instances of the same table, unfortunately the error doesn't say which table!

Also the user is unable to tell me what he has done when the error appears and so far my logging isn't extensive enough to provide what he can't. So it looks like some more fun hours bug hunting!

Thanks anyway.

John

 

[jjob]

I'm still working on this , on and off, and the thread below seems to be identical to my experiences. The only odd thing being that I still get the problem when I've set the other forms to 'snapshot' and 'read only' access to the table that I believe is shared and causing the problem. I also had a 'refresh' present in the subform where the problem occurs, and this hasn't cured the problem.

thread181-349262

 

[KenReay]

HI
3197 - "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time"

why are you regarding this as an error?

Is it not just Access (jet) warning you that two processes are trying to update the same data. I suspect that the realisty is that although the message says two users, it really means two processes, so if you have several forms open all referencing the same data you are effectively producing a 'locking conflict' with yourself. This would explain the intermittent nature of the problem

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jjob]

Hi Ken,

Habit really, and you are right, I've discovered that the message does not get logged into the error log, as you say, it isn't an error, but initially the message was supplied to me by my user, in an error log, along with another error message. It is possible to produce a real error if one does not exit the form, but keeps hitting OK in response to the messages, then the message appears in the error log.

MS and various sites like this were full of examples of how databases would give this message when they were corrupted. My current database is not corrupt, and my problem is identifying the second process that, as you rightly say, generates this accurate message.

I posted here because I'd not seen many posts that said that such a message could be genuine; the majority assumed corruption, and until now, all my previous experiences were associated with corruption. The thread I just linked to is another genuine case.

So far, genuine case posts appear to be a bit vague as to how to identify the cause and what is the cure. The snapshot and refresh were two suggestion that I find still don't work.

I do have two forms looking at the same table, but one is a snapshot, display only, no edits etc, and the user requires the app to work this way. These are bound Access forms, but I do run a db.execute to update records on the sub form (After the form is cleared) - somehow I feel (can't prove it yet) that this may interact with the bound form possibly a timing thing. Sometimes I think, the "update" hasn't completed before the form starts retieving new form entries. The SQL "update" being executed in a save button on the form.


Finally (sorry it's a long post!) I did read about DAO 3.5 in VB not heeding the Lock Edit set to Record Level, and wondered if this applied to Access, but as yet I've not checked the DAO version in use.

regards

John

 

[psemianonymous]

Consider avoiding the snapshot form thing. This gave me an error, long ago, and here's the thread on google:

http://groups.google.com/groups?hl=...napshot&meta=group%3Dcomp.databases.ms-access

Anyway, I'd do a workaround to provide a 'snapshot-like' environment without actually using the snapshot recordset-type.

Like on form open, APPEND all items into a temp table (this table must be unique for all users), and then open the temp table in dynaset. If it's a smallish recordset, which it should be anyway, the APPEND query won't take long.

It will probably take less time to work around the problem than to find a real solution. I know, that's awful, but it's probably the truth.

 

[jjob]

I think, but haven't proved it yet, that the problem is due to my updating the "sub form" table entries using a SQL update, the 'snapshot' forms do not, I think have any effect, except to maybe affect timing of events, such that they seem to introduce some delays to the SQL update. Certainly making them 'snapshots' have made no difference to the errors .

The trouble is with a main/subform form. Basically I have a main form that is unbound for accepting data for a 'main' table, (this is to allow the user to NOT save the changes and make life easier for to cope), then a bound subform which can take multiple records linked via the id of the main form record. To allow the user to NOT save the changes, my main record generates a 'pseudo table id' which is used in the subform records as the link value. IF the users saves, I actually write the main record, retrieve the real id, then run the SQL update on the sub form table to replace the 'pseudo id' with the real one, otherwise i run a batch delete of the sub form records which hold the pseudo id.

Now I read somewhere that SQL batch updates do page locks, so I think what happens is, a page lock is set, and access, in the background is running the batch update, when my form tries to write a new sub form record, but as the table is ordered via autonumber id's I think the new record sometimes falls within the page of the updated records - hence the locks and the errors. I'm now about to replace the SQL update with a recordset update loop, and see if that solves the problem.

Thanks for your advice though, it may well be applicable here and my ideas nothing more than a blind alley - I'll let you know anyway.

 

[jjob]

Hi Foolio,

My changes made no difference, so I've implemented your suggestion, a temporary table for the 'sub form' table. Now this hasn't solved all my problems, BUT it has changed the message, now I appear to be getting 'Not a valid bookmark', but possibly not as often as I could get the earlier message. I've given you the star because I think your suggestion has opened up more possibilities and narrowed the problem down to the subform.

If I solve it I'll let you know.

regards

John

 

[jjob]

Me again.

The thread below is exactly my problem,

thread181-624176

how I've missed it until now is beyond me. Apparently MS say it is a timing problem with JET 4, and dlookups, which striked me as amazing - form and subform is a standard requirement in almost any database app, so if dlookup can't be used I wonder what they are going to do!

Anyway, I've now also compiled to an MDE file and the problem seems much reduced, so I will send that to my customer and see if the other thread author has made any progress.

Thanks again to Foolio12 for setting me onto this tack.


John