Error 3197 on subforms

[Guest]

Hello,

We are experiencing an intermittent problem that is affecting some forms in
an Access XP database. At some sites the problem occurs almost everytime and
at others it is rare. The database is split into a frontend and backend.

What happens is that when viewing a form, if a new record is entered on the
subform then the user will get a error 3197 when Access tries to write the
record to the database. They then ok the error message and #Error then
appears in one of the bound controls on the subform. This will then happen
for every bound control on the subform until they all have #Error in them.
The user can then enter subsequent records without any problems. If the user
then presses F9 to refresh the subform, the record with the #Error's in is
refreshed and the data that the user entered appears.

This only occurs for the first record to be entered. If a subform has
calculated controls, we can tell that the problem is going to occur because
#Error will appear in the calculated controls before any data is entered into
the new record. Pressing F9 will clear the problem.

In one particular subform that has this problem, as a test, we have set
HasModule=No and bound the form direct to the table instead of using a query
but the problem still occurs (The table does not include any memo fields).

On another subform, it's parent form is unbound and the subform is not
linked to it on any fields but the problem occurs.

Has anyone got any ideas what it causing this or how it can be avoided?

Thanks.

 

[Allen Browne]

First step has to be to trace what is different about the machines that
throw the error compared to those that don't.

Is there are difference in the JET service patch or Office service patches?
msjet40.dll (typically in Windows\system32) should be version 4.0.8xxx.0.
The xxx digits don't matter, but if they don't see the 8 they need to get
SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
Similarly, check if all machines are SP3 for Office XP.

If the problem persists after that, what's the story with the network? Any
wireless connections? (Access does not handle WiFi well.) What's the server?
If Windows NT, I have seen something similar. If Windows 2000, make sure
that the server has opportunistic locking turned off. (Should be disabled by
default once SP3 for Win Server 2000 has been applied IIRC.)

Hope that's a useful lead.

 

[Guest]

Hi Allen,

I have gathered more information about some of the environments on which the
error occurs. All sites suffer from the problem but some a great deal more
than others, for example, we have had great difficulty in recreating the
problem in-house but have done so whereas some sites the error occurs 90% of
the time.

Platforms: Windows 2000 SP3/4 Terminal Server, Windows 2003 Terminal Server,
Windows XP Pro SP2, Windows 2000

The error occurs when using Terminal Services and when using it over the LAN
so networking is not the problem.

The error occurs using either Access 2002 SP2 or SP3.

Jet 4.0 SP8 is installed on all platforms.

The error occurs with both single and multiple users.

The error seems to occur more frequently on high spec Terminal Servers, e.g.
Twin Xeon Processor >=1Gb RAM


Is there anything else that can be ruled out?

 

[Allen Browne]

Mike, I'm not sure I have an answer for you, so hopefully others will jump
in as well.

You say the database is split: we should just check that everyone has their
own copy of the front end, i.e. they are not opening the same mde. (Don't
laugh: have seen it done.)

Others have more experience with Terminal Server than I, but it's generally
a good arrangment in that the client sessions are actually running on the
server, so won't just fall off.

It's also worth eliminating the usual suspects: Name Autocorrect (shudder!),
Subdatasheets, long path names in the Connect property, virus scanning
software, etc. More info on that:
http://www.granite.ab.ca/access/performancefaq.htm

Beyond that, I did see a similar situation once, but did not really solve
it. It seemed to be a timing issue, where some workstations would show
#Error for all calculated fields, and also the listboxes/combos would not
load properly (typically showing only the first 30 ~ 60 of several hundred
entries.) I suspect it was related to the client's network (based on old Win
NT servers), but I was not their net admin and so did not spend time trying
to pin it down. They were using A2000. We diminished the problem by
installing JET 4 SP8, SP3 for Office 2000, and going peer-to-peer instead of
the server. We never eliminated the problem though.

Some rather lame suggestions that might pin-point the culprit:
- Turn off record-level locking.
- Use optimistic locking.
- Decompile, and recompile using A2002 (esp. if you edited the software in
A2003.)
- Avoid running code in the Timer event if possible.
- Test withtout code in the Current event, and without conditional
formatting. (These can cause some intense "Calculating...")

 

[Guest]

Hi Allen,

Thanks for all your suggestions. I have now tried most of them but to no
avail and can confirm that the problem also occurs with Access 2003 SP1.

For the moment, I have put some code in the Form_Error event to check for
error 3197 and then requery the form if it occurs. Whilst not a solution, it
at least will make the form usable.

Thanks again.