deleting records and changing the 1ry key

[p cooper]

I want to use a combobox to save information in table1 from table2
I 'think' I can link to inforamtion I need later by saving the table2 1ry
key.

Q: If I leter delete a record from table2 what happens to the primary key?

Does Access re-arrange everything so the reference will still be correct ?
Does it recalculate the 1ry keys ?
Just ignore the gap?
would I be better linking using a field ?

 

[John Nurick]

I want to use a combobox to save information in table1 from table2
I 'think' I can link to inforamtion I need later by saving the table2 1ry
key.

By definition the primary key can uniquely identify any record in the
table, so if you store the PK value from a record in table2 in a field
in a record in table1 you have linked the two records. It's a
one-to-many relationship: one record in table2 can be linked to any
number (including zero) records in table1, but each record in table1 can
link to zero or one records in table2 (because the linking field in
table1 can only hold one primary key value at a time).

Q: If I leter delete a record from table2 what happens to the primary key?

You mean the corresponding primary key value in a record in table1?
Normally nothing. The value remains but is useless because there's no
corresponding record in table2. If you have specifically created a
relationship between the two tables, enforced relational integrity, and
enabled cascading deletes, deleting a record from table2 will delete all
related records from table1.

Does Access re-arrange everything so the reference will still be correct ?
Does it recalculate the 1ry keys ?

Never (unless you have casccading updates enabled and alter the primary
key yourself)

Just ignore the gap?

Yes, unless you use cascading deletes.

would I be better linking using a field ?

What you are doing *is* linking using a field: in relational databases
like Access there's no other way of doing it.