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 "Unrecogniz able 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.co m> wrote in message
news:1b******** *************** ***@posting.goo gle.com...

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 "Unrecogniz able 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.co m> wrote in message
news:1b******** *************** ***@posting.goo gle.com...

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 "Unrecogniz able 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****@teluspl anet.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.goo gle.com:

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****@teluspl anet.net> wrote in
news:ji******** *************** *********@4ax.c om:

Tony Toews <tt****@teluspl anet.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********@bwa y.net.invalid> wrote in message news:<Xn******* *************** ************@24 .168.128.86>...

ad****@vsnl.com (adirat) wrote in
news:1b******** *************** ***@posting.goo gle.com:

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