"you and another user..."--standard fixes don't apply

[Jan]

Hi:

Here's a problem I've had for a long time. The client is really running
out of patience, and I have no answers.

Access2003, front- and back-end.

Single form with 4 subforms (each representing a related table), 5-10
clerks doing data entry at one time. Tables are quite large but all
work is done with unbound forms and/or local temp tables, and written
back to the server at intervals (I moved to that structure in the hopes
of solving this problem; it didn't seem to help).

The error is 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."

It generally happens on one particular subform, but sometimes happens on
another one. I believe it happens at the point at which the record is
written back to the main db on the server (from the local temp tables).
The client is in California and I'm in Chicago so I can't watch them
work. They won't give me the ability to log onto their individual
workstations to troubleshoot. I can't reproduce the problem, either on
my computer here, or on the computer to which I do have remote access.

I have searched this problem and looked at all the proposed causes, and
none of them applies.

There are no Memo fields.
I have compacted, rebuilt the tables, written all data to new tables on
a clean blank database, I believe there is no data corruption.
This error occurs intermittently. They can go a few days with no errors
but then have them on every two or three records for a few days.
I believe that most of the clerks get the errors but that some don't, or
some get them less often.

Their server used to be horrendously slow; it's now somewhat better.
I'd love to blame it on their network connections, but without solid
evidence their IT staff is not going to accept that as an answer.

We are working toward switching to a SQL Server back-end. I'd like to
think that will solve the problem, but if it doesn't I'm in deep trouble.

I have no idea how to fix this.

Can someone help?

I'd be happy to pay someone to take this thing apart and find the flaw.
I sure can't see it myself.

Thanks.

Jan

[paii, Ron]

"Jan" <ja*@dontspamme.comwrote in message
news:ST*******************@en-nntp-08.dc1.easynews.com...

Hi:

Here's a problem I've had for a long time. The client is really running
out of patience, and I have no answers.

Access2003, front- and back-end.

Single form with 4 subforms (each representing a related table), 5-10
clerks doing data entry at one time. Tables are quite large but all
work is done with unbound forms and/or local temp tables, and written
back to the server at intervals (I moved to that structure in the hopes
of solving this problem; it didn't seem to help).

The error is 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."

It generally happens on one particular subform, but sometimes happens on
another one. I believe it happens at the point at which the record is
written back to the main db on the server (from the local temp tables).
The client is in California and I'm in Chicago so I can't watch them
work. They won't give me the ability to log onto their individual
workstations to troubleshoot. I can't reproduce the problem, either on
my computer here, or on the computer to which I do have remote access.

I have searched this problem and looked at all the proposed causes, and
none of them applies.

There are no Memo fields.
I have compacted, rebuilt the tables, written all data to new tables on
a clean blank database, I believe there is no data corruption.
This error occurs intermittently. They can go a few days with no errors
but then have them on every two or three records for a few days.
I believe that most of the clerks get the errors but that some don't, or
some get them less often.

Their server used to be horrendously slow; it's now somewhat better.
I'd love to blame it on their network connections, but without solid
evidence their IT staff is not going to accept that as an answer.

We are working toward switching to a SQL Server back-end. I'd like to
think that will solve the problem, but if it doesn't I'm in deep trouble.

I have no idea how to fix this.

Can someone help?

I'd be happy to pay someone to take this thing apart and find the flaw.
I sure can't see it myself.

Thanks.

Jan

Do your uses have separate copies of the front-end or are they sharing?

Have you tried pasting that error message into Google?

[Jan]

They have separate copies. I did paste the error into Google and got a
bit of new info but not much: mainly people who are tearing their hair
out over this error with no solution. The only work-arounds I've seen
are trying to do a brief "sleep" and then retry.

Any ideas?

paii, Ron wrote:

"Jan" <ja*@dontspamme.comwrote in message
news:ST*******************@en-nntp-08.dc1.easynews.com...

>Hi:

Here's a problem I've had for a long time. The client is really running
out of patience, and I have no answers.

Access2003, front- and back-end.

Single form with 4 subforms (each representing a related table), 5-10
clerks doing data entry at one time. Tables are quite large but all
work is done with unbound forms and/or local temp tables, and written
back to the server at intervals (I moved to that structure in the hopes
of solving this problem; it didn't seem to help).

The error is 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."

It generally happens on one particular subform, but sometimes happens on
another one. I believe it happens at the point at which the record is
written back to the main db on the server (from the local temp tables).
The client is in California and I'm in Chicago so I can't watch them
work. They won't give me the ability to log onto their individual
workstations to troubleshoot. I can't reproduce the problem, either on
my computer here, or on the computer to which I do have remote access.

I have searched this problem and looked at all the proposed causes, and
none of them applies.

There are no Memo fields.
I have compacted, rebuilt the tables, written all data to new tables on
a clean blank database, I believe there is no data corruption.
This error occurs intermittently. They can go a few days with no errors
but then have them on every two or three records for a few days.
I believe that most of the clerks get the errors but that some don't, or
some get them less often.

Their server used to be horrendously slow; it's now somewhat better.
I'd love to blame it on their network connections, but without solid
evidence their IT staff is not going to accept that as an answer.

We are working toward switching to a SQL Server back-end. I'd like to
think that will solve the problem, but if it doesn't I'm in deep trouble.

I have no idea how to fix this.

Can someone help?

I'd be happy to pay someone to take this thing apart and find the flaw.
I sure can't see it myself.

Thanks.

Jan

Do your uses have separate copies of the front-end or are they sharing?

Have you tried pasting that error message into Google?

[Salad]

Jan wrote:

Hi:

Here's a problem I've had for a long time. The client is really running
out of patience, and I have no answers.

Access2003, front- and back-end.

Single form with 4 subforms (each representing a related table), 5-10
clerks doing data entry at one time. Tables are quite large but all
work is done with unbound forms and/or local temp tables, and written
back to the server at intervals (I moved to that structure in the hopes
of solving this problem; it didn't seem to help).

The error is 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."

It generally happens on one particular subform, but sometimes happens on
another one. I believe it happens at the point at which the record is
written back to the main db on the server (from the local temp tables).
The client is in California and I'm in Chicago so I can't watch them
work. They won't give me the ability to log onto their individual
workstations to troubleshoot. I can't reproduce the problem, either on
my computer here, or on the computer to which I do have remote access.

I have searched this problem and looked at all the proposed causes, and
none of them applies.

There are no Memo fields.
I have compacted, rebuilt the tables, written all data to new tables on
a clean blank database, I believe there is no data corruption.
This error occurs intermittently. They can go a few days with no errors
but then have them on every two or three records for a few days.
I believe that most of the clerks get the errors but that some don't, or
some get them less often.

Their server used to be horrendously slow; it's now somewhat better. I'd
love to blame it on their network connections, but without solid
evidence their IT staff is not going to accept that as an answer.

We are working toward switching to a SQL Server back-end. I'd like to
think that will solve the problem, but if it doesn't I'm in deep trouble.

I have no idea how to fix this.

Can someone help?

I'd be happy to pay someone to take this thing apart and find the flaw.
I sure can't see it myself.

Thanks.

Jan

Do you have any code, main or subform, that in the form's AfterUpdate it
updates any tables or runs an action query?

Is it a specific person that gets the problem? It may be something they
do that you aren't trapping for...as in you might test it a certain way
but the user does something differently or the user enters something
differently than you.

Do you have any idea when the error occurs? What line of code, what
event triggers the problem?

Are they running a full version of Access and not a runtime? You could
search for On Err and replace with 'On Err for the problem forms code
(don't do that on On Error Resume Next or On Error GoTo 0's) so the code
can get into Debug?

In your error message do you have the routine it breaks on. Ex:
Msgbox Err.Description,,"Form_AfterUpdate"
so you get an idea where the message is coming from?

[Jan]

Hi, Salad:

I know exactly where it happens, and it happens to most if not all the
users, not just one.

It happens in a routine (actually, a function) that writes the record
back to the server, from the temp tables. It's called mostly during
after_update, but sometimes in other places. I've seen the code break
point, and I still don't know what's causing the error. They are using
the full version, not runtime.

In my Google search on this error, some pointed to page locking issues.
I don't see why this would be a problem as the update is done with a
recordset, so the record isn't open for very long.

Other question/ideas?

Salad wrote:

Jan wrote:

>Hi:

....

>It generally happens on one particular subform, but sometimes happens
on another one. I believe it happens at the point at which the record
is written back to the main db on the server (from the local temp
tables). The client is in California and I'm in Chicago so I can't
watch them work. They won't give me the ability to log onto their
individual workstations to troubleshoot. I can't reproduce the
problem, either on my computer here, or on the computer to which I do
have remote access.

I have searched this problem and looked at all the proposed causes,
and none of them applies.

There are no Memo fields.
I have compacted, rebuilt the tables, written all data to new tables
on a clean blank database, I believe there is no data corruption.
This error occurs intermittently. They can go a few days with no
errors but then have them on every two or three records for a few days.
I believe that most of the clerks get the errors but that some don't,
or some get them less often.

....

Do you have any code, main or subform, that in the form's AfterUpdate it
updates any tables or runs an action query?

Is it a specific person that gets the problem? It may be something they
do that you aren't trapping for...as in you might test it a certain way
but the user does something differently or the user enters something
differently than you.

Do you have any idea when the error occurs? What line of code, what
event triggers the problem?

Are they running a full version of Access and not a runtime? You could
search for On Err and replace with 'On Err for the problem forms code
(don't do that on On Error Resume Next or On Error GoTo 0's) so the code
can get into Debug?

In your error message do you have the routine it breaks on. Ex:
Msgbox Err.Description,,"Form_AfterUpdate"
so you get an idea where the message is coming from?

[JvC]

Can you post the function? It will make it easier to figure out!

John

After serious thinking Jan wrote :

Hi, Salad:

I know exactly where it happens, and it happens to most if not all the users,
not just one.

It happens in a routine (actually, a function) that writes the record back to
the server, from the temp tables. It's called mostly during after_update,
but sometimes in other places. I've seen the code break point, and I still
don't know what's causing the error. They are using the full version, not
runtime.

In my Google search on this error, some pointed to page locking issues. I
don't see why this would be a problem as the update is done with a recordset,
so the record isn't open for very long.

Other question/ideas?

Salad wrote:

>Jan wrote:

>>Hi:

...

>>It generally happens on one particular subform, but sometimes happens on
another one. I believe it happens at the point at which the record is
written back to the main db on the server (from the local temp tables).
The client is in California and I'm in Chicago so I can't watch them work.
They won't give me the ability to log onto their individual workstations
to troubleshoot. I can't reproduce the problem, either on my computer
here, or on the computer to which I do have remote access.

I have searched this problem and looked at all the proposed causes, and
none of them applies.

There are no Memo fields.
I have compacted, rebuilt the tables, written all data to new tables on a
clean blank database, I believe there is no data corruption.
This error occurs intermittently. They can go a few days with no errors
but then have them on every two or three records for a few days.
I believe that most of the clerks get the errors but that some don't, or
some get them less often.

...

>Do you have any code, main or subform, that in the form's AfterUpdate it
updates any tables or runs an action query?

Is it a specific person that gets the problem? It may be something they do
that you aren't trapping for...as in you might test it a certain way but
the user does something differently or the user enters something
differently than you.

Do you have any idea when the error occurs? What line of code, what event
triggers the problem?

Are they running a full version of Access and not a runtime? You could
search for On Err and replace with 'On Err for the problem forms code
(don't do that on On Error Resume Next or On Error GoTo 0's) so the code
can get into Debug?

In your error message do you have the routine it breaks on. Ex:
Msgbox Err.Description,,"Form_AfterUpdate"
so you get an idea where the message is coming from?

[lyle fairfield]

This sounds like a joke to me. No one in his/her right mind would
create such an application. I recommend incinerating all the computers
that have anything whatever to do with this application, and a
complete new start from scratch.

On Aug 28, 6:50*pm, Jan <[e-mail]:

It happens in a routine (actually, a function) that writes the record
back to the server, from the temp tables. *It's called mostly during
after_update, but sometimes in other places. *I've seen the code break
point, and I still don't know what's causing the error. *They are using
the full version, not runtime.

In my Google search on this error, some pointed to page locking issues.
* I don't see why this would be a problem as the update is done with a
recordset, so the record isn't open for very long.

[GrafixSoft]

On 28 Aug, 23:28, Jan <[e-mail]:

Hi:

Here's a problem I've had for a long time. *The client is really running
out of patience, and I have no answers.

Access2003, front- and back-end.

Single form with 4 subforms (each representing a related table), 5-10
clerks doing data entry at one time. *Tables are quite large but all
work is done with unbound forms and/or local temp tables, and written
back to the server at intervals (I moved to that structure in the hopes
of solving this problem; it didn't seem to help).

The error is 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."

It generally happens on one particular subform, but sometimes happens on
another one. *I believe it happens at the point at which the record is
written back to the main db on the server (from the local temp tables).
* The client is in California and I'm in Chicago so I can't watch them
work. *They won't give me the ability to log onto their individual
workstations to troubleshoot. *I can't reproduce the problem, either on
my computer here, or on the computer to which I do have remote access.

I have searched this problem and looked at all the proposed causes, and
none of them applies.

There are no Memo fields.
I have compacted, rebuilt the tables, written all data to new tables on
a clean blank database, * I believe there is no data corruption.
This error occurs intermittently. *They can go a few days with no errors
but then have them on every two or three records for a few days.
I believe that most of the clerks get the errors but that some don't, or
some get them less often.

Their server used to be horrendously slow; it's now somewhat better.
I'd love to blame it on their network connections, but without solid
evidence their IT staff is not going to accept that as an answer.

We are working toward switching to a SQL Server back-end. *I'd like to
think that will solve the problem, but if it doesn't I'm in deep trouble.

I have no idea how to fix this.

Can someone help?

I'd be happy to pay someone to take this thing apart and find the flaw.
* I sure can't see it myself.

Thanks.

Jan

I had the same problem some time ago. The application is a frontend,
backend AXP.
The error was ocurring only on my clients computers. Three users doing
data entry at the same time. The error was thrown even if only one
user (frontend was on the same computer where backend was located) was
entering data and no one was connected to backend database. We checked
network cards, cables, service packs and everything we thought it
might cause the problem. I tried to duplicate the error on my
computers but everything worked fine. Our client was also running out
of patience.
After few weeks of searching and testing the only solution we got was
to ignore the error. But be aware that this 'solution' apply only if
your data are saved. This was the case with my application. The data
was saved but the error '3197' was thrown.
So, in the form_error procedure I put this piece code:
'If DataErr = 3197 Then
Me.Requery
End If
Response = acDataErrContinue'.
Then suddenly the error '3159', '3001', '2237' were thrown.
I ended up with this piece of code in the form_error procedure:
'If DataErr = 3197 Or DataErr = 3159 Or DataErr = 3001 Or DataErr =
2237 Then
Me.Requery
End If
Response = acDataErrContinue'.
So, the point is that you have to capture the error and ignore it,
providing that data is saved and everything else but the error message
is ok.
The error still happens but this is ignored and no message is
displayed.
Now almost a year has passed, the client is happy and there were no
problems regarding this issue anymore.
I don't say that this is the solution or the best solution but a
solution.

HTH

Cheers

Sebastian

[Keith Wilby]

"Jan" <ja*@dontspamme.comwrote in message
news:ST*******************@en-nntp-08.dc1.easynews.com...

>
all work is done with <sniplocal temp tables, and written back to the
server at intervals (I moved to that structure in the hopes of solving
this problem; it didn't seem to help).

If that didn't help (and I wouldn't expect it to) then remove that
functionality, it is redundant and might actually add to your problems.

Keith.
www.keithwilby.com

[CDMAPoster]

On Aug 28, 4:28*pm, Jan <[e-mail]:

Hi:

Here's a problem I've had for a long time. *The client is really running
out of patience, and I have no answers.

Access2003, front- and back-end.

Single form with 4 subforms (each representing a related table), 5-10
clerks doing data entry at one time. *Tables are quite large but all
work is done with unbound forms and/or local temp tables, and written
back to the server at intervals (I moved to that structure in the hopes
of solving this problem; it didn't seem to help).

The error is 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."

It generally happens on one particular subform, but sometimes happens on
another one. *I believe it happens at the point at which the record is
written back to the main db on the server (from the local temp tables).
* The client is in California and I'm in Chicago so I can't watch them
work. *They won't give me the ability to log onto their individual
workstations to troubleshoot. *I can't reproduce the problem, either on
my computer here, or on the computer to which I do have remote access.

I have searched this problem and looked at all the proposed causes, and
none of them applies.

There are no Memo fields.
I have compacted, rebuilt the tables, written all data to new tables on
a clean blank database, * I believe there is no data corruption.
This error occurs intermittently. *They can go a few days with no errors
but then have them on every two or three records for a few days.
I believe that most of the clerks get the errors but that some don't, or
some get them less often.

Their server used to be horrendously slow; it's now somewhat better.
I'd love to blame it on their network connections, but without solid
evidence their IT staff is not going to accept that as an answer.

We are working toward switching to a SQL Server back-end. *I'd like to
think that will solve the problem, but if it doesn't I'm in deep trouble.

I have no idea how to fix this.

Can someone help?

I'd be happy to pay someone to take this thing apart and find the flaw.
* I sure can't see it myself.

Thanks.

Jan

Maybe the following standard fix will help:

PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60

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

James A. Fortune
CD********@FortuneJames.com

[wow computer]

The Access Front End (and a web front end as well) SQL2000 back end
application I have been working on is not quite as complex on the access
side and is definitely not simultaneously modifying the same underlying
record sources in different forms. But I did find how to correct this
error for my situation.

I tried this in both DAO and ADO and both worked. I was adding a new
record and only assigning values to fields that I wanted to populate at
that time. For instance lets say there are 3 fields I was only
assigning 2. I found by explicitly setting the value of the 3rd field
to null this problem was rectified. After deleting the records I was
working with and inserting new and updating through forms I no longer
received the error.

I got the idea from a Microsoft article relating to the ADO run time
error that is the same as this one.
http://support.microsoft.com/kb/q279888/

Hope that helps.

*** Sent via Developersdex http://www.developersdex.com ***

[Jan]

I got buried in other projects and lost track of this, so now here I am
back with a question; hope you're still around, Keith:

You said this would add to the problem, and I'm not sure which part you
think does this: is it the unbound forms, the local temp tables, or the
"written back to the server at intervals," or what?

THanks.

Jan

Keith Wilby wrote:

"Jan" <ja*@dontspamme.comwrote in message
news:ST*******************@en-nntp-08.dc1.easynews.com...

>>
all work is done with <sniplocal temp tables, and written back to
the server at intervals (I moved to that structure in the hopes of
solving this problem; it didn't seem to help).

If that didn't help (and I wouldn't expect it to) then remove that
functionality, it is redundant and might actually add to your problems.

Keith.
www.keithwilby.com