MS Access Multiuser - What causes the need for repair?

Author

What causes the need for repair?

Emma Hope

2005-10-27, 5:30 pm

I have a multi-user secured database running across a network. It has three
main tables which are connected by IDs and the main form has these as a form
with a subform and a subform within that. A few of my fields are mandatory
and there are a couple of combo box lookups. There are also a bunch of lookup
tables and querys.

My users two or three times a day seem to manage to corrupt the database and
i have to kick them all out, repair and let them back in. I cannot see what
is causing the db to corrupt each time and they are not reporting seeing any
error messages etc. I guess this is a really vague question but from what i
have described can anyone give me any pointers.

Secondly, i know i should split the db into FE and BE and i am currently
working on doing this. Does anyone know if doing this might help to reduce
the problem?

On a different but similar note, i was contemplating setting up a similar db
for our remote users, i.e. laptop, dial up users but decided against it as
i'm almost 100% sure that their bad habits of disconnecting themselves but
just yanking out the cable would cause identical and more frequent problems.
Would a FE/BE split help to stop this also?

Thanks
Emma

Larry Linson

2005-10-28, 3:24 am

"Emma Hope" < EmmaHope@discussions
.microsoft.com> wrote

>I have a multi-user secured database
> running across a network. <SNIP>
> My users two or three times a day seem
> to manage to corrupt the database and
> i have to kick them all out, repair and let
> them back in. I cannot see what is causing
> the db to corrupt each time and they are
> not reporting seeing any error messages etc.
> I guess this is a really vague question but
> from what i have described can anyone
> give me any pointers.

I suspect you have identified a major contributor to the problem in the very
next paragraph.

> Secondly, i know i should split the db into
> FE and BE and i am currently working on
> doing this. Does anyone know if doing this
> might help to reduce the problem?

This is not a "secondly"; it is a "firstly", and should be done without
further delay -- having multiple users logged in to the same monolithic
database, or to the same front-end, significantly increases the chance of
corruption (which is why you are having to do such repeated compact/repair
runs). And anyone who's followed the Access newsgroups will surely have seen
the strong admonitions on this issue.

The best resource that I know about for multiuser performance and avoiding
corruption in the multiuser environment is MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. It has a wealth of information and
links.

> On a different but similar note, i was contem-
> plating setting up a similar db for our remote
> users, i.e. laptop, dial up users but decided against it as
> i'm almost 100% sure that their bad habits of
> disconnecting themselves but just yanking out
> the cable would cause identical and more frequent
> problems. Would a FE/BE split help to stop this also?

Nothing but a LAN (preferrably a 100 MBPS or faster LAN) is satisfactory for
running an Access - Jet multiuser database, not even a typical WAN, unless
you use remote access software to execute Access from afar. FE/BE split
won't make it so, either.

Larry Linson
Microsoft Access MVP

Sponsored Links