The Wysard needs help...! Bogus error message.

[John Vinson]

I've got a client site where we're having a totally baffling problem.
On one form, we frequently get a message

The Microsoft Jet engine stopped the process because both you and
another user are attempting to change the same data at the same time.

We've gotten the message *occasionally* on another major form, but in
that case it might indeed be two people hitting the same record. It's
not in this case however! It is NOT an accurate message; while this
is a shared database, we can get the same message running when nobody
else is in the database, front or backend. Naturally my first thought
was that I had some code or another form with the record open; however
that is not the case. Setting Locks on the form (and its subforms) to
No Locks, or to Current Record, has no effect. The error is not
consistant; sometimes it lets us update the record, sometimes we get
the error. When I copy the databases from the client site to my PC I
do NOT get the error.

It's a complex form on a complex database, so it's quite possible I'm
missing something - but I need a fresh pair of eyes on the problem.

Access2003, in 2002/2003 format; split front/backend; JET 4.0 SP8;
Windows XP, SP1 on some machines SP2 on others (and there's no
correlation). My client and I will happily pay for any solution; I'm
sure that he would provide Citrix access to the client computer if
that's needed.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dirk Goldgar]

I've got a client site where we're having a totally baffling problem.
On one form, we frequently get a message

The Microsoft Jet engine stopped the process because both you and
another user are attempting to change the same data at the same time.

We've gotten the message *occasionally* on another major form, but in
that case it might indeed be two people hitting the same record. It's
not in this case however! It is NOT an accurate message; while this
is a shared database, we can get the same message running when nobody
else is in the database, front or backend. Naturally my first thought
was that I had some code or another form with the record open; however
that is not the case. Setting Locks on the form (and its subforms) to
No Locks, or to Current Record, has no effect. The error is not
consistant; sometimes it lets us update the record, sometimes we get
the error. When I copy the databases from the client site to my PC I
do NOT get the error.

It's a complex form on a complex database, so it's quite possible I'm
missing something - but I need a fresh pair of eyes on the problem.

Access2003, in 2002/2003 format; split front/backend; JET 4.0 SP8;
Windows XP, SP1 on some machines SP2 on others (and there's no
correlation). My client and I will happily pay for any solution; I'm
sure that he would provide Citrix access to the client computer if
that's needed.

John -

I was thinking about corruption, but since it doesn't happen when copied
to your computer, that seems unlikely. I have only vague wonderings to
offer:

Could there be some task running on the network that does periodic
backups, and locks the file temporarily while it does so?

You mentioned Citrix. Is this happening only when the database is
accessed over a Citrix connection?

You say it happens mostly on this one form, and maybe occasionally on
another. Do the forms have anything in common? Is this form more
heavily used than other forms (so that the problem appears to be linked
to the form, but is really just happening periodically)?

Are there other forms bound to the same table as this form? Can you
verify that they don't show the problem? In other words, can we link
the problem to a particular table?

Are there any operations occurring in Timer events?

 

[Allen Browne]

Hi John

This can be hard to trace. Hopefully you'll get multiple responses, as I
believe there are multiple possible causes.

1. The most obvious one is a corruption. You've probably tried a decompile
already? A rebuild (import into a fresh database) is probably worth a shot,
except this particular form (and its subforms?) should probably be done via
SaveAsText/LoadFromText.

The others are the usual suspects:

2. Explicit saves. Set Me.Dirty = False before any operation that requires
the form to move record, such as Requery, Filter, OrderBy, change
RecordSource, set Bookmark, Move..., Close, ...

3. Make sure the record is saved before opening another form or report that
reads the same data. This is particularly crucial if there are any Memo,
Hyperlink, or OLE Objects (i.e. BLOBs that use a pointer).

4. Avoid dirtying the record unnecessarily. It's surprising how often people
change a bound control to the same value, or even dirty the record in
Form_Current.

5. Make sure that Name AutoCorrect is off.

6. Explicitly close all recordsets you open.

7. Leave record locking on optimistic ("no locks"), and try without Record
Level Locking.

8. Old JET 4 version (but you say that's covered).

HTH

 

[Jeff Conrad]

Hi John,

In addition to the things Yoda mentioned here are a couple more things to check:

Are all the computers on Service Pack 1 for Access 2003? Some, but not all?

I doubt this would be the case, but it way be worth checking to see *exactly*
what version of Jet everyone is using.

Jet 4.0 SP8 will have a version number of 4.0.8015.0

However, a Windows security update (can't remember which one) updated
Jet past that number. If everyone has installed Service pack 8 AND all the
Windows Updates the Jet version should be 4.0.8618.0.
Might be worth checking.

Is the problem occurring on every computer for your client or just one (or several)?

 

[Albert D. Kallal]

The places I would look for is there any update code that runs and modifies
some data?

Any focus changes from sub-form to main form when this error occurs? (and
that code uses a sub-form recordset clone?).

Often, for some code that updates any data that *might* be in the form..then
I do a me.refresh

(or, better yet..the

if me.Dirty = true then
me.Dirty = false
endif

Given you first rate experience with this product, then no doubt this call
for help is not going to be a easy one!...

 

[Nick Coe \(UK\)]

Lo John,

Interesting. BE on a server?

I'd have a look at what's actually happening to the BE mdb
and ldb files on an OK machine and a problem one with
FileMon.
http://www.sysinternals.com/ntw2k/source/filemon.shtml

Be useful to eliminate any persistant file locks or renegade
handles. Probably worth watching the FE too.

Is indexing set on the problem machines?

Clutching at straws now.....

 

[John Vinson]

Could there be some task running on the network that does periodic
backups, and locks the file temporarily while it does so?

You mentioned Citrix. Is this happening only when the database is
accessed over a Citrix connection?

You say it happens mostly on this one form, and maybe occasionally on
another. Do the forms have anything in common? Is this form more
heavily used than other forms (so that the problem appears to be linked
to the form, but is really just happening periodically)?

Are there other forms bound to the same table as this form? Can you
verify that they don't show the problem? In other words, can we link
the problem to a particular table?

Are there any operations occurring in Timer events?

Very good suggestions all but... none resolve the issue. I'd already
ruled out some (most of the victims are NOT on Citrix, but I get the
problem over Citrix); no tasks that would do this.

The Form that does it consistantly is the LEAST used of the major
forms. There are three applications. It's an animal shelter database;
there are Calls (injured animals, dangerous animals, strays, etc);
Impounds (animals picked up to be sheltered); and Licensing. It's the
Licensing that's causing the problem. It now happens on EVERY attempt
to create a new record - and each time I enter data on a subform, I
get *six* of the error message popups. If I then leave the computer
alone, I get a new message every few seconds - scores of them if I go
off for a while.

See answers elsethread... and thanks.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Hi John

This can be hard to trace. Hopefully you'll get multiple responses, as I
believe there are multiple possible causes.

1. The most obvious one is a corruption. You've probably tried a decompile
already? A rebuild (import into a fresh database) is probably worth a shot,
except this particular form (and its subforms?) should probably be done via
SaveAsText/LoadFromText.

I hadn't done so but it didn't help when I did. I was able to
SaveToText but got an error on LoadFromText ("unable to generate
output file") so I just imported the form and its subforms.

The others are the usual suspects:

2. Explicit saves. Set Me.Dirty = False before any operation that requires
the form to move record, such as Requery, Filter, OrderBy, change
RecordSource, set Bookmark, Move..., Close, ...

I do that a LOT.

3. Make sure the record is saved before opening another form or report that
reads the same data. This is particularly crucial if there are any Memo,
Hyperlink, or OLE Objects (i.e. BLOBs that use a pointer).

There's a subform on the form viewing the same data, *read only* - but
removing this subform has no effect.

4. Avoid dirtying the record unnecessarily. It's surprising how often people
change a bound control to the same value, or even dirty the record in
Form_Current.

That I do need to check.

5. Make sure that Name AutoCorrect is off.

6. Explicitly close all recordsets you open.

I'll doublecheck - I habitually do so but some of this code has been
around a long while!

7. Leave record locking on optimistic ("no locks"), and try without Record
Level Locking.

Tried No Locks and Current Record Lock, haven't tried changing Record
Level Locking yet.

8. Old JET 4 version (but you say that's covered).

Well, it's running on a number of machines to which I do not have
access (I'm in Idaho, they're in Los Angeles).

Very good suggestions, and I'll be following them up... thank you!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Hi John,

In addition to the things Yoda mentioned here are a couple more things to check:

Are all the computers on Service Pack 1 for Access 2003? Some, but not all?

I doubt this would be the case, but it way be worth checking to see *exactly*
what version of Jet everyone is using.

Jet 4.0 SP8 will have a version number of 4.0.8015.0

I'll ask my client to doublecheck.

However, a Windows security update (can't remember which one) updated
Jet past that number. If everyone has installed Service pack 8 AND all the
Windows Updates the Jet version should be 4.0.8618.0.
Might be worth checking.

Is the problem occurring on every computer for your client or just one (or several)?

Several.

Thanks!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

The places I would look for is there any update code that runs and modifies
some data?

There is - but not in this particular part of the program, and I've
determined that it's not running.

Any focus changes from sub-form to main form when this error occurs? (and
that code uses a sub-form recordset clone?).

Yes... there is. In one case I have a "people" form with an "address"
subform; the clients sometimes know an address but not a name, and
want to just type the address. I set the parent form's LastName to
"Unknown" and pick up the PersonID. This is working OK - it's when I
enter the name *first* (and the code doesn't execute) that I'm having
the error!

Often, for some code that updates any data that *might* be in the form..then
I do a me.refresh

(or, better yet..the

if me.Dirty = true then
me.Dirty = false
endif

Yep... very familiar.

Given you first rate experience with this product, then no doubt this call
for help is not going to be a easy one!...

That's why I'm calling in reinforcements!

Thanks Albert. Some good ideas to investigate.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Lo John,

Interesting. BE on a server?

I'd have a look at what's actually happening to the BE mdb
and ldb files on an OK machine and a problem one with
FileMon.
http://www.sysinternals.com/ntw2k/source/filemon.shtml

Be useful to eliminate any persistant file locks or renegade
handles. Probably worth watching the FE too.

Is indexing set on the problem machines?

Clutching at straws now.....

Me too...

That's a good idea. I've had NO luck figuring out what it thinks is
being updated by what else. I'll see if these tools can tell me...

Thanks Nick!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Nick Coe \(UK\)]

Might be worth getting the client to send you one of the
system boxes that shows the fault so that you can work on it
directly.

--
Nick Coe (UK)
AccHelp v1.01 Application Help File Builder
http://www.alphacos.co.uk/
Download Free Demo Copy

 

[Allen Browne]

... I was able to SaveToText but got an error on LoadFromText
("unable to generate output file") so I just imported the form and its
subforms.

John, the fact that LoadFromText fails bothers me.

Peter Miller has been saying for 3 years now that importing a form can
simply import the corruption, and this error message is definately
associated with corruption (amongst other things).

 

[John Vinson]

Might be worth getting the client to send you one of the
system boxes that shows the fault so that you can work on it
directly.

That's not gonna happen... any more than the SEAACA animal control
facility will pay for me to fly down there! <g> I can work on it over
Citrix, and reproduce the fault there.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jeff Conrad]

Hi John,

Two things really jump out at me from comments elsethread:

Number 1:

I hadn't done so but it didn't help when I did. I was able to
SaveToText but got an error on LoadFromText ("unable to generate
output file") so I just imported the form and its subforms.

I agree with Allen; that definitely sounds like at least *some* form of corruption.
Not being able to LoadFromText is very, very fishy.
Can you try something on a backup copy?

1. Delete ALL the code behind this massive form (yes, all of it).
2. Compact the database.
3. Try the SaveAsText and LoadFromText now.
Are you able to LoadFromText now or do you still get an error?
If you can LoadFromText successfully then it is quite probable the VBA
code behind the form has become corrupt.

Number 2:

The Form that does it consistently is the LEAST used of the major
forms. There are three applications. It's an animal shelter database;
there are Calls (injured animals, dangerous animals, strays, etc);
Impounds (animals picked up to be sheltered); and Licensing. It's the
Licensing that's causing the problem. It now happens on EVERY attempt
to create a new record - and each time I enter data on a subform, I
get *six* of the error message popups. If I then leave the computer
alone, I get a new message every few seconds - scores of them if I go
off for a while.

Messages appearing from nowhere also sounds very weird!
Do you have any rogue Timer events running anywhere in this application?

 

[Dirk Goldgar]

The Form that does it consistantly is the LEAST used of the major
forms. There are three applications. It's an animal shelter database;
there are Calls (injured animals, dangerous animals, strays, etc);
Impounds (animals picked up to be sheltered); and Licensing. It's the
Licensing that's causing the problem. It now happens on EVERY attempt
to create a new record - and each time I enter data on a subform, I
get *six* of the error message popups. If I then leave the computer
alone, I get a new message every few seconds - scores of them if I go
off for a while.

Anything interesting in functions called by conditional formatting?
Could there be something going on in conditional formatting that is
forcing a record to be saved?

 

[John Vinson]

The places I would look for is there any update code that runs and modifies
some data?

Any focus changes from sub-form to main form when this error occurs? (and
that code uses a sub-form recordset clone?).

Yes... and when I took that code out the problem *seems* to have
resolved!!!

Two bits of code, don't know which did it:

- The users want to be able to enter an address (on a subform) without
entering a name (on the mainform). I had code to check to see if
Parent!ContactID was NULL; if so, it would insert "Unknown" into
Parent.LastName and then set Me!ContactID to (the new)
Parent!ContactID.

- There was another "alert" form, frmComplaintsAtAddress, which
displays all prior complaints referencing the entered address. I was
requerying that subform in the AfterUpdate event of the Address field.

- The third thing I removed was a Me.Dirty = False in the AfterUpdate
of the Address Number field (to keep the ComplaintsAtAddress as
current as possible).

Commenting out these three has - with *limited* testing - apparently
resolved the issue. More testing from users tomorrow!

Is the practice of using subform recordset clones and/or focus changes
deleterious in general?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[BillieGoat]

John, Did you ever find the solution to the problem? I am having the same
problem with a client database and have tried what seems like everything to
fix the issue.

Thanks, Billie Cornell