Environment is SQL Server 2k and Access 2k.
I have a number of views that look like "Select tblWhatever.* From
tblWhatever Where tblWhatever.UserGroup In (Select UserGroup From
vwCurrentUser)". The base tables have insert triggers that plug
UserGroup into them. The primary key of each base table is an Identity
column. Some tables have foreign keys.
I have no problems adding records to views where the base table has no
FKs, but adding records to views where the base table does have FKs
If I link the tables with FKs in my Access front end, I have no
problems adding records. However, if I link the view, I get #Deleted
errors whenever I try to add a record that duplicates an existing FK
value. Requerying the view reveals the record that was just added.
This could be something to do with the known problem of Access not
knowing the value of the just added record's Identity column and
trying to find it based on the values of other fields. But if that's
so, why don't the same problems occur when I add the records to the
table rather than the view?
I understand that the insert trigger is most likely doing a delete
followed by an add. Could this cause a problem with the view but not
with the base table?
Any suggestions would be much appreciated.
thanks Dori Kafri