frequent database corruption

[adirat]

I have read a lot on this subject on newsgroups and other access
related websites on data corruption, but since we are still not able
to isolate the problem – I am posting this detailed explanation of my
problem:

We have a 23 user environment with Windows advanced server and windows
2000 clients with access 2002 running on all clients in a FE/BE
format. The problem is our database gets corrupted almost 3-6 times on
a busy day (lot of data being processed).

Typically the following ways are in which the database gets corrupt:
1. We get the error message "Unrecognizable database format - do you
want to repair...." – this situation is most common and a compress and
repair on the database does the trick for us.
2. The second kind of error is when one record in any table becomes
corrupt – this usually happens with "#error" in each field of a record
or "####" in each field or sometimes "oriental and other garbled
characters" in place of the data in a record. Deleting this record is
sometimes possible, sometimes the rest of the records have to be
copied into a fresh table.

Also the problem faces could start on any client – hence we do not
think it is because of any one particular client.

Here is a brief history which can assist you in helping us dentify the
source of problem:

1. Earlier we had win95 with access97 with BE on Novel Netware. At
that time, for four years, we never faced this problem.
2. The we got new machines for all the nodes with win2000 with
access2000 (still using the netware server). We upgraded the same
access file from 97 to 2000. STILL NO PROBLEM.
3. Then we changed the server to win 2000 advanced server. That is
when we started having the corruption problems.
4. We further changed to access2002 – hoping this would remove the
problem – but it hasn't.

Further we have ensured that:
1. All the nodes have access 2002, SP4 for win2000 and same Jet engine
4.0
2. We have changed the server's network card, server's cable and
switch to ensure it is not because of network hardware devices – still
the problem persists.

Can anybody guide us how do we further isolate the problem and then
how do we resolve it.
Thanks in advance
adirat

[Larry Linson]

One thing you did not mention: have you separated the back-end (tables,
data, and relationships) from the front-end (queries, forms, reports,
macros, modules, and any local lookup tables) and given each user their own
copy of the front-end to be linked to the tables in the back end? Having
multiple users logged in to the same copy of the front-end or to the
monolithic database greatly increases the chances of corruption.

There's an introductory presentation on Access in a Multiuser Environment
that I did for my user group that you can download from
http://appdevissues.tripod.com. It will identify topics that I thought
worthwhile to discuss, and a bit more. The best collection of detailed
information and links on the subject of Access in the multiuser environment
is at MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm. You'll
likely find the answer to your problem in the references at Tony's site.

Larry Linson
Microsoft Access MVP

"adirat" <ad****@vsnl.com> wrote in message
news:1b**************************@posting.google.c om...

I have read a lot on this subject on newsgroups and other access
related websites on data corruption, but since we are still not able
to isolate the problem - I am posting this detailed explanation of my
problem:

We have a 23 user environment with Windows advanced server and windows
2000 clients with access 2002 running on all clients in a FE/BE
format. The problem is our database gets corrupted almost 3-6 times on
a busy day (lot of data being processed).

Typically the following ways are in which the database gets corrupt:
1. We get the error message "Unrecognizable database format - do you
want to repair...." - this situation is most common and a compress and
repair on the database does the trick for us.
2. The second kind of error is when one record in any table becomes
corrupt - this usually happens with "#error" in each field of a record
or "####" in each field or sometimes "oriental and other garbled
characters" in place of the data in a record. Deleting this record is
sometimes possible, sometimes the rest of the records have to be
copied into a fresh table.

Also the problem faces could start on any client - hence we do not
think it is because of any one particular client.

Here is a brief history which can assist you in helping us dentify the
source of problem:

1. Earlier we had win95 with access97 with BE on Novel Netware. At
that time, for four years, we never faced this problem.
2. The we got new machines for all the nodes with win2000 with
access2000 (still using the netware server). We upgraded the same
access file from 97 to 2000. STILL NO PROBLEM.
3. Then we changed the server to win 2000 advanced server. That is
when we started having the corruption problems.
4. We further changed to access2002 - hoping this would remove the
problem - but it hasn't.

Further we have ensured that:
1. All the nodes have access 2002, SP4 for win2000 and same Jet engine
4.0
2. We have changed the server's network card, server's cable and
switch to ensure it is not because of network hardware devices - still
the problem persists.

Can anybody guide us how do we further isolate the problem and then
how do we resolve it.
Thanks in advance
adirat

[John A.]

Have you tried http://support.microsoft.com/?kbid=272582 ??

It worked for us in a similar, but not exactly the same, environment.

John
"adirat" <ad****@vsnl.com> wrote in message
news:1b**************************@posting.google.c om...

I have read a lot on this subject on newsgroups and other access
related websites on data corruption, but since we are still not able
to isolate the problem - I am posting this detailed explanation of my
problem:

We have a 23 user environment with Windows advanced server and windows
2000 clients with access 2002 running on all clients in a FE/BE
format. The problem is our database gets corrupted almost 3-6 times on
a busy day (lot of data being processed).

Typically the following ways are in which the database gets corrupt:
1. We get the error message "Unrecognizable database format - do you
want to repair...." - this situation is most common and a compress and
repair on the database does the trick for us.
2. The second kind of error is when one record in any table becomes
corrupt - this usually happens with "#error" in each field of a record
or "####" in each field or sometimes "oriental and other garbled
characters" in place of the data in a record. Deleting this record is
sometimes possible, sometimes the rest of the records have to be
copied into a fresh table.

Also the problem faces could start on any client - hence we do not
think it is because of any one particular client.

Here is a brief history which can assist you in helping us dentify the
source of problem:

1. Earlier we had win95 with access97 with BE on Novel Netware. At
that time, for four years, we never faced this problem.
2. The we got new machines for all the nodes with win2000 with
access2000 (still using the netware server). We upgraded the same
access file from 97 to 2000. STILL NO PROBLEM.
3. Then we changed the server to win 2000 advanced server. That is
when we started having the corruption problems.
4. We further changed to access2002 - hoping this would remove the
problem - but it hasn't.

Further we have ensured that:
1. All the nodes have access 2002, SP4 for win2000 and same Jet engine
4.0
2. We have changed the server's network card, server's cable and
switch to ensure it is not because of network hardware devices - still
the problem persists.

Can anybody guide us how do we further isolate the problem and then
how do we resolve it.
Thanks in advance
adirat

[Tony Toews]

ad****@vsnl.com (adirat) wrote:

3. Then we changed the server to win 2000 advanced server. That is
when we started having the corruption problems.

Given this history I strongly suspect the problem is the OpLocks setting on the
server. Corrupt Microsoft Access MDB Causes - OpLocks
http://www.granite.ab.ca/access/corr...sesoplocks.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

Tony Toews <tt****@telusplanet.net> wrote:

3. Then we changed the server to win 2000 advanced server. That is
when we started having the corruption problems.

Given this history I strongly suspect the problem is the OpLocks setting on the
server. Corrupt Microsoft Access MDB Causes - OpLocks
http://www.granite.ab.ca/access/corr...sesoplocks.htm

Or, as Larry suggests, you haven't split the MDB into a FE/BE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[David W. Fenton]

ad****@vsnl.com (adirat) wrote in
news:1b**************************@posting.google.c om:

Further we have ensured that:
1. All the nodes have access 2002, SP4 for win2000 and same Jet
engine 4.0
Which version of Jet 4.0? SP6 or SP8 are the only usable versions of
Jet 4.0.
2. We have changed the server's network card, server's cable and
switch to ensure it is not because of network hardware devices -
still the problem persists.

Have you investigated the oplocks settings on the server?

I wonder if you are using memo fields in your data tables, and if
those are the tables that exhibit the corrupted records?

If so, one way to make your app more robust is to never edit the
memo with a bound field. 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.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Tony Toews <tt****@telusplanet.net> wrote in
news:ji********************************@4ax.com:

Tony Toews <tt****@telusplanet.net> wrote:

3. Then we changed the server to win 2000 advanced server. That
is when we started having the corruption problems.

Given this history I strongly suspect the problem is the OpLocks
setting on the server. Corrupt Microsoft Access MDB Causes -
OpLocks
http://www.granite.ab.ca/access/corr...sesoplocks.htm

Or, as Larry suggests, you haven't split the MDB into a FE/BE.

Or both.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[adirat]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message news:<Xn**********************************@24.168. 128.86>...

ad****@vsnl.com (adirat) wrote in
news:1b**************************@posting.google.c om:

Further we have ensured that:
1. All the nodes have access 2002, SP4 for win2000 and same Jet
engine 4.0

Which version of Jet 4.0? SP6 or SP8 are the only usable versions of
Jet 4.0.

2. We have changed the server's network card, server's cable and
switch to ensure it is not because of network hardware devices -
still the problem persists.

Have you investigated the oplocks settings on the server?

I wonder if you are using memo fields in your data tables, and if
those are the tables that exhibit the corrupted records?

If so, one way to make your app more robust is to never edit the
memo with a bound field. 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.

version of Jet 4.0 is sp8 in all machines
no memo fields at all.

oplocks NOW also set to disable (only on server - do i need to do on
client machines too?)
will watch and post if oplocks has solved the problem

regards
adirat

[Tony Toews]

ad****@vsnl.com (adirat) wrote:

oplocks NOW also set to disable (only on server - do i need to do on
client machines too?)
Server only.
will watch and post if oplocks has solved the problem

Please do.

Thanks, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[adirat]

> >will watch and post if oplocks has solved the problem

Here is the update:
29th june we did the oplocks disable.
Till 8th july no problem - we were about to distribute sweets in the
office when

1. On 9th one table in the BE database had two problems
a) One record had "#ERROR" in every field
b) Another record had oriental characters (like japanese or chinese)
in every field and the id field (which usually has a four digit
number) had a minus 9 digit number.
c) We lost one record.
Also to be able to use the table further, we had to copy all the
uncorrupted records (around 3578) to a fresh table with the same
structure.

Any ideas what could have caused all this and HOW TO PREVENT THIS.

2. Today, 12th July we once again got the unrecognised database - need
to repair error message - and had to run a compact and repair.
Any ideas where we go from here.

thanks ina advance
adirat

[Michael \(michka\) Kaplan [MS]]

The hard part about corruption is that it is not a bug, it is the result of
a bug (almost universally to do with hardware/NIC issues). The result is not
so great from a "debugging the problem" standpoint except in the case where
the machines where it happens show patterns that can point to the offenders.

Defensive Access apps work to minimize the ability for such issues to cause
problems; disabling oplocks is one such workitem, but there are many
others -- such as never binding to memo or bound object fields in
forms/reports, for example.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies
Windows International Division

This posting is provided "AS IS" with
no warranties, and confers no rights.

"adirat" <ad****@vsnl.com> wrote in message
news:1b**************************@posting.google.c om...

will watch and post if oplocks has solved the problem

Here is the update:
29th june we did the oplocks disable.
Till 8th july no problem - we were about to distribute sweets in the
office when

1. On 9th one table in the BE database had two problems
a) One record had "#ERROR" in every field
b) Another record had oriental characters (like japanese or chinese)
in every field and the id field (which usually has a four digit
number) had a minus 9 digit number.
c) We lost one record.
Also to be able to use the table further, we had to copy all the
uncorrupted records (around 3578) to a fresh table with the same
structure.

Any ideas what could have caused all this and HOW TO PREVENT THIS.

2. Today, 12th July we once again got the unrecognised database - need
to repair error message - and had to run a compact and repair.
Any ideas where we go from here.

thanks ina advance
adirat

[David W. Fenton]

ad****@vsnl.com (adirat) wrote in
news:1b**************************@posting.google.c om:

1. On 9th one table in the BE database had two problems
a) One record had "#ERROR" in every field
b) Another record had oriental characters (like japanese or
chinese) in every field and the id field (which usually has a four
digit number) had a minus 9 digit number.
c) We lost one record.
Also to be able to use the table further, we had to copy all the
uncorrupted records (around 3578) to a fresh table with the same
structure.

Any ideas what could have caused all this and HOW TO PREVENT THIS.

Are there memo fields in the table that got corrupted?

If so, did you take the advice given here to not edit those in bound
controls?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[adirat]

as mentioned in earlier mail
no memo field
no bound objects fields
nowhere in the whole app.

what i am asking for is what next i can do - what else i can look for
-
1.how does one trace a NIC issue (assuming that could be one of the
problems) - since the problem do not seem to originate from one node)
2. Anything i can change in the access database (in the FE or in BE)

any tips most welcome....

adirat

[David W. Fenton]

ad****@vsnl.com (adirat) wrote in
news:1b**************************@posting.google.c om:

as mentioned in earlier mail
no memo field
no bound objects fields
nowhere in the whole app.

what i am asking for is what next i can do - what else i can look
for -
1.how does one trace a NIC issue (assuming that could be one of
the problems) - since the problem do not seem to originate from
one node)
Probably you need to start digging into the contents of the LDB
file. I don't know how much information the ADO UserRoster function
returns, but the LDBVIEW utility provides you an interface to all
the information available in an LDB (through a DLL):

http://support.microsoft.com/default...b;EN-US;176670
2. Anything i can change in the access database (in the FE or in
BE)

First off, I assume that users are *not* sharing the front end?

If everything is unbound, then it's very odd that you're having
problems.

I forget your original situation. Did this all start happening with
the upgrade to a new version of Access, or was it an upgraded
server?

The other thing you really *must* be certain about is that every
single workstation have the latest service pack for Access and Jet
4.0 service pack 6 or later (though not SP7, which was very buggy,
and was quickly replaced with 8). Even one single workstation still
having a substandard installation can cause problems -- I've seen
it.

I now have all my A2K apps record on user startup what version of
the MSACCESS.EXE file they are using and what version of
MSJET40.DLL. This makes it very easy to tell if any workstations
have the wrong versions, and makes it very easy to identify when a
workstation reverts to older versions (which can happen very easily,
with, say, a repair of Office, or the rebuilding of a workstation or
its replacement with a new PC).

I'd say the problem is unlikely to be a NIC *if* A97 worked reliably
on the same equipment (I may be remembering wrong?). I think it's
much more likely to be a software configuration issue on the server,
especially given that changing the OPLOCKS setting improved the
situation.

Is this server running MS Exchange, by chance? I had a client once
where an Exchange hot fix started causing corruptions. As Exchange
wasn't even in use on the server (and shouldn't have been running
and should not have been patched), we backed out the hot fix and the
corruption never recurred. That was a very long time ago (early
1999) with very different versions of software (and replication
involved, to boot), but it taught me a very important lesson:
corruption can be caused by changes to the software configuration of
a server, something I'd never really considered before that point.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

ad****@vsnl.com (adirat) wrote:

>will watch and post if oplocks has solved the problem

Here is the update:
29th june we did the oplocks disable.
Till 8th july no problem - we were about to distribute sweets in the
office when

1. On 9th one table in the BE database had two problems
a) One record had "#ERROR" in every field
b) Another record had oriental characters

This one is a puzzler. One place I personally visited was having
similar problems which turned out to be an old house which was
converted into an office which had severely overloaded circuits and
very flaky power. The PCs and server were on UPSs but the hub
wasn't. Once they put the hub on a UPS all was well.

The point here though is that you'd think the network protocols would
somehow tell the other computer to retransmit clean packets or
completely fail. But that's not what happened. Corrupted packets got
through.
2. Today, 12th July we once again got the unrecognised database - need
to repair error message - and had to run a compact and repair.

Next time it happens try the steps at Determining the workstation
which caused the Microsoft Access MDB corruption
http://www.granite.ab.ca/access/corr...orkstation.htm to see if
you can narrow it down to a specific workstation.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[david epsom dot com dot au]

> 1.how does one trace a NIC issue (assuming that could be one of the

I don't even have the hardware to trace NIC issues, I would hire an
expensive network guy who did. But presumably Win Server can at least
count bad packets.

what i am asking for is what next i can do - what else i can look for
Also, remove extra network protocols, and turn off Anti-Virus software

(david)
"adirat" <ad****@vsnl.com> wrote in message
news:1b**************************@posting.google.c om... as mentioned in earlier mail
no memo field
no bound objects fields
nowhere in the whole app.

what i am asking for is what next i can do - what else i can look for
-
1.how does one trace a NIC issue (assuming that could be one of the
problems) - since the problem do not seem to originate from one node)
2. Anything i can change in the access database (in the FE or in BE)

any tips most welcome....

adirat

[Tony Toews]

"David W. Fenton" <dX********@bway.net.invalid> wrote:

the LDBVIEW utility provides you an interface to all
the information available in an LDB (through a DLL):

http://support.microsoft.com/default...b;EN-US;176670
Trouble is LDBView gives you misleading results for Jet 4.0 MDBs. Everyone who logs
out is marked suspect even though they aren't.
I now have all my A2K apps record on user startup what version of
the MSACCESS.EXE file they are using and what version of
MSJET40.DLL.
For the original poster, not for you David:

What I've done is use the various API calls available and am checking the version
number and date/time of a crucial dll, msjetxx.dll, to ensure it matches what I have
on my system. See the Verify Appropriate Jet Service Pack is installed page at my
website for more details including sample code:
www.granite.ab.ca\access\verifyjetsp.htm
Is this server running MS Exchange, by chance? I had a client once
where an Exchange hot fix started causing corruptions. As Exchange
wasn't even in use on the server (and shouldn't have been running
and should not have been patched), we backed out the hot fix and the
corruption never recurred. That was a very long time ago (early
1999) with very different versions of software (and replication
involved, to boot), but it taught me a very important lesson:
corruption can be caused by changes to the software configuration of
a server, something I'd never really considered before that point.

Ah, very interesting. Thanks.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Mitchell Thomas]

ad****@vsnl.com (adirat) wrote in message news:<1b**************************@posting.google. com>...

will watch and post if oplocks has solved the problem

Here is the update:
29th june we did the oplocks disable.
Till 8th july no problem - we were about to distribute sweets in the
office when

1. On 9th one table in the BE database had two problems
a) One record had "#ERROR" in every field
b) Another record had oriental characters (like japanese or chinese)
in every field and the id field (which usually has a four digit
number) had a minus 9 digit number.
c) We lost one record.
Also to be able to use the table further, we had to copy all the
uncorrupted records (around 3578) to a fresh table with the same
structure.

Any ideas what could have caused all this and HOW TO PREVENT THIS.

2. Today, 12th July we once again got the unrecognised database - need
to repair error message - and had to run a compact and repair.
Any ideas where we go from here.

thanks ina advance
adirat

We experienced the same problem with data being corrupted into chinese
characters, i spent a couple of months trying to figure out this
problem even worked with Microsoft directly, could not find the cause
of this problem, never had this problem with Access97, only
Access2002. We finally just converted our BE to Oracle, since then
no more corruption with chinese characters, still using Access 2002 as
FE. I think there is a bug in Access 2002 that is partly responsible
for causing this corruption.

[Joe Kissel]

Hello, I have been working with an application that uses the Jet engine
(various version) for almost 10 year now. I have expirienced the same
problems when moving to Jet 4.0. The problem, in our case, turned out
to be a mixture of using the database in row-level record locking mode
and executing stored update queries that operate on the same records
that were earlier processed by VBA code within the same transaction.
This appears to be a bug in Jet 4.0 when in row-level record locking
mode. For example, the following VB code can, sometimes, cause a
corruption depending on record distribution in the database. In
row-level locking mode this code generates a 3218 error, other times it
works, and other times it corrupts a record in the manner that you
described.

Assume that DB is an open database object in DAO (using ADO with the JET
provider generates the same results)...

dim RS as recordset

set RS = db.openrecordset("Select * from Cleints", dbopendynaset)
do while not rs.eof
rs.edit
if rs("lasttransactionamount") = 0 then rs("closeddate") = now
rs.update
rs.movenext
loop

rs.close

DB.Execute "Update Clients Set CleintIsActive = True where closeddate <=
#" & Now & "#", dbfailonerror

...when the execute statment is run an error 3218 can occur and a record
can become corrupt. The only fix is avoid such constructs of mixing SQL
executes with code that updates record individually. This is also true
of mixing bound forms with command buttons that operate on the data by
calling an update query in Access. Hopefully, someday, Microsoft, will
address this problem with row-level locking.

I hope this helps...Joe.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Albert D. Kallal]

There is several 100 million (that is right 100 million) estimated users of
ms-access around the world.

I would think that out of several 100 million users, if your example were a
simple re-producible means to corrupt data, then I sure this behavior would
have been long ago documented here.

Hello, I have been working with an application that uses the Jet engine
(various version) for almost 10 year now. I have expirienced the same
problems when moving to Jet 4.0. The problem, in our case, turned out
to be a mixture of using the database in row-level record locking mode
and executing stored update queries that operate on the same records
that were earlier processed by VBA code within the same transaction.
First of all, the above don't make sense, since row level locking was
not available in previous versions of JET. Only when JET 4 came
out was row locking available. So, in the last 10 years, you could
not have had a row locking problem, since row locking only
came on the scene with JET 4
For example, the following VB code can, sometimes, cause a
corruption depending on record distribution in the database.
You need to define what you mean by corruption. I am aware of NO code that
exists when using JET that allows you to corrupt the data. This is also true
of mixing bound forms with command buttons that operate on the data by
calling an update query in Access.
No, the above is not a problem. Of course, if edits have been made to the
bound record to this form, then pending updates do exist. This means of
course that if code modifies the data, then the user will get a message
about the data having been modified by someone else (in this case..that some
one else is in fact YOUR code). The simple solution in this case is to force
a disk write BEFORE you run any update code. In fact, it is a good habit to
force a disk write when you launch another form from the current form.. And,
if you often launch a report to print the current record you are looking at,
then once again, in fact you HAVE to force a disk write, else the report
will NOT show given changes to the record on the current form. So, to force
that disk write...you can go:

me.Refresh
Hopefully, someday, Microsoft, will
address this problem with row-level locking.

Hopefully, some day, people will take blame for their own shortcomings
instead of trying to blame everyone else or the world for their problems.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn