Corruption - how to MAKE it happen?

[Doug]

I am in a friendly debate with some co-workers... and my boss.

We use Access 2003 for the frontend (on workstations) as well as for
the backend (on a Dell PowerEdge running Windows 2000 server, SP4).
We have approx. 20 users *logged in* at any one time (over 300
userid's exist), but we have never run metrics to see how many on
average are accessing the database concurrently.

About once every few weeks, the database mysteriously becomes
corrupted.

One thing that seems pretty reliable is if my co-worker or I are in
design mode of a table and manually edit a record while someone else
is editing the same record via the frontend.

In addition, I thought that if I abnormally exited the program (CTRL-
ALT-DEL and kill the process while the program is loading, etc.), that
there would be a very good chance of corrupting the DB. Especially
(only?) if you killed it while it was accessing a record, before it
closed a recordset, etc.

Can anyone shed any light here? I have found several references
throughout this newsgroup, and even a Corruption FAQ:

http://www.granite.ab.ca/access/corruptmdbs.htm

but I would really like to know if abnormal termination would/should
do it. Random testing so far has been unable to reproduce the desired
corruption. I even tried running the code, setting a breakpoint in
the middle of a record operation, then CTRL-ALT-DELing the process.
No corruption.

Help?

Thanks.

[Larry Linson]

Having more than a single user logged in to a montolithic Access database or
a front-end significantly increases the chances of corruption, but does not
guarantee it -- based on your description, however, that does not seem to be
your case.

Because it is a file-server, when used with a Jet back-end, all the
processing is done on the user's machine, thus "abnormal termination" at
particular points in processing may leave internal pointers improperly set
because an operation was not completed. As Microsoft does not publish
details of the internals, and when, where, and why pointers are set/reset,
it's difficult to be more specific. That is, "ungraceful " or "abnormal"
termination is often involved, but it does not **always** cause corruption.

Because of the way Access-Jet operates, see above, it is far more sensitive
to dropped network connections, even if they are intermittent and brief,
than most other software.

And, well, if I had a list of situations that were guaranteed to cause
corruption, I'm not sure I'd think it appropriate to publish those in a
public newsgroup. I'd probably share them with Microsoft, in hopes that a
fix or workaround could be found, so they would soon not apply.

Larry Linson
Microsoft Access MVP

"Doug" <sp*******@gmai l.comwrote in message
news:11******** **************@ y80g2000hsf.goo glegroups.com.. .

>I am in a friendly debate with some co-workers... and my boss.

We use Access 2003 for the frontend (on workstations) as well as for
the backend (on a Dell PowerEdge running Windows 2000 server, SP4).
We have approx. 20 users *logged in* at any one time (over 300
userid's exist), but we have never run metrics to see how many on
average are accessing the database concurrently.

About once every few weeks, the database mysteriously becomes
corrupted.

One thing that seems pretty reliable is if my co-worker or I are in
design mode of a table and manually edit a record while someone else
is editing the same record via the frontend.

In addition, I thought that if I abnormally exited the program (CTRL-
ALT-DEL and kill the process while the program is loading, etc.), that
there would be a very good chance of corrupting the DB. Especially
(only?) if you killed it while it was accessing a record, before it
closed a recordset, etc.

Can anyone shed any light here? I have found several references
throughout this newsgroup, and even a Corruption FAQ:

http://www.granite.ab.ca/access/corruptmdbs.htm

but I would really like to know if abnormal termination would/should
do it. Random testing so far has been unable to reproduce the desired
corruption. I even tried running the code, setting a breakpoint in
the middle of a record operation, then CTRL-ALT-DELing the process.
No corruption.

Help?

Thanks.

[Tony Toews [MVP]]

"Doug" <sp*******@gmai l.comwrote:

>One thing that seems pretty reliable is if my co-worker or I are in
design mode of a table and manually edit a record while someone else
is editing the same record via the frontend.

I'm not quite clear on how you can be in the design mode of a table and edit a
record. Do you mean datasheet mode?

>In addition, I thought that if I abnormally exited the program (CTRL-
ALT-DEL and kill the process while the program is loading, etc.), that
there would be a very good chance of corrupting the DB. Especially
(only?) if you killed it while it was accessing a record, before it
closed a recordset, etc.

A client has had at least five power failures with no UPSs on the client systems with
about 15 to 20 users while I was on site. There was no corruption of the database.
I was rather surprised.

>but I would really like to know if abnormal termination would/should
do it. Random testing so far has been unable to reproduce the desired
corruption. I even tried running the code, setting a breakpoint in
the middle of a record operation, then CTRL-ALT-DELing the process.
No corruption.

What might work is running a SQL query updating a few thousand records and powering
off the system abruptly.

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

[(PeteCresswell)]

Per Doug:

>About once every few weeks, the database mysteriously becomes
corrupted.

This is one reason why I do my best to avoid using a JET back end for a
mission-critical application.

I've done it... because the user's insisted on it... but in one case I've had
big time regrets about playing along. In that case, the cause of the
corruptions seemed to be something about the file server the back end was on. We
moved the .MDB file to another server and the problem went away.

What made it so bad was that the organization was very large, with extremely
stringent change control procedures in place and getting somebody who had
permission to move the file took literally months - during which the users lives
became very difficult due to the recurring corruptions.

User wound up paying 3 mil for IT to rewrite the app (and, at IT's insistence,
add web access) - only to have to scrap the rewrite and go to another outside
vendor to do it all over again.

If they'd just bought into an SQL Server back end in the first place, I have no
doubt they would still be running that application.
--
PeteCresswell

[The Frog]

Is it true then that there is absolutely no way to control the
corruption issues that are relevant to an MDB babckend? That is to
say, that we are unable to detect and correct for these issues via
code, except perhaps by quite literally have a front end re-create the
backend and import the last / most recent set of data backups?

I was also looking into the possible elimination of corruption
conditions, but have found this an extremely difficult task to pin
anything down except certain environmental factors (with the help of
many postings here in this newsgroup). I am curious if anyone has had
any success with "correcting " corruptions from a front end without
having to do the whole make-a-new approach. Any takers?

The Frog

[Doug]

>From: "Tony Toews [MVP]" <tto...@teluspl anet.net>

>
I'm not quite clear on how you can be in the design mode of a table and edit a
record. Do you mean datasheet mode?

Yes, I misspoke. I should have said datasheet mode.

On Mar 23, 8:36 am, "The Frog" <andrew.hogend. [e-mail] wrote:

Is it true then that there is absolutely no way to control the
corruption issues that are relevant to an MDB babckend? ...I am curious if anyone has had
any success with "correcting " corruptions from a front end without
having to do the whole make-a-new approach. Any takers?

Hey Frog:

I found several references (some from MS) stating that:

"(3) Executing the 'Compact and Repair' options inside an Access
database
regularly is recommended as a procedure to prevent corruption. This
should
certainly be executed every week for a database that is being used
daily. "

ACC2000: How to Troubleshoot/Repair a Damaged Jet 4.0 Database
(Q209137)
http://support.microsoft.com/default...;EN-US;q209137

That isn't exactly "using code", but it's the only thing I've found
that even begins to address this.

Other excerpts from that KB article:

"In most cases, database corruption is *not* the result of poor
programming
techniques or sloppy design." (emphasis added)

(4) Avoid mixing operating systems in a multi-user environment. For
example, Windows 98 with Windows 2000.

(6) When designing the database tables it is prudent to be sensible
about
the number of fields per table. As a general indicator, most tables
should
contain no more than 25 fields.

(7) Consider disabling opportunistic locking on the file server it is
Windows NT or Windows 2000 based.

[David W. Fenton]

"Doug" <sp*******@gmai l.comwrote in
news:11******** **************@ y80g2000hsf.goo glegroups.com:

In addition, I thought that if I abnormally exited the program
(CTRL- ALT-DEL and kill the process while the program is loading,
etc.), that there would be a very good chance of corrupting the
DB. Especially (only?) if you killed it while it was accessing a
record, before it closed a recordset, etc.

If it's in edit mode it's much more likely. Have two users (or two
instances of Access on the same machine) edit a memo field
simultaneously, and kill one or both instances of Access and you've
got a pretty good chance of corrupting the records (though not
necessarily the database). You don't even have to kill Access to
make it happen with the memo fields.

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

[David W. Fenton]

"Tony Toews [MVP]" <tt****@teluspl anet.netwrote in
news:s8******** *************** *********@4ax.c om:

What might work is running a SQL query updating a few thousand
records and powering off the system abruptly.

Or running a massive append and killing the process in the middle of
the append.

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

[David W. Fenton]

"Doug" <sp*******@gmai l.comwrote in
news:11******** **************@ n59g2000hsh.goo glegroups.com:

I found several references (some from MS) stating that:

In my experience, since A2K was released, the biggest cause of
corruption is bad versions of Jet and Access. I've had only one
hardware problem (which didn't result in corruption) with Access
since about 2000, and that was with an A97 application.

But with A2K and higher, clients have had more corruption, maybe 1
client a year. One of my clients using A2K had problems when one
workstation got reverted to the release version of A2K -- that's
almost guaranteed to cause problems.

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

[pks]

On Mar 23, 7:36 am, "The Frog" <andrew.hogend. [e-mail] wrote:

.. . .

>
I was also looking into the possible elimination of corruption
conditions, but have found this an extremely difficult task to pin
anything down except certain environmental factors (with the help of
many postings here in this newsgroup). I am curious if anyone has had
any success with "correcting " corruptions from a front end without
having to do the whole make-a-new approach. Any takers?

Frog,

I had a client with about 40-50 users (maybe 35 concurrent) that had
an ongoing problem with corruption. After trying everything that I
could come up with (and a big THANK YOU to this NG), I was left with
intermittent network issues. We then set them up with a Terminal
Server with the BE on the TS and a copy of the FE in each user
profile's desktop, with all users accessing the DB via TS even though
they were in the same office. With the DB network traffic eliminated,
the corruption issue stopped. Even with 2 users accessing the DB
across the network instead of via TS (one occasional and one heavy
user), we had no more corruption.

It worked great until about 6 months ago when the corruption problem
returned, although not as severe. Come to find out that they added a
second TS for users on another floor, and that TS was connecting to
the DB over the network. We haven't gotten anywhere yet with moving
those users to the original server, but with running a compact/repair
2-3 times per week, the corruption is minimal although it still
happens.

FWIW.