indexes and relationships blown away sometimes

K

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
 
J

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
 
A

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

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

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

Keith G Hicks

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top