Updating recordsets via query

[Dave Stone]

Can anyone explain what is happening here?
2 tables tblA and tblB, each with a code column and a full-text column (e.g.
C001, "Joe Bloggs"). tblA contains a small subset of the rows in tblB. There
is a query joining these as follows:

SELECT tblA.Code, tblA.Adata, tblB.Bdata
FROM tblA INNER JOIN tblB ON tblA.Code = tblB.code;

tblB.Code is the primary key in that table.

If tblA.Code is defined as tblA's primary key:
On creating a new record in the query datasheet, all 3 columns must be
entered;
and on deleting a record in the query datasheet, the corresponding tblA and
tblB records both vanish.

Now if tblA has no primary key, what do we find? (Exactly the same query
remember):
On creating a new record in the query datasheet, as soon as tblA.Code is
entered, tblB.Bdata is filled in by Access;
and on deleting a record in the query datasheet, the corresponding tblA
record vanishes, but the tblB record remains.


TIA,

Dave

 

[Guest]

Hi Dave,

It sounds like Access is interpreting these differently because in one case
there is a known 1:1 relationship (both tables have PK on related field) and
in the other case Access would see it as a 1:Many (one record in table B
could match many in Table A). In the second case, deleting a record from the
query would not delete a record from Table B because the Table B record could
have matches to other records in table A.

There are many factors other than just primary keys that affect how a query
will perform and whether it will be updateable. Access help has a pretty
good rundown on these under the heading "When can I update data from a query"
(at least thats the heading in Access 2002).

Other things that will affect the way that queries perform include the
predefined referential integrity settings in the table relationships window
and query properties such as the "unique values" setting.

HTH, Ted Allen