Hi Pat,
It's kind of interesting how the various versions of Access work. In Access
2000, one can create lookups at the table level with existing relationships
in place. Access 2002, which is the version that I am currently using,
requires one to first delete the existing relationship before they are
allowed to use the table lookup wizard. At the end of the process, the lookup
wizard creates a new relationship, but it does not include referential
integrity.
I'm guessing--Tom goes out on a limb here--that you are using Access 2003
AND that this version of Access may have gotten rid of the error message that
tells a person that they first need to delete a relationship by just doing it
for them. Isn't that swell? It then finishes the process by just creating a
basic relationship, just like Access 2002.
I never was able to get a clear answer from the members of the Access
development team, who attend our monthly meetings of the Pacific NW Access
Dev. Group, when I asked the question why the requirement to first delete a
relationship as introduced in Access 2002. I'm guessing that perhaps one ends
up with duplicate indexes in Access 2000 if they use the table lookup wizard
with an existing relationship in place.
If I NOW remove the Lookups, will that impact the existing Forms? No.
What would happen if I left the Lookups, and attempted to enforce
Referential Integrity?
Not a problem. You can and should do this.
Tom
___________________________________________
:
G'Day Nikos,
Thank you for your time.
I created the Lookups because Forms based on the Tables 'inherited' them,
and that appeared to be an 'easy' process.
What you seem to be saying is that if I had NOT created them, then in
the Forms I could have used the Control Wizard to achieve the same
outcome - and retained my Referential Integrity to boot!
I see the problem in relation to New Databases and will not repeat it.
If I NOW remove the Lookups, will that impact the existing Forms?
What would happen if I left the Lookups, and attempted to enforce
Referential Integrity?
Thanks again!
--
Regards,
Pat Garard
Melbourne, Australia
___________________________________________
Pat,
It's the lookup fields! Have a look at:
www.mvps.org/access/lookupfields.htm
and you'll know why.
The solution is: remove the lookup fields, and restore the referential
integrity on your relationships. Use lookups in forms only, this is where
they are required... the user is not supposed to work directly in tables
anyway.
HTH,
Nikos
________________________________________
G'Day All,
I have an Access database where the back end was originally designed
with Referential Integrity enforced.
I subsequently modified the design to include a number of 'Lookup' fields.
For those fields, the relationships remain, but without Referential Integrity.
Should I edit those relationships to again enforce Referential Integrity?
