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! :-(