Error Message The Microsoft Jet Engine stopped the process because

B

BillieGoat

I have a client that has a database that we have converted from 97 to 2003.
We converted the programs first and things seemed to run fine except that it
was slow. We converted the data and we are now getting the Error Message:
"The Microsoft Jet Engine stopped the process because you and another user
are attempting to change the same data at the same time".
This error occurs on two different subforms of a parent form. One of the
subforms has some complex calculations the other does not and really doesn't
have any extra VB code behind it. The error only occurs occationally and
doesn't seem to have a ryhme or reason. It does not occur when I have my
tables link locally but occurs when I am connected to the "server" PC which
is a Windows XP Professional PC.
We can enter the same claim again and not get the error. It happens on all
the PCs that are using this database.
I have been working on this issue for several months and tried optimizing
everything I could. While this has spead up the database, we are still
getting the error.
I had the same problem at another client and optimized the database, found
that many of the problems were network related and the issue seems to have
gone away. I have done the same thing to this database but I am still getting
the error.

Help!!!!
Billie
 
K

Ken Snell [MVP]

I have gotten this error when using a subform and running code in the
subform and the main form at the same time. It can be a timing issue for the
code. What happened for me is that the person was editing data in the
subform record, then clicked directly onto the main form onto a command
button that updated the data. Because of a short timing issue, the main form
tried to save the data while the subform was still "dirty" and hadn't saved
its data yet.

Here is info from a post I did back in September 2003:
----
This error can occur in many ways (see numerous KB articles about it), but
the first place to look is for situations where the user can begin editing a
record in a subform (makes it dirty), then clicks onto another part of the
form (another subform, or the main form) and causes some code to run on that
new area of the form. The initial subform is NOT written to its recordsource
at that time; the subform is still dirty. If your other code is trying to
edit the same record/table info, you can get this error.

I have overcome this problem on some forms by forcing the subform to save
its current record in the code that runs on the other part of the form.

It also can be a sign of corruption.

See this URL for some KB articles (watch for line-wrapping, as the URL is
one line):
http://support.microsoft.com/search...e=PHRASE&maxResults=150&Titles=false&numDays=

----

Take a look at your setup and see if it's possible that this error is
resulting from such a conflict in your code. The fact that you see it on a
network but not on your local setup is symptomatic of this problem.

If this is the problem, you may need to reprogram the form so that the code
conflict doesn't occur (cannot click on a button to cause more code to run
until the one is done), to force the main form's code to finish the
subform's code by forcing it to save the record in the subform before the
main form continues, or to put a delay in the main form's code of a few
seconds or so to allow the subform to finish its updating.
--

Ken Snell
<MS ACCESS MVP>
 
B

BillieGoat

Thanks, I'll take a look at these. It seems that is what is happening. It does
seem to be a timing issue since it only happens sometimes. Some days more than
others and on one PC over the other but it does happen on all the PCs. I
have made a
couple changes since I posted so I'm waiting to see if these help.
I've checked out the corruption but I really don't think so since it ran
fine with
the 97 data. Of course at first I thought it was that.
One benifit is that I've learned numerous ways to speed things up for the
client.

I'll post what I find so that it may help someone else with this issue.

Thanks
Billie Cornell
 
B

BillieGoat

How do you put a delay in the code? I must be searching on the wrong
words.
It seems like the error is happening when they are going to a new record
on the same subform. This is happening on two different subforms. One has a
complicated
calculation and the other doesn't have any calculations.
I do have fields that are using the =Sum() function. I am going to try and
change that to
a query look up and see if that helps any.
Do you know why the Sum function shouldn't be used on a form anymore?

Thanks
 
K

Ken Snell [MVP]

You can use Sum on a form, but it takes a little time to calculate.

Easiest way of "pausing" is to loop through a call to DoEvents:

Dim intLoop As Integer
For intLoop = 1 to 1000
DoEvents
Next intLoop


Another way is to test for the completion of some task (a field or control
has a value when it didn't before, the form's NewRecord property is now true
when it was false, etc.).

Another way is to have just one code section run all the code that you need
run -- not always feasible when different events occur in succession /
concurrently and each has its own code to run.

Another way is to "prohibit" the action that can cause the concurrent events
until the first event's code is done. For example, I sometimes will disable
a button when the form becomes "dirty" until I've saved the record, then the
code re-enables the button. That way, the user cannot click the button and
cause the concurrent situation.

--

Ken Snell
<MS ACCESS MVP>
 
B

BillieGoat

Well, I tried putting in a pause several places in the code where I thought
the error might be happening but I am still getting the error.
On the one form it does do some pretty advanced calculations and sometimes
will
take a second before it goes onto the next field. In the other sub form it
really doesn't
do anything except look up a description from another table.
I don't really have a way to stop the code from making the event not happen
as all it is doing after
the event is going to a new record. I am checking the form on several events
to see if it is dirty and saving
the record if it is. They aren't clicking any buttons when the error occurs,
just going to the next, new record.
The records on the sub form get a #Error in the fields once this error
occurs but if you get out of the whole form and then
back in the record has been saved.

This is the sequence that is occuring;
1. Enter information into the main form, when that is complete then the
program asks if you want to save the record. If you select "yes"
it saves the record. Then puts you into the first subform to enter CPT Codes.
2. Key in CPT Code (once the cptcode has been entered it get the CPT
Description form a different table and puts the description in the descriptin
field which is unbound), Modifier Code, Amount Billed, Date, and then Number
of times. At this point it does some advanced calculations to re-price the
bill. The next field you move to is the Description field which is Locked.
Then it goes to a new record.
3. If all CPT codes have been entered you then click the the diagnosis code
tab to go to that sub form. You enter the diagnosis code, the description is
populated from another table and then you go to the next record to be added.
The error always occurs when you have finished entering information into one
form and then you have gone to the next new record to add.

Not sure where else to look or put in the code to try and catch where the
problem is happening.

Thanks

Billie
 
K

Ken Snell [MVP]

I'd have to see the database to better understand what your setup is. I have
no idea which tables/queries are involved in the two subforms and in the
main form. Does the error happen only when you go from one subform (which
one?) to the other? Are the two subforms using queries that share a common
table/query?
--

Ken Snell
<MS ACCESS MVP>
 
B

BillieGoat

It seems to happen when you are moving to a new record with in the same sub
form.
The two subforms are not using the same table(s) at all. The only thing is
that they both
are linked to the main form through a claim code which is the key.
It's really a strange beast. Today I was entering test claims and it would
give me the error
every few claims I entered pretty consistantly. I would change something in
the code and then
try again. By the end of the day I took out most of the changes that I had
added earlier today then
started testing again. I went though 10 claims and didn't get a single error.
That's wear I left it so we'll see if the client starts getting the error
message Monday morning again.
It seems to take a break some days and then is really bad other days. But no
consistancy of what is
going on. I do have it compacting on close which should keep it cleaned out.

Thanks for the help. I keep trying the suggestions. I'm sure something will
work sooner or later. It will
probably be something so simple.

Billie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top