Error 3197 but not corrupt 1

[Glasgow]

I am experiencing an intermittent error 3197 suggesting that two users are accessing the same record but I am the only user. I am aware that this can often be caused by a corrupt memo field but can see no evidence of such corruption within the database (how would it manifest itself?)

Are there any other circumstances when this error might occur and is it possible to generate this error by inadvertently trying to, for example, update a recordset while one of its records is open in another recordset within the same application?

Thanks in advance.

 

[rkdcomp]

When I do development work using a front-end/back-end, I always use a back-end with minimum records as it's easier to see what your results are and also I don't have to work with the customers REAL data if they don't want it out there.

So when working with a small back-end and all goes well, then when the customer links to their back-end and has problems, you can suspect there is a problem with their data or too much of it.

Richard

 

[jjob]

Errors are unpredictable, but inevitable, if that makes sense. Initially I couldn't get the errors at all on my system, because I kept calling the problem form from the "main menu", once I realised that the user was calling it from the tab in the company forms, it didn't take me too long to get the errors, but I had then asked for, and got, the users data, as I was convinced there was corruption - there wasn't. To get the error I have to SAVE data, not just enter data then abort and exit the form. Saving does 2 things, obviously invoks table writes, BUT because the new data needs to appear as a summary in the complex parent form (customer asked for an immediate refresh), a refresh of the complex parent form is executed by the child. I have a refresh sub as a method of the calling form class.

The bad news is that I now get the errors when calling the data entry form on it's own, but, as I said earlier maybe it's since having the user data, also possibly only if I've first run it via the complex form! I need to check that by starting up and adding via the main menu only.

I've been almost a week just trying to determine the circumstances. I have found that it is always the sub form that is generating the errors, even though this is bound to a relatively small table - about 3000 small records (it's a link table), none are corrupt by the way.

I will try reducing the backend database size, but I'm interested in trying a few other things first


a) introduce a time delay in the SAVE procedure and see if that allows Access to sort itself out.

b) replace any dlookups in the complex form with my own lookup functions, in fact try and replace any Access function wth my own.

What surprises me is that as this is such a standard and straightforward action, Microsoft appear to have nothing on their sites or knowledge bases - the two errors, 3197 and the Bookmark seem to be typical of the problem but I've found nothing.

My customer is going to love it when I tell them that their upgrading to XP and Office XP (Access 2002 I believe) is the probable cause of the problems!

 

[jjob]

Just checked the BE size, 20 Meg. (front end is now 8 Meg - growing because of temp table resident in it.)

Another thought, re database design - I inherited this database, it was originally all Access Wizard stuff produced by a technical user. The core of the appliction is a form with about 8 tabs, many subforms etc and there is loads of data handling and links. It looks good, but I think it produces a lot of processing, always did, and it's penchant for corruption was why I started to strip out anything clever that I could reproduce in VBA or SQL.

I came on board when the techical author found it hard to produce the report filter forms (actually being a developer I went straight for VBA, I could probably have used SQL and QBE to produce queries, but felt I had more control and time was short, with hindsight I think this was a good move!)

I've replaced all macros, split it into front and backends and to speed things up have replaced lots of access functions with my own, however, the database remains basically a 'bound forms' one. It also did use lots of the Access table fields were you would look at the field and get another table appearing. It did corrupt a lot during development, but as I removed the Access stuff and replaced it with VBA it became stabler. I think there is much to be desired, but it has worked for about 18 moths with up to 5 simultaneous users (maybe more). No corruption of the database on site so far, what corruptions occured where normally at my end during development.

I've used Access back ends on web sites and it seems robust enough, the problem seems to me to be in the user interface part. Certainly the back end here has so far stood up well

 

[Glasgow]

>My customer is going to love it when I tell them that their upgrading to XP and Office XP (Access 2002 I believe) is the probable cause of the problems!

Boy do I know where you are coming from there! Our credibility was in the balance on this issue having been called in to do the Access 97 to 2000 upgrade and being caught out in the process.

Removing the DLOOKUPs sounds like a good idea. As well as their very helpful suggestion (cough) of reverting to Access 97 (after it was far too late), Microsoft also suggested migration to SQL Server. Removal of DLOOKUPs would help if you ever decided to go that route. We couldn't do it (much as we would have liked to) because it would have been too much additional effort.

 

[jjob]

7:45am UK time, and I'm about to start on my day client's work, but thought the following may be of use.

My database is less than a quarter of the size of Glasgow's, about 80 Tables, 75 Form forms, 60 queries and about same number of reports. There is one very complex form with 8 tabs and many subforms, which I think probably opens most if not all tables in the db.

My problems seem to be with the one form, a sub form on a data entry form, I enter a Sales Rep, with his customer and method of contact, ie whether he is visiting, phoning etc on the main form, on a sub form I enter his objectives, these are of the form, 1.1 - 'Flog him stuff', or 1.2 - 'ask for payment' or 3.7 - 'just say hello' etc. The sub form is a continuous form and can display 4 objectives before needing to scroll (may or may not be significant)

Each form of the sub form has 4 visible fields and 2 hidden, the hidden are the Pk and the link to the Rep table. Of the 4 visible fields, 3 are lookups, one a memo. of the lookups one is a description populated by my own function getObjectiveDesc(field, form) the field is used to lookup the desc, the form is for logging errors (this function is called from various forms)

I appear to have a hierarchy of errors, First is the 3197, next is the Bookmark, finally there is an error generated in my function getObjectiveDesc, which claims that the field is not an object. I say hierarchy, because I believe that 3197 is when access gives up and stops engine, the bookmark error, is I think after that and my form can read the table, but Access possibly hasn't finished something, and the same with the getObjectiveDesc error - access hasn't, I think, got the 'Objective' field ready in the bound sub form. So it may be I'm close to having an error free process, maybe a delay in my code may help - but I'll try that tonight, back to my musings.

I have moved from intermittent 3197's, via a similarly intermittent Bookmark error to the almost predictable 'getObjectiveDesc' error. I've managed that by recoding the 'save' proc in the rep form. Now it works as follows, first sub form now uses a temp table in front end, NOT the live objectives table in the back end as it did originally.

a) Saves rep using recordset
b) Uses CurrentDb execute SQL to insert objective records from temp table into live
c) Uses CurrentDb executeSQL to delete objective records in temp table.

With those changes I've moved to being able to regularly obtain the getObjectiveDesc error by adding more than 4 objectives on the sub form. 4 or less and thing saves fine (so far, but maybe if I tried long enough I may get the 3197 errors again) If I add 5 or 6 (my biggest attempt so far) I get the error message, BUT after clicking OK, the form appears blank, ready for input, AND the 6 entries are in the database.

Now it may be coincidence that 4 objective entries saves without error, more than 4 don't - given that a maximum of 4 can be displayed on the sub form before scrolling.

I will continue testing tonight, but I find this rather depressing, as unless I've now corrupted something, Access should surely be able to cope with this level of complexity?

Barring a miracle, my Customer will be rather disappointed when I have to report on my progress tomorrow! :-(

 

[Glasgow]

I wonder whether you should be struggling with this so much? Do you have an MSDN agreement with Microsoft? It might be worth raising a support incident with them so at least, when you have to report bad news to the customer, you may be able to lay the blame with Mr Gates. If necessary, I can dig up our own incident number so they can compare.

We struggled for ages on this (and on a fixed price contract) before effectively coming clean with the customer. We offered some workarounds but, to be honest, we were far from happy with that solution as we thought it a matter of time before something similar cropped up elsewhere in the application. Convincing the customer to archive off some older data was not so difficult especially when offered the additional carrot of likely performance improvements in addition to disappearance of the symptoms.

 

[jjob]

I'm on a fix priced contract too, and I do have MSDN Universal subscription, runs out in April mind you. I just can't believe that Access can be hamstrung by such trivial task and am convinced it must be something I'm doing. I'm particularly concerned as my app is only a quarter the size of yours.

I 'm going to mail the customer tonight with the bad news.

Thanks for all your time and advice, it has been most helpful.


John

 

[Glasgow]

Well if you have an incident or two left on MSDN, you might as well use one up before they expire. Just maybe you'll get a different result from us.

One thing I forgot to ask - I assume you have applied all available Access & Jet service packs? I know that one of Jet service packs (circa Sep 2003) resolved one of the problems we were experiencing.

Good luck.

 

[jjob]

Thanks for the advice, I'll check I've upgraded my system - I use VMWare to produce various environments and it is more than possible I've failed to update this one.

I've never used MSDN incidents, I don't suppose I could bother you again to ask what you do to raise an MSDN incident can I?

Again thanks for your efforts.

John

 

[Glasgow]

You get 4 incidents per year on Universal I think - i.e. problems you can get MS to investigate. Might as well get your money's worth. 99 times out of 100, I use Tek-Tips and it is fine. But, in this case, I used an incident - though ultimately it was credited back because they didn't really resolve it.

You can do it on-line I think but I suggest phone. Are you in UK? If so, call 0870 5010100. They will obviously ask for some ID (subscription or contract no). Is MSDN in your personal name, someone else's name or company name? Make sure you suss that first in case they refuse to deal with you personally when you call.

 

[jjob]

TVM, I've given you a star for this, as I never realised I could do that, in all the years I've had the subscription!

If anything useful comes of it, I'll post it here

regards

John

 

[Denisbed]

I am in Texas and have experienced exactly the same error.
Out of desperation I put acDataErrorContinue into the error for the form. This kills the error. Yes the error still occurs. It appears when I go to another record in a bound form it errors. got both errors as you described.
I did clean the database, no help. Can't back up, and having a lot of problems with it. converted using acess routine to go from access 97 to 2002.
HELP!!!!!

 

[jjob]

My errors are intermittent, and I've no corruption so I'm not sure if the circumstances are the same. I have experienced the same errors due to corruption, but in this instance there is no corruption. I've informed Microsoft via an MSDN Incident, and am awaiting a response.

AS I said earlier in this thread, I'll post here any response from MS or any fix I may find.

regards

John

 

[jjob]

A Microsoft Engineer has just emailed me, so I'm told, with some suggestions, unfortunately, I won't be home until late tonight, but I'm also hoping to speak to the Engineer on Wednesday afternoon, UK time. I'll post anything of use up here.

 

[Glasgow]

That's an obligatory phase. Understandably, they're not going to jump straight to the conclusion 'oh yes, this one is our fault, Access 97 is better'. However, for your sake, I hope they come up with a different conclusion.

 

[jjob]

These are the links the MS engineer sent to me.

This message assumes corruption, where I think there is none, but I thought I'd paste the links anyway

If you are, in fact, encountering random database corruption, then you will
want to review the following web pages, and apply the suggestions therein:

ACC2000: How to Troubleshoot and Repair a Damaged Jet 4.0 Database

http://support.microsoft.com/?id=209137

ACC2000: Defragment and Compact Database to Improve Performance

http://support.microsoft.com/?id=209769

ACC2000: Updated Version of Microsoft Jet 4.0 Available in Download

http://support.microsoft.com/?id=239114

ACC2000: Jet Compact Utility Available in Download Center

http://support.microsoft.com/?id=273956

ACC2000: Unexpected Project Corruption in Access Database

http://support.microsoft.com/?id=280433

ACC2000: Table Corruption After Referential Integrity Checks

http://support.microsoft.com/?id=296389

HOW TO: Keep a Jet 4.0 Database in Top Working Condition

http://support.microsoft.com/?id=300216

ACC2000: How to Troubleshoot Corruption in an Access Database

http://support.microsoft.com/?id=306204

How to Obtain the Latest Windows 2000 Service Pack

http://support.microsoft.com/?id=260910

 

[jjob]

Still looking at this with the help of Microsoft, and the thinking at the moment is that the sub form has not completed it's update before my code on the parent form starts updating the database - interestingly enough my comment earlier regarding the number of entries visible on the subform

'Now it may be coincidence that 4 objective entries saves without error, more than 4 don't - given that a maximum of 4 can be displayed on the sub form before scrolling.'

seems to be significant, if we expand the subform, we appear to push the figure before failure up to the number of displayed (and partially displayed) sub form records!

That was this afternoon, I'm not 100% convinced that this is the cause, I tend to think it is a symptom - but we are looking at VBA code in the parent to try and figure when the sub form has finished committing it's records. I will keep you all informed. I think both MS and I now agree that there is no corruption, although they have not ruled it out completely, but do suggest if there is, it is very subtle.

 

[Glasgow]

Sounds like it is following a similar path to our own problem where similar suggestions were being made by MS but sounds also like they are delving a bit deeper with your case than they did with ours so it will be interesting to hear the final outcome.

 

[Glasgow]

Have Microsoft waved their magic wand yet?

 

[jjob]

Errm, no, but they are very friendly!

In fact I've figured out most of the 'fixes', one that has removed the 'consistent' error without inceasing sub form 'window' size, was simply to stop the sub form container on the main form being disabled when there was no main form data entered. They think it was a 'thread problem', but I'm now of the opinion (tentative!) that the 3197 and 'bookmark not found' may not be related to the object not found that I think we've fixed. The 3197 & bookmark are so intermittent however, it could be weeks before I can confirm if this also fixed these 8-(

Nice though the guy is, I'm rather surprised, as he appears to be a developer just as I am, and whilst I may not have as much experience with Access as he has, I know the App and have worked on this problem for a considerable time, so I had expected that maybe they would look "under the bonnet" so to speak - it appears they haven't. If they do come up with anything else I'll let you know. I'm about to release a new version to my customer, so I should find out if there is any improvement.