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]

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

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.

I think you would be hard-pressed to find a recommendation from the product
team or any MVP that Access is suitable for a 24x7x365 (certainly falls in
the category of "mission critical") application -- file-server databases
weren't designed for that, weren't intended for it, and aren't good in that
role.

Most of us are strong believers in "appropriat e technology" and neither
Access nor Jet are "appropriat e technology" for that kind of application.

Larry Linson
Microsoft Access MVP

[(PeteCresswell)]

Per Larry Linson:

neither
Access nor Jet are "appropriat e technology" for that kind of application.

Would that caveat extend to MS Access as a front end to an SQL Server DB - or is
it just for the back end?
--
PeteCresswell

[Larry Linson]

"(PeteCresswell )" <[e-mail] te

>neither Access nor Jet are "appropriat e technology"
for that kind of application.

Would that caveat extend to MS Access as a front end
to an SQL Server DB - or is it just for the back end?

Access should be just fine as a _client_ to SQL Server or other server DB,
provided the Access client is carefully designed so that it can just be
replaced in case of bloat or corruption. Executing certain Queries can
cause bloat, as well as what we think of as "normal" causes of bloat --
deleting Records or objects from the Access DB.

If the Access client will ever need to be compacted or repaired, it would
not be suitable -- and some people do try to keep user-specific data in the
Access client. Compacting and repairing means that it will be "off-line" for
some period, and that is not consistent with 24/7/365 operation. If there is
user-specific data, then another database with linked tables, on the local
machine or LAN, should be used rather than storing the user-specific data in
the front-end/client.

24/7/365 is a demanding requirement; in a previous incarnation as a
mainframer, such systems were often duplicated and switched to the backup
machine/system when maintenance (software or hardware) was required on the
production machine. Similar things are done with clusters of servers and
banks of drives, these days.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

"Larry Linson" <bo*****@localh ost.notwrote in
news:a%XNh.2870 $yo3.1181@trndd c04:

Most of us are strong believers in "appropriat e technology" and
neither Access nor Jet are "appropriat e technology" for that kind
of application.

Access is a perfectly appropriate technology for building a front
end for that kind of application. Why would you suggest otherwise?

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

[David W. Fenton]

"Larry Linson" <bo*****@localh ost.notwrote in
news:Qj_Nh.6444 $vI1.709@trnddc 02:

If the Access client will ever need to be compacted or repaired,
it would not be suitable -- and some people do try to keep
user-specific data in the Access client. Compacting and repairing
means that it will be "off-line" for some period, and that is not
consistent with 24/7/365 operation. If there is user-specific
data, then another database with linked tables, on the local
machine or LAN, should be used rather than storing the
user-specific data in the front-end/client.

I strongly doubt that the 24/7 requirement applies to the client.

And any front end that is storing data that is not static is
wrongly-designed.

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

[The Frog]

I have used MS Access as a client with various database servers as the
backend, such as SQL Server and Oracle. The designs have been
successful with both linked tables and just using ADO for all the
heavy lifting. I have to say that I prefer SQL Server to Oracle though
- but thats a personal taste thing.

The only thing that I suppose I found to be a rule with this type of
thing, as it is with all dveleopment, is to make sure that the code is
robust and reliable, and that the forms are user friendly. The goal in
my case is always to facilitate workflow processes and to make the
users lives easier. A lot of the time is designing, as I have found,
goes into understanding the workflow and the expectations of the
users. Long load times? - then change the way the application appears
to load. Long query wait times? - perhaps some sort of pre-aggregated
tables to speed things up. And so on.... There are lots of different
problems and lots of different solutions. One of the reasons I love
this newsgroup :-)

Cheers

The Frog

[Doug]

We are re-writing the application with a dotNET front end and a SQL
Server back end.

I am just trying to test my hypothesis about the MDB corruption.

Thanks for all the input.

[Tony Toews [MVP]]

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

>We are re-writing the application with a dotNET front end and a SQL
Server back end.

Good luck. Hope you have a nice sized budget. Any estimate on how much longer the
same app will take in .NET?

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:

>We are re-writing the application with a dotNET front end and a SQL
Server back end.

Do you have good numbers on how much it cost to write the original app?

If so, it would be nice tb able to compare the new version's cost once it's
done.
--
PeteCresswell

[Susie DBA [MSFT]]

MDB is not reliable enough for a single-user application

lose the training wheels; you MDB kids are con artists
-Susie
On Mar 26, 6:26 pm, "David W. Fenton" <XXXuse...@dfen ton.com.invalid >
wrote:

"Larry Linson" <boun...@localh ost.notwrote innews:a%XNh.28 70$yo3.1181@trn ddc04:

Most of us are strong believers in "appropriat e technology" and
neither Access nor Jet are "appropriat e technology" for that kind
of application.

Access is a perfectly appropriate technology for building a front
end for that kind of application. Why would you suggest otherwise?

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