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.

[(PeteCresswell)]

Per David W. Fenton:

>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.

How about opening up the .MDB in a text editor like SPF and overtyping and/or
deleting a few characters?
--
PeteCresswell

[(PeteCresswell)]

Per The Frog:

but have found this an extremely difficult task to pin
anything down except certain environmental factors

The only one that I was able to find/fix was a bad NIC in one of the user's
machines.
My fallback position in a "mystery" situation is a .BAT file that runs every
night and keeps many, many (like a hundred...) copies of the back end.

The main rationale is that a DB can come up corrupted on day 1 and users can
open it on days 2, 3, 4, 5 and so-forth; find it's corrupted; mutter a few harsh
words; and jut get on with other work without telling anybody about it.

On each one of those days, another bad copy goes into the LAN backup scheme and
eventually the last good copy falls off the end of the process.

There are also scenarios where a .MDB becomes partially corrupted. It still
works, but it gets goofey under some circumstances and/or various tables aren't
right anymore but still function - and the DB gives no programmatic indication
of it's state when opened. In that scenario, a DB can go even longer without
being identified as corrupt.

--
PeteCresswell

[David W. Fenton]

"(PeteCresswell )" <[e-mail] te in
news:rb******** *************** *********@4ax.c om:

Per David W. Fenton:

>>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.

How about opening up the .MDB in a text editor like SPF and
overtyping and/or deleting a few characters?

Or opening it in Word.

But that's not realistic corruption -- it's going to be random
corruption. The other methods produce corruption that is made in the
process of Access attempting to maintain internal data structures.
And much of that will be recoverable. One byte out of place at the
top of an MDB file could perhaps wreck everything.

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

[David W. Fenton]

"(PeteCresswell )" <[e-mail] te in
news:ge******** *************** *********@4ax.c om:

My fallback position in a "mystery" situation is a .BAT file that
runs every night and keeps many, many (like a hundred...) copies
of the back end.

The one client of mine that's had corruption in the last year (and
who also has a very large data file with 3 tables each having 100s
of thousands of records) runs the FMS Agent overnight. It archives
into a zip file, and keeps as many back copies as you choose, and
then compacts the MDB. It was very helpful to me in troubleshooting
one of my *owh* errors earlier this week, as I was able to go back
and look at data before I'd made the fatal change and after, and
thus track down the cause (a big finger pointing back at myself!).

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

[Doug]

Another part of the problem is that the application is used 24x7x365,
so to compact/repair, I need to kick users out of the system.

It's just that I *really* have this feeling that the corruption is
caused by people starting up the program (which takes a LONG time),
then deciding "You know what? I don't have time for this..." and they
give it the "3-Fingered Salute".

[Allen Browne]

Doug, to test that theory, and possibly track down the culprit
hardware/user(s), consider logging users into and out of the database.

Those that log in but don't log out are suspect.

(The other factor might be to identify why it takes so long to start up, and
fix that problem.)

--
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.

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

Another part of the problem is that the application is used 24x7x365,
so to compact/repair, I need to kick users out of the system.

It's just that I *really* have this feeling that the corruption is
caused by people starting up the program (which takes a LONG time),
then deciding "You know what? I don't have time for this..." and they
give it the "3-Fingered Salute".

[(PeteCresswell)]

Per Doug:

>Another part of the problem is that the application is used 24x7x365,
so to compact/repair, I need to kick users out of the system.

It's just that I *really* have this feeling that the corruption is
caused by people starting up the program (which takes a LONG time),
then deciding "You know what? I don't have time for this..." and they
give it the "3-Fingered Salute".

Sounds like a textbook case for moving the back end over to SQL Server.

Anybody else agree?
Any idea why it takes so long to start up? Might be some other issues there...
--
PeteCresswell

[The Frog]

In light of what has been discussed in this topic I would have to
agree with the move to SQL server. A 24/7/365 needs a bit more
reliability than a JET back end can supply it seems. It simply may not
be worth the effort of running down the culprits when you can get a
free version of SQL Server Express and eliminate the problem as well
as a number of other benefits that SQL Server comes with.

I too am interested in the corruption issues, but it seems that
despite the best efforts of some of the most experienced people here
that it isnt actually possible to eliminate the problems, or even
necessarily detect and correct them! Lets face it, MDB files are a
good desktop database to work with, but for mission critical you
really need to move to something that gives you the stability you
need.

I would have to agree with Pete. Though I am not as experienced as
some here, and they may be able to come up with alternatives to
solving your problem, I would tend to think that 24/7/365 means shift
it to something that can operate 24/7/365 and be done with the
headaches.

The Frog

[David W. Fenton]

"(PeteCresswell )" <[e-mail] te in
news:86******** *************** *********@4ax.c om:

Per Doug:

>>Another part of the problem is that the application is used
24x7x365, so to compact/repair, I need to kick users out of the
system.

It's just that I *really* have this feeling that the corruption is
caused by people starting up the program (which takes a LONG
time), then deciding "You know what? I don't have time for
this..." and they give it the "3-Fingered Salute".

Sounds like a textbook case for moving the back end over to SQL
Server.

Anybody else agree?

Well, the startup issues aren't, but the 24/7 requirement certainly
is.

Any idea why it takes so long to start up? Might be some other
issues there...

Obviously if you've designed your app so that it takes so long to
load that users give up, it's *your* fault if they are corrupting
the database by killing the process. The easiest way to fix it is
judicious use of DoEvents so that something happens onscreen
periodically to tell them that the app is loading. I recently added
an unnecessary splash screen with 'Please wait while program loads"
before the switchboard comes up (when previously the the blank
Access window was all that was visible), and the users said "Oh!
You've done something to speed up the load time!" Nothing actually
changed behind the scenes -- all that changed was what users were
seeing, and it made them feel like the app was loading more quickly.

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

[(PeteCresswell)]

Per The Frog:

but it seems that
despite the best efforts of some of the most experienced people here
that it isnt actually possible to eliminate the problems, or even
necessarily detect and correct them!

I had a similar experience when my worst-case corruption scenario was going on:
beeeeeeg mutual fund, extremely sophisticated LAN people, the latest tools....
and nobody could pinpoint the problem.
--
PeteCresswell