indexes and relationships blown away sometimes

[Keith G Hicks]

Access 2000 w/ jet backend.

This client of mine runs the app in a pretty harsh environment. Long cables
(and poor quality ones at that), no boosters, very hot temperatures (the
air, not the employees, LOL) and impatient operators. Some time ago I
noticed that some of the relationships were blown away and primary key
fields were no longer primary keys. Once in a while I'd have to recreate a
few of them as needed. After a few years of littel contact with them I've
found that the problem has gotten worse and the current data file is full of
corruption. Of all my Access clients, this is the only one I've ever seen
with this level of corruption.

Allen Browne's site has this section:
http://allenbrowne.com/ser-47.html#LostKeyRelation

But he doesn't say why indexing rules could be violoated. I'm wondering how
much the hardware has to do with this. I'm also wondering how Access is
letting duplicate PK's into tables so that the compact & repair ends up
deleting indexes and FK's. I'm hoping that if they upgrade some network gear
that the problem will be reduced significantly. I'm also wondering how much
help it would be for me to convert this thing to MS SQL on the backend. I
did that for another client and corruption was reduced but they didn't have
any where near the problems that this client has.

Thanks for any input,

Keith

 

[Jeff Boyce]

Just a thought ... any chance they're also using wireless connections?

Is this a split application, with one copy of the front-end on each user's
PC and a single copy of the back-end on their network?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Albert D. Kallal]

I'm also wondering how much help it would be for me to convert this thing
to MS SQL on the backend.

The above is usually far more reliable in harsh environments. so, I think
this is good idea.

I did that for another client and corruption was reduced but they didn't
have any where near the problems that this client has.

Is a copy of the application installed on each computer? This REALLY helps.
I deployed some applications in some not so great environments, and been
really surprised on reliability the setup is when you split this way.

so:
Ensure that each workstation has a copy of the front end (don't allow
multiple users into the same front end).

ensure that each workstation has all of the service packs installed,
especially for 2000 and also install the jet updates.

ensure no one is using wireless networks connections.

If the above is already your current setup, then improving the network
cabling is possible, or it simply might be less cost to simple use one of
the several free editions of sql server for the back end...

I also like installing a mde on each computer, but I have no data or proof
that using a mde is better or more reliable network wise then distributing
an mdb of the application to each computer.

 

[Albert D. Kallal]

I bumped send..but take a look at this corruption faq here:

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

the above is gem of information on reducing corruption issues...

 

[Keith G Hicks]

Yes, split (never done anything but split - seems like trouble to do the
other way).

Only one machine is wireless. But that's a fairly new thing. They were
having this trouble long before that was set up.

 

[Keith G Hicks]

Thanks for the info guys. Really apprecieated.

Keith

 

[Jerry Whittle]

Is the app locked down and only a few people can get to the table design?
I've seen users mess with things like primary keys when they became "too much
trouble".

 

[Keith G Hicks]

Nobody touches it. They have no idea how and they don't have time. The
changes are too random as well.