I am getting an intermittent error 3197 in Access 2003 on an unbound form that has several sub forms that are bound to local tables. When the user gets the error the current sub form record selector gets a not sign and the fields are replaced with #Error. There are no OLE nor Memo data types. Has anyone else had this error or any ideas about how to get rid of it?
I was able to get rid of some of the occurances of this by changing my code from:
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.
This error typically occurs when a long value column (an OLE or MEMO data-type) has bad data stored in it. Long value columns are typically stored in a separate page from the page that the row is stored in. If a long value column is present in the table schema, the Jet database engine will attempt to read the long value page when reading the row of data. In order to read the long value page, there is a pointer in the row of data. This error is generated when the Jet database engine cannot properly read the long value page from the pointer present in the data row. When viewing a row that exhibits this behavior in Microsoft Access data-windows, the user will typically see the number sign (#) for the entire row.
I am using Access 2003 though. I have one form with 5 subforms. Four of them are on tabs. The main form (wasn't but is now) and all sub forms are bound to local tables. (When the Accept button is clicked, the data is transferred to the backend database). The record locking property of all of the forms was No Locks.
Since my last post, I have tried rebooting the terminal server, adding line numbers and error messages for every single line of my vba code. The error came up without any of my errors appearing. I added extra code that checks the value of fields and updates the value only if it is new. I have put "if me.dirty then" before every single save command. I made sure that in every module where I use the set command, that I include the "set x = nothing" lines or used rs.close. The 3197 error message would still come up intermitantly. It was noticed that exiting the form and going back in once the error arose would allow the next record to be entered with out a problem, so I made a module that would close and reopen the form when the Accept button was clicked. That did not fix it.
I gave them the mdb and tried running it in non-runtime mode, but the error returned. I converted all of their back end databases to 2003 from 97. I also ran compact and repair several times. I ran it on their system and eventually got the error. Just sitting staring at the screen, the error came up once per minute.
I most recently upgraded the subforms to have record locking of Edited Record (I tried All Records, but it would not allow me to clear the tables to reset the forms for the next record.) The error came back this afternoon.
And the lastest installment to this saga goes thus:
I started with a blank 2002-2003 format database and over the past 3.5 days, I created tables, forms, queries, reports and procedures from scratch while trying to keep the same level of functionality, but using the best code I can fathom. Instead of using temporary query definitions, I used parameterized queries. Most Dlookups were replaced with recordsets based on more parameterized queries. I created only the basic functionality and did not include anything that was not required. The 3197 error came up again.
The only unique thing I can think of with the one site that is getting this error is that their 2003 Terminal server has 2 cpu's. It has been running with the latest jet 4 patch since the beginning: Update for Jet 4.0 Service Pack 8 (KB829558).
Does anyone have any idea on how to overcome this error?
I imported the data to local tables and received the error, so I thought it may be a corruption problem after all. I doubted it since the simple act of exiting the form and reloading it would render it usable again, but I was running out of options. I ran jetcomp.exe on the backend and frontend databases. It did not help, so, after over a month of this, I gave up. I added in form error checking with a me.requery and a Response = acDataErrContinue and so far it seems to have gotten rid of the symptoms. It would be nice to be able to get rid of the actual cause of the problem, but it does not seem to be a problem within my code.
If anyone ever figures out the cause and a fix, please let me know. I hate using useless error checking as a workaround.
Here is an update. At least one MS Access MVP thinks this happens when a subform is dirty and code is run on the main form. I know this is not the case because I checked for exactly that as soon as I read the error message about two people saving the same record. I had put a tonne of saves in such that whenever anything was changed or added, it would be saved before focus left the form or subform. Note also that the tables affected were only front end tables to which only one person at a time could use.
As soon as I started using error checking for 3197, several other errors started to crop up for no apparent reason, so I ended up with the code showing below:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
'The following error is causing #Error to be put into the fields
'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.
'These errors started to come up when this error checking sub was added
'3159 Not a valid bookmark.
'3001 Invalid argument.
If DataErr = 3197 Or DataErr = 3159 Or DataErr = 3001 Or DataErr = 2237 Then
MsgBox "There has been an error: " & DataErr, 48, "Receipts Error 111"
Response = acDataErrContinue
Again, if anyone has a better fix, please let me know.