Expert Question - ADP - "Instead of" Triggers

[JustinMagaram]

Using an Access ADP, how can I ADD records to joined
tables? Here are the details:

I am using an Access ADP connected to SQL Server. I have
two tables that have a 1-0 or 1 relationship. There is an
autonumber field in the primary key of the base table. I
created a view that joins these tables, and want the view
to be fully updatable - add, delete, edit records. Access
makes the edit and delete easy via datasheet or form. But
when I try to add records I get an error saying "Write
conflict". So I created an "Instead of Trigger" on the
view. It works fine when submitting new records via Query
Analyzer but fails miserably when trying to using the
Access datasheet; Access complains about the primary key
column being null. According to SQL Server documentation -
"An INSERT statement referencing a view that has an
INSTEAD OF INSERT trigger must supply values for every
view column that does not allow nulls. This includes view
columns that reference columns in the base table for which
input values cannot be specified: Identity columns in the
base table for which IDENTITY INSERT is OFF." I can't
figure out how to pass anything but null to the autonumber
column in my view.

So how can I make it possible to use the Access datasheet
to add records to two tables joined in a 1-0 or 1
relationship where the primary key is Identity?

 

[Tom Ellison]

Dear Justin:

You have a foreign table and a dependent table, those being the 1 side
and the 0/1 side of the relationship, respectively. It is possible to
have the foreign table row without the dependent row, but not the
other way around.

It would seem only natrual that you have done this because it matches
the natural scheme of the data. That is, the fact that these are two
separate rows would then be a normal thing that the users understand,
right?

In that case, it would seem very natural to me that you could perform
the insertion in two separate subforms on the screen. The user would
expect to have to put in the foreign table row first, then optionally
enter a single row to the dependent table.

This makes it very easy to handle. You need to add the column values
for the unique constraint on the foreign table subform using the
AfterInsert event. You do not show these columns in the dependent
subform. It is filtered by those columns in the foreign table
subform.

Note that this is a variation that is an alternative to using
Parent/Child Links. We tend not to use these as they prevent using
continuous Foreign Table subforms. Instead, the current event of the
Foreign table subform rewrites the RecordSource of the dependent table
subform. This works the same as the Parent/Child Links, but is more
powerful, especially in allowing continuous subforms.

Using an Access ADP, how can I ADD records to joined
tables? Here are the details:

I am using an Access ADP connected to SQL Server. I have
two tables that have a 1-0 or 1 relationship. There is an
autonumber field in the primary key of the base table. I
created a view that joins these tables, and want the view
to be fully updatable - add, delete, edit records. Access
makes the edit and delete easy via datasheet or form. But
when I try to add records I get an error saying "Write
conflict". So I created an "Instead of Trigger" on the
view. It works fine when submitting new records via Query
Analyzer but fails miserably when trying to using the
Access datasheet; Access complains about the primary key
column being null. According to SQL Server documentation -
"An INSERT statement referencing a view that has an
INSTEAD OF INSERT trigger must supply values for every
view column that does not allow nulls. This includes view
columns that reference columns in the base table for which
input values cannot be specified: Identity columns in the
base table for which IDENTITY INSERT is OFF." I can't
figure out how to pass anything but null to the autonumber
column in my view.

So how can I make it possible to use the Access datasheet
to add records to two tables joined in a 1-0 or 1
relationship where the primary key is Identity?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts