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]

Have you tried the Compact and Repair option? This may fix the problem.

How are you accessing the table? Via a form or in the table view?

Is the table related?

Do you get this message when you edit a specific field or any specific record?

Let me know.

Regards,

Richard in Tulsa

 

[Glasgow]

Thanks for responding Richard.

I have not done compact and repair but this is a customer's database and they compact and repair daily and continue to get the problem. Also, the database has been freshly converted from Access 97 and we don't get the problem in Access 97.

We are accessing via a form (in fact a sub-form on a form). We are also experiencing 'Not a valid Bookmark' problems (again not a problem in Access 97) with the same table but I'm not sure if this is relevant.

When I view the relationships, this table does not appear to be related to any others. In practice it is related to an Invoice table.

Hope that helps!
The error does not occur with a specific record - in fact we cannot consistently reproduce the problem, which is frustrating. It appears to trigger as focus moves from the subform to another control. There is only one 'quantity' column in the subform that can be modified so difficult to tell if a specific field is at the root of the problem.

Also, the error re-triggers and continues to do so after each Refresh Interval has elapsed.

 

[rkdcomp]

Since it seems that your problem is related to one table, try this.

Select the table, then do a Copy/Paste, but only select the structure option. Give the new table a different name. This will create a new table with all the fields and settings from the old table.

Then create a new query based on the old table. Add all the fields to the grid. Run the query to see if all the data is there, it should be.

Then go to the design of the query and change it to an "Append" query and append it to the new table. Run it and all the records from the old table will be copied into the new table.

Since the table is not related to any other table, you should be able to rename it. Change the name of the old table to something like TABLENAME2 (add the 2), then Rename the new table to the same name as the old table.

See what this does. Since the error occurs intermittent, this will be a killer to find and fix.

Browsing around the Fourm I found this.

http://www.granite.ab.ca/access/corruptmdbs.htm

Gave it a quick look and it seems to cover a ton of errors.

Regards,

Richard in Tulsa

 

[Glasgow]

I tried this thanks and I see where you are coming from but I don't think it has helped - still getting bookmark problem and, although the 3197 error has not yet resurfaced, its intermittent nature suggests it could still be waiting to happen again.

I don't suppose you know of any utility that would perform this operation (i.e. dump & reload) all tables in the database - in case you are on the right track but we have problems with more than one table?

 

[rkdcomp]

Let's go one step further. You mentioned this was a converted db from 97 into 2000 and didn't have the problems in 97.

So maybe someplace in the conversion things went bump in the night.

So try creating a FRESH db in 2000. Then try File Get External Data Import and import the tables from the "bad" db and see what happens.

Another way that would work and keep the "bad" tables out of the new 2000 db is to try File Get External Data Link. This would keep the tables out side of the new db. Then use a query and run "Make Table" queries to use the data in the linked tables into really fresh new tables in the new db.

Is the old 97 db still available? May try the above using the 97 db.

Just some more thoughts.

Richard in Tulsa

 

[Glasgow]

Thanks Richard. We may well try this on Monday but we have been wrestling with this for a few days now and, time to down tools is approaching here in UK. We have also decided to use up a valuable MSDN incident and Microsoft will (?) be calling us back on Monday.

I will update this thread with final resolution - as well as with results of your further suggestions.

Your efforts are appreciated.

 

[rkdcomp]

I'd be interested to hear what MS has to say.

Have a good weekend and hope it's a COOL one.

Richard in Tulsa, OK

 

[Glasgow]

Actually I just tried your first 'Fresh' database suggestion. No joy I'm afraid, same problems. Of course one thing I'm uncertain about is whether, when we encounter one error, this creates the corruption that causes the others.

The 97 db is still available.

Have a good weekend also.

 

[Glasgow]

10 days on and still waiting for response from Microsoft. They have not even acknowledged whether they have been able to emulate the problem. I wonder if MSDN subscription is really worth it!

 

[Glasgow]

It appears that, after over two weeks, the Microsoft explanation that I will have to accept is that this is down to timing issues where certain code that I understand to be running concurrently (e.g. DLOOKUP statements) is conflicting. The fact that it is happening under Access 2000 and not Access 97 could be down to the additional overhead of Jet 4.0 vs 3.5.

Microsoft's suggestion, believe it or not, is to rewrite the application (which is not our design and is certainly open to criticism) or revert to Access 97.

 

[jjob]

Hi

I have the same problem, so wondered if you have made any further progress on this. I managed to miss this thread so started another, it was only towards the end when I was getting 'bookmark' errors I found yours. My thread, not as useful as this, is thread181-788113

TIA

John

 

[Glasgow]

Hi John

Re progress - no, not really. We have found that the problem(s) disappear when the database size is reduced. In fact we are about to run a controlled purge on the client's database.

It seems that Access 2000 / Jet 4 simply cannot handle this combination of database size and application complexity. Shame they don't have this plastered across the sales literature.

 

[jjob]

Many Thanks for that feedback - is this also true about Access 2002/03 - as I believe they are still Jet 4?

Out of curiosity, what size is your database?

TIA

John

 

[Glasgow]

My guess is that more recent versions of Access/Jet are probably, if anything, worse still. My interpretation of what we were told is as follows:

a) As new versions of MS products evolve, they become less forgiving than their predecessors and practices which may have 'escaped unheeded' in an older version may be rejected in a new version.

b) Substantial changes in the Jet database engine between Access 97 and Access 2000 can lead to performance issues in more substantial and complex applications. The degraded performance can in itself cause timing issues which causes the application to proceed with certain processes before others complete. The busier the network, the more likely this is to happen – this could explain why we could not emulate some of the problems experienced by our customer.

The database in question is about 80Mb but contains 80+ tables; 700+ queries; 200+ forms and 350+ reports. It also involves a front end with linked tables. We are expecting that the purge we are organising should halve the database size.

 

[rkdcomp]

Long time no hear. I'm glad you at least got going on the right track, but it's unfortunate the track looks broken.

Your answer really scares me as our company will be upgrading all our hardware and software very shortly.

The hardware is no problem, but the OS will be WinXP and the Office Suite will be Office 2003!

The bad part is I have probably a dozen programs currently running under the current version of Access 97. One is large and the others are small. It's will be fun to see what happens when I have to convert them from 97 to 2003.

Your answer will sure help when I have to call "our" HELP DESK since they will be the support on this corporate installation.

I see fun and games coming down the road.

I better have double back-up of everything before I proceed.

Have a great week!

Regards,

Richard
Tulsa, OK USA

 

[Glasgow]

Well it would be interesting to hear just how similar jjob's symptoms are and if, by purging (a copy of!) the database of a reasonable volume of records, whether the problem disappears. Otherwise, maybe ours is an isolated case?

I think a lot will depend upon the quality of the database design and application code. The one we were converting is, er, far from perfect.

It might be wise to flag the issue with your company NOW so at least you can say 'I told you so' when the thingy hits the rotary cooling device. Though no point in spreading panic - it is only a risk rather than an inevitability.

 

[rkdcomp]

In fact, tomorrow I'm heading down to our headquarters for some training on the new heardware/software and WILL bring up the question of Access conversions.

I'll let you know what they say. I really don't expect any real answers as they probably haven't run into any problems yet.

I wonder if Access 2003 has the same option to use a previous version as the default. 2002 can create db's in 2002 format and are backward compatable to 2002.

We'll see.

Take care,

Richard

 

[jjob]

My symptoms are identical, A master form with a sub form (sales rep details for a visit on the main form, list of objectives on the sub.) This form appeared fine until it was required to be called from a complex form via a command button. (although I can't be sure that it was not already a problem, but got worse on moving)

The form 'saves' temporary stuff in to 'permanent' data. Initially I used the same sub form table for the temporary data and ran an update on the foreign keys using SQL execute, I then tried a DO LOOP in a recordset, then a DO LOOP with a temporary table inserting from that into the live, then the temp table with a SQL insert. Each change altered the problem slightly, moving from the 3197, 'Jet Stopped the operation', now to the 'Bookmark' problem. I belive my symptoms are identical. I have a front and backend database, and I have the new temp table in the front end, but still problems. I'll check the database BE size tonight, but the front end is about 4 Meg - with very complex forms. The back end has about 28,000 large customer records, plus various other tables AND it worked OK in Access 97.

 

[Glasgow]

jjob Yes your problem does sound VERY similar. We got bookmark problems too. Very frustrating. It would be worth just trying to remove a chunk of those customer records (and perhaps records from other related tables - orders, receipts or whatever) to see if the problem is alleviated or removed.

Do you always get a problem or is it a bit unpredictable? We found unpredictable - it might happen every 6-7 attempts for example - particularly after we had cut the database size down by less significant percentages.