Relationships disappeared

[Lungta]

I searched this forum but only found 1 similar post.

Twice now in the past week copies of my db have turned up with all the
relationships gone at the end of the day. I make multiple copies daily as it
can't be split yet, and reconcile all the updates at end of day. Last week
it happened on 2 of the copies in use, but today it's on all of them.

A similar post suggests they're just cosmetic, but I just tried to add data
that violates ref. integrity and it let me, no squawking.

Can anyone help with either why this is happening, or what to do about it?

It's Access 2000 running within Access 2003 on XP, across a network (each
user has his own distinct copy).

Any ideas would be much appreciated!

Thanks,

 

[Damian S]

Hi Lungta,

Have you tried opening the relationship window, then right-click-->show all?
Does this show them.

Also, if each user has their own copy, how are you going about reconciling
them?

Damian.

 

[Lungta]

Hi, Damian,

Yeah, I tried that but no change. Is there any possibility that turning off
Name Autocorrect did this? that's the only change between the 2nd last and
last backups yesterday. (made a lot of changes so backed up every couple
hours)

Ended up opening a new blank db and importing everything from a Monday
backup that still had the relationships, then adding the new records from the
tables modified today.

I turned off Name Autocorrect in anticipation of making a mde file &
splitting properly.

You don't even want to know how the end of day reconciling is done - I gotta
learn about update or append queries...!

If you have other ideas as well, I'm happy to hear them. Thanks!

 

[Damian S]

Well, turning off name autocorrect shouldn't affect the relationships at all.
I would STRONGLY recommend that you split your data from your front end so
that you can have all your users talking to the back end data (on your
network) with their own local copy of the front end.

Damian.

 

[Allen Browne]

There is a very good chance that Name AutoCorrect caused Access to be
confused about the relationships. Turning it off would not trigger the
problem, but it could have exposed it.

Creating a new (blank) database and importing everything was a good idea,
provided you unchekced the Name AutoCorrupt boxes before you imported the
tables.

Rarely, Access will drop a relation during a compact/repair operation. It
happens with the primary key index is damaged (e.g. there's a duplicate in
there), so it drops the index, which in turn drops the relation that
depended on the index. When it does this, it adds a new table explaining the
compact errors. It is extremely unlikely that this would happen on all your
tables at once.

There are issues where users have different versions of JET and Office.


I could not follow the part where you explained that multiple users are all
using the same unsplit database over the network at the same time and yet
they each have their own distinct copy. That seems like a contradiction to
me.

 

[Lungta]

OK, I hear you on that one. I can't figure what caused it either but maybe it
is just the situation of copying/pasting so many hundreds of times over the
last several months. The new clean import works well on the home pc, so
we'll watch & see what happens tomorrow.
Splitting is definitely the next step, but at the moment the network is so
slow that the performance is really not acceptable, so no chance yet. Thanks
very much for your time and input, Damian!

 

[Lungta]

Hi, Allen,

Thanks - that's the single change between those 2 backups so I suspect you
must be right that it triggered something there. I did make sure the
NameAutoCorrect was unchecked at the outset before importing this new one, so
hope it'll be better tomorrow. But there's a new backup with Name
Autocorrect checked, too. Hopefully they'll both be cool now.

Good to know that background about the primary key/compact repair thing. We
all just got converted to XP/Office 2003, so I'm expecting the Jet/Office
stuff is consistent between these machines. But maybe an error log would be
a good start - my new users are all afraid of computers and do some pretty
odd things from time to time. Can't use much validation as the records can
vary a lot, so I mostly try to just check their input to ward off any bad
reactions.

btw the main db is copied so multiple users all have their own distinct copy
and their changes get merged into one db every day. Not too efficient but
we're getting there (& learning tons from this newsgroup).

Thanks for the help!