Records corrupt in Access 2000+ but not Access 97

[Owen Jenkins]

Following on from an earlier post...

I can reliably corrupt a record by doing the following ...

Open two separate but identical front ends on one PC each linking to the
same back end.
Edit a records in one front end and leave it unsaved.
Edit the same record in the other front end and save the change.
Save the change in the first front end - this pops up the Write Conflict
message to which I click Save.
About 10-20 seconds later, all fields in the record are replaced with
|||||||||||||||||||||||||||. Sometimes this causes corruption of the
file, sometimes not.

Obviously saving a change to a record in 2 front ends is not a good
thing to do, but should it corrupt the record? What is the point of the
Write Conflict box if one of the options corrupts the record.

Strange thing is, this happens when the front and back ends are in
Access 2000 format or when the FE is 2003 and BE 2000. It never happens
when the BE is A97 format, whether the FR is A97, 2000 or 2003.

Has anyone experienced this? Why should clicking Save in the Write
Conflict dialog reliably trash the record? Any why only 2000+?

Owen Jenkins

['69 Camaro]

Hi, Owen.

Has anyone experienced this?

Yes. But it's very, very rare.

Why should clicking Save in the Write Conflict dialog reliably trash the
record?

It shouldn't. But it can if one is on an unstable network, or if some of
the workstations have incompatible versions of the service packs for Jet
4.0. And it's more prone to corruption when bound Memo and OLE Object
columns are involved.

Make sure all of the workstations connecting to this database have Jet 4.0
SP-8 and MDAC 2.8 SP-1 installed. And avoid using bound Memo and OLE
Objects in your forms.

Any why only 2000+?

Access 2000 and newer all use Jet 4.0. Each version of Access came with a
different service pack level for Jet 4.0, some of which don't play nicely
with the other service pack levels in a mixed environment. In my
experience, Access 2000 and newer seem to have corruption slightly more
often with bound Memo and OLE Objects than Access 97 had, but your mileage
may vary.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Owen Jenkins" <[e-mail] in message
news:45**********************@news.optusnet.com.au ...

Following on from an earlier post...

I can reliably corrupt a record by doing the following ...

Open two separate but identical front ends on one PC each linking to the
same back end.
Edit a records in one front end and leave it unsaved.
Edit the same record in the other front end and save the change.
Save the change in the first front end - this pops up the Write Conflict
message to which I click Save.
About 10-20 seconds later, all fields in the record are replaced with
|||||||||||||||||||||||||||. Sometimes this causes corruption of the file,
sometimes not.

Obviously saving a change to a record in 2 front ends is not a good thing
to do, but should it corrupt the record? What is the point of the Write
Conflict box if one of the options corrupts the record.

Strange thing is, this happens when the front and back ends are in Access
2000 format or when the FE is 2003 and BE 2000. It never happens when the
BE is A97 format, whether the FR is A97, 2000 or 2003.

Has anyone experienced this? Why should clicking Save in the Write
Conflict dialog reliably trash the record? Any why only 2000+?

Owen Jenkins

[Allen Browne]

Owen, when you say you can "reliably corrupt a record", I assume you mean
that the corruption happens 100% of the time when you follow these steps.

Just out of interest, does it make any difference if you turn off
record-level locking under:
Tools | Options | Advanced
for both the front ends and the back end?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Owen Jenkins" <[e-mail] in message
news:45**********************@news.optusnet.com.au ...

Following on from an earlier post...

I can reliably corrupt a record by doing the following ...

Open two separate but identical front ends on one PC each linking to the
same back end.
Edit a records in one front end and leave it unsaved.
Edit the same record in the other front end and save the change.
Save the change in the first front end - this pops up the Write Conflict
message to which I click Save.
About 10-20 seconds later, all fields in the record are replaced with
|||||||||||||||||||||||||||. Sometimes this causes corruption of the file,
sometimes not.

Obviously saving a change to a record in 2 front ends is not a good thing
to do, but should it corrupt the record? What is the point of the Write
Conflict box if one of the options corrupts the record.

Strange thing is, this happens when the front and back ends are in Access
2000 format or when the FE is 2003 and BE 2000. It never happens when the
BE is A97 format, whether the FR is A97, 2000 or 2003.

Has anyone experienced this? Why should clicking Save in the Write
Conflict dialog reliably trash the record? Any why only 2000+?

Owen Jenkins

[Owen Jenkins]

Hi Gunny,

This happens on a single PC using two separate but identical front ends,
with the same back end. No network is involved. I have Jet 4 SP8 on Win
XP Home. (But I am trying to troubleshoot corruption on a network with
Jet 4 SP8. It's just that I found this problem as well.)

The form does have a bound memo field, but it is not obviously this
which is the problem. All fields are trashed. When I've had corruption
problems in the past, only the memo field has appeared corrupted.

Owen

>>Has anyone experienced this?

Yes. But it's very, very rare.

>>Why should clicking Save in the Write Conflict dialog reliably trash the
record?

It shouldn't. But it can if one is on an unstable network, or if some of
the workstations have incompatible versions of the service packs for Jet
4.0. And it's more prone to corruption when bound Memo and OLE Object
columns are involved.

Make sure all of the workstations connecting to this database have Jet 4.0
SP-8 and MDAC 2.8 SP-1 installed. And avoid using bound Memo and OLE
Objects in your forms.

>>Any why only 2000+?

Access 2000 and newer all use Jet 4.0. Each version of Access came with a
different service pack level for Jet 4.0, some of which don't play nicely
with the other service pack levels in a mixed environment. In my
experience, Access 2000 and newer seem to have corruption slightly more
often with bound Memo and OLE Objects than Access 97 had, but your mileage
may vary.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Owen Jenkins" <[e-mail] in message
news:45**********************@news.optusnet.com.a u...

>>Following on from an earlier post...

I can reliably corrupt a record by doing the following ...

Open two separate but identical front ends on one PC each linking to the
same back end.
Edit a records in one front end and leave it unsaved.
Edit the same record in the other front end and save the change.
Save the change in the first front end - this pops up the Write Conflict
message to which I click Save.
About 10-20 seconds later, all fields in the record are replaced with
|||||||||||||||||||||||||||. Sometimes this causes corruption of the file,
sometimes not.

Obviously saving a change to a record in 2 front ends is not a good thing
to do, but should it corrupt the record? What is the point of the Write
Conflict box if one of the options corrupts the record.

Strange thing is, this happens when the front and back ends are in Access
2000 format or when the FE is 2003 and BE 2000. It never happens when the
BE is A97 format, whether the FR is A97, 2000 or 2003.

Has anyone experienced this? Why should clicking Save in the Write
Conflict dialog reliably trash the record? Any why only 2000+?

Owen Jenkins

[Owen Jenkins]

Yes, 100% of the time.

I currently have no record locks. If I specify 'Edited record' in the
properties of the main data entry form, the corruption doesn't happen
because the 'Write Conflict' dialog never appears, just a beep
indicating that the record can't be edited because it is locked by the
other front end.

Owen

Owen, when you say you can "reliably corrupt a record", I assume you
mean that the corruption happens 100% of the time when you follow
these steps.

Just out of interest, does it make any difference if you turn off
record-level locking under:
Tools | Options | Advanced
for both the front ends and the back end?

[Allen Browne]

You're using optimistic locking, which is the default, and fine.

But Access 2000 introduced record-level locking (as distinct from page-level
locking) as a new option. I have seen some problems with that, and it is a
difference between A97 and A2000, so I'm curious if it makes a difference to
uncheck the box under:
Tools | Options | Advanced | Open db using record-level locking
in all FE and BE mdbs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Owen Jenkins" <[e-mail] in message
news:45**********************@news.optusnet.com.au ...

Yes, 100% of the time.

I currently have no record locks. If I specify 'Edited record' in the
properties of the main data entry form, the corruption doesn't happen
because the 'Write Conflict' dialog never appears, just a beep indicating
that the record can't be edited because it is locked by the other front
end.

Owen

>Owen, when you say you can "reliably corrupt a record", I assume you mean
that the corruption happens 100% of the time when you follow these steps.

Just out of interest, does it make any difference if you turn off
record-level locking under:
Tools | Options | Advanced
for both the front ends and the back end?

['69 Camaro]

Hi, Owen.

This happens on a single PC using two separate but identical front ends,
with the same back end. No network is involved. I have Jet 4 SP8 on Win XP
Home. (But I am trying to troubleshoot corruption on a network with Jet 4
SP8. It's just that I found this problem as well.)

If it's on a flaky network, the database file may have had some corruption
before you started your test, but not enough corruption to be noticeable.
Just because you isolated the database from your network during the test
doesn't rule out the possibility that the network had a hand in the
corruption. It's best to test with a "clean" database file: a new database
file with the objects from the original file imported into it one group at a
time, first the tables, then the queries, then the forms, et cetera, then
compile the code and compact the file.

The form does have a bound memo field, but it is not obviously this which
is the problem.

If removing the Memo field from the query that the form is bound to resulted
in not experiencing the record corruption even once in two and a half years
when the table had corrupted an average of once a month with the bound Memo
field, perhaps that would convince you that it may be the problem. It was
enough to convince me never to use bound Memo fields again.

All fields are trashed.

A corrupted pointer to the Memo field can trash the whole record, as well as
the Memo field.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Owen Jenkins" <[e-mail] in message
news:45**********************@news.optusnet.com.au ...

Hi Gunny,

This happens on a single PC using two separate but identical front ends,
with the same back end. No network is involved. I have Jet 4 SP8 on Win XP
Home. (But I am trying to troubleshoot corruption on a network with Jet 4
SP8. It's just that I found this problem as well.)

The form does have a bound memo field, but it is not obviously this which
is the problem. All fields are trashed. When I've had corruption problems
in the past, only the memo field has appeared corrupted.

Owen

>>>Has anyone experienced this?

Yes. But it's very, very rare.

>>>Why should clicking Save in the Write Conflict dialog reliably trash the
record?

It shouldn't. But it can if one is on an unstable network, or if some of
the workstations have incompatible versions of the service packs for Jet
4.0. And it's more prone to corruption when bound Memo and OLE Object
columns are involved.

Make sure all of the workstations connecting to this database have Jet 4.0
SP-8 and MDAC 2.8 SP-1 installed. And avoid using bound Memo and OLE
Objects in your forms.

>>>Any why only 2000+?

Access 2000 and newer all use Jet 4.0. Each version of Access came with a
different service pack level for Jet 4.0, some of which don't play nicely
with the other service pack levels in a mixed environment. In my
experience, Access 2000 and newer seem to have corruption slightly more
often with bound Memo and OLE Objects than Access 97 had, but your mileage
may vary.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Owen Jenkins" <[e-mail] in message
news:45**********************@news.optusnet.com. au...

>>>Following on from an earlier post...

I can reliably corrupt a record by doing the following ...

Open two separate but identical front ends on one PC each linking to the
same back end.
Edit a records in one front end and leave it unsaved.
Edit the same record in the other front end and save the change.
Save the change in the first front end - this pops up the Write Conflict
message to which I click Save.
About 10-20 seconds later, all fields in the record are replaced with
|||||||||||||||||||||||||||. Sometimes this causes corruption of the
file, sometimes not.

Obviously saving a change to a record in 2 front ends is not a good thing
to do, but should it corrupt the record? What is the point of the Write
Conflict box if one of the options corrupts the record.

Strange thing is, this happens when the front and back ends are in Access
2000 format or when the FE is 2003 and BE 2000. It never happens when the
BE is A97 format, whether the FR is A97, 2000 or 2003.

Has anyone experienced this? Why should clicking Save in the Write
Conflict dialog reliably trash the record? Any why only 2000+?

Owen Jenkins

[Owen Jenkins]

Hi Allen,

By default I have the A2000 record-locking checkbox checked. This is
because I develop in 97 with optimistic locking and convert to 2000.

Removing the tick stops the corruption. Should I be surprised at this?
I'm not sure what effect this has. My records are fairly large and
probably take up an entire 'page'. In table view, using pessimistic
record locking, I get only one record locked.

Is it sensible for me to untick this box for all my A2000/2003/2007
clients to avoid corruption? I can't find any specific cause, but can
reliably corrupt a record by clicking Save in the Write Conflict box. I
have some clients who have corruption issues, but they deny seeing the
Write Conflict message. Can I expect their corruption problems to be
fixed by unticking this box?

Or should I be looking at using pessimistic locking?

I'm going to try importing all objects into a new database, but I have
done this before in the recent past before these corruption issues
appeared. Also I'll do some experimenting with pessimistic locking.

In my testing, I can reliably corrupt a record if I click Save in the
Write Conflict dialog (2 FEs on 1PC with same BE). This is independent
of the field edited, the record edited, whether the same field is
changed in the 2 FEs, whether the FE is an mdb or mde, if the memo field
is removed from the form, if the memo field is removed from the
underlying table, and a few other things. The record won't corrupt if I
edit the table directly in the db window while the form is open; editing
in the form is required, so it is somehow related to the form (which
does have a lot of code behind it).

Thanks for you help.

Owen
Allen Browne wrote:

You're using optimistic locking, which is the default, and fine.

But Access 2000 introduced record-level locking (as distinct from
page-level locking) as a new option. I have seen some problems with
that, and it is a difference between A97 and A2000, so I'm curious if
it makes a difference to uncheck the box under:
Tools | Options | Advanced | Open db using record-level locking
in all FE and BE mdbs.

[Owen Jenkins]

Hi Gunny,

It doesn't appear to be a memo field problem as I have removed this and
still get the corruption. I have added some extra notes in my reply to
Allen Browne in this thread. I'd be please if you would look at that an
offer any further comments.

Owen

[Allen Browne]

Optimistic locking is fine.

Record-level locking does have some problems. If moving to page level
locking (still with optimistic) solves the problem, you should certainly
consider doing that for your databases.

Particularly with a JET 4 front end (A2000 or later) connected to a JET 3.5x
back end (A97), Access can get confused. JET 3 did not support record-level
locking, and the JET 4-to-3 thunking does not behave reliably IME if the
front end wants record-level locking and the back end doesn't handle it.

In any case, page-level locking is more stable and executes more
efficiently.

There may be other causes too. If this mdb started out in A97, it is
possible that some of the old A97 binary is still lurking in the code, and
contributing to the error. A decompile might solve that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Owen Jenkins" <[e-mail] in message
news:45**********************@news.optusnet.com.au ...

Hi Allen,

By default I have the A2000 record-locking checkbox checked. This is
because I develop in 97 with optimistic locking and convert to 2000.

Removing the tick stops the corruption. Should I be surprised at this? I'm
not sure what effect this has. My records are fairly large and probably
take up an entire 'page'. In table view, using pessimistic record locking,
I get only one record locked.

Is it sensible for me to untick this box for all my A2000/2003/2007
clients to avoid corruption? I can't find any specific cause, but can
reliably corrupt a record by clicking Save in the Write Conflict box. I
have some clients who have corruption issues, but they deny seeing the
Write Conflict message. Can I expect their corruption problems to be fixed
by unticking this box?

Or should I be looking at using pessimistic locking?

I'm going to try importing all objects into a new database, but I have
done this before in the recent past before these corruption issues
appeared. Also I'll do some experimenting with pessimistic locking.

In my testing, I can reliably corrupt a record if I click Save in the
Write Conflict dialog (2 FEs on 1PC with same BE). This is independent of
the field edited, the record edited, whether the same field is changed in
the 2 FEs, whether the FE is an mdb or mde, if the memo field is removed
from the form, if the memo field is removed from the underlying table, and
a few other things. The record won't corrupt if I edit the table directly
in the db window while the form is open; editing in the form is required,
so it is somehow related to the form (which does have a lot of code behind
it).

Thanks for you help.

Owen
Allen Browne wrote:

>You're using optimistic locking, which is the default, and fine.

But Access 2000 introduced record-level locking (as distinct from
page-level locking) as a new option. I have seen some problems with that,
and it is a difference between A97 and A2000, so I'm curious if it makes
a difference to uncheck the box under:
Tools | Options | Advanced | Open db using record-level locking
in all FE and BE mdbs.

[Owen Jenkins]

I've decompiled the db, and also created a new db and imported all
objects. Neither prevented my ability to corrupt the data.

I'll remove record-level locking since this certainly corrupts records
in my test situation. Without it, the records don't corrupt. (Thanks for
the valuable advice which I've since found other references to in the
NG). Hopefully that will also fix it for the clients although the
corruption circumstances seem to be different.

If this doesn't work, I might look into the 'Opportunistic Locking on
the Server' issue that I've read about in this NG. I don't have any
other leads at present.

On a related issue ... I have a bound memo field on the main form which
I suspect of being involved. I have followed David W. Fenton's
instructions in another post , viz ...

"Instead, use an unbound textbox for the memo field, and load the data
from the recordsource into the unbound textbox in the OnCurrent event of
the form, and then write to the field in the textbox's AfterUpdate event. "

To do this, I have a hidden text box bound to the memo field which I
read from and update to for the unbound editable field. Is this
sufficient, or could the hidden bound memo field be a problem too? How
else could I update the memo field in the recordsource without having it
as a field on the form?

Owen

[Allen Browne]

I don't personally use the approach David suggests, so I will let him
comment, or others who can speak from experience.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Owen Jenkins" <[e-mail] in message
news:45***********************@news.optusnet.com.a u...

I've decompiled the db, and also created a new db and imported all
objects. Neither prevented my ability to corrupt the data.

I'll remove record-level locking since this certainly corrupts records in
my test situation. Without it, the records don't corrupt. (Thanks for the
valuable advice which I've since found other references to in the NG).
Hopefully that will also fix it for the clients although the corruption
circumstances seem to be different.

If this doesn't work, I might look into the 'Opportunistic Locking on the
Server' issue that I've read about in this NG. I don't have any other
leads at present.

On a related issue ... I have a bound memo field on the main form which I
suspect of being involved. I have followed David W. Fenton's instructions
in another post , viz ...

"Instead, use an unbound textbox for the memo field, and load the data
from the recordsource into the unbound textbox in the OnCurrent event of
the form, and then write to the field in the textbox's AfterUpdate event.
"

To do this, I have a hidden text box bound to the memo field which I read
from and update to for the unbound editable field. Is this sufficient, or
could the hidden bound memo field be a problem too? How else could I
update the memo field in the recordsource without having it as a field on
the form?

Owen

[David W. Fenton]

Owen Jenkins <[e-mail] in
news:45***********************@news.optusnet.com.a u:

On a related issue ... I have a bound memo field on the main form
which I suspect of being involved. I have followed David W.
Fenton's instructions in another post , viz ...

"Instead, use an unbound textbox for the memo field, and load the
data from the recordsource into the unbound textbox in the
OnCurrent event of the form, and then write to the field in the
textbox's AfterUpdate event. "

To do this, I have a hidden text box bound to the memo field which
I read from and update to for the unbound editable field. Is this
sufficient, or could the hidden bound memo field be a problem too?
How else could I update the memo field in the recordsource without
having it as a field on the form?

You don't need a hidden text box. This won't get you anywhere in the
unbound editing issue -- you'll just be transferring the problem
from the unbound textbox to the hidden bound one.

If your recordsource has a field in it named MemoField and you have
an unbound textbox named txtMemoField, in the Afterupdate of the
textbox, you just do this:

Me!MemoField = Me!txtMemoField

Now, you may find that it is best also at this point to save the
record, but I don't always do that, and have not had problems with
it (though the reason I started doing it was actually different --
it was to avoid corrupting memo fields in replicated back ends when
a scheduled synch took place).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Owen Jenkins]

David W. Fenton wrote:

>You don't need a hidden text box.

<snip>
Thanks, very much appreciated!