J
JMagaram
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?
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?