Using a trigger on a view from an .adp

[Chris]

Hi,
Does anybody know if Access 2000 .adp files work
correctly with SQL2000 views. I have set up a view in SQL
2000 and have set this as the recordsource for my form.
This works OK in terms of getting access to the data from
my interface. I have then created an update trigger on the
view (which works correctly through query analyser). I am
able to alter data from my access app without the trigger
being fired.

does access work correctly with views, or does it somehow
interpret the view and effect it's action on the base
table?

Thanks in advance
Chris

 

[Chris]

Thanks Steve,
Do you know is this for ADO in general, or
just the connection from Access bound forms(like the one I
am using)?

Also is this with the latest MDAC?

I have read that these use a datashaped provider to get
their data. Will this situation the same even if I abandon
bound forms and create an unbound one?

Thanks
Chris

-----Original Message-----
ADO wants to take control and do things itself if it can. This means, it looks
at the attributes of the view object, then updates the underlying tables itself,
circumventing any triggers you may have placed on the view. To get around this
create the view using the WITH VIEW METADATA option. This makes the view appear
to be a table as far as ADO is concerned, and ADO won't try to go around the
view.

Note that there are some potential snafus with this. If your view includes a
join of 2 tables, ADO will report bizarre key

information. If all key columns

from all tables are included in the view, the view will report all of them as
keys of the view, and ADO will treat that combination as the unique identifier.
If you don't include all keys from all tables in the view output, the view will
report nothing as part of a primary key, and ADO will not let you try to update
the view using a recordset.

The strategy I've come up with is to make WITH VIEW METADATA views of individual
tables so I can put my INSTEAD OF triggers on those, then create normal views of
those views for joins of multiple tables.

 

[Steve Jorgensen]

Sorry, I misspoke about what was responsible for what. It's SQL Server that
reports the key column combinations, so different versions of ADO will not make
a difference in the behavior.

Thanks Steve,
Do you know is this for ADO in general, or
just the connection from Access bound forms(like the one I
am using)?

Also is this with the latest MDAC?

I have read that these use a datashaped provider to get
their data. Will this situation the same even if I abandon
bound forms and create an unbound one?

Thanks
Chris

-----Original Message-----
ADO wants to take control and do things itself if it can. This means, it looks
at the attributes of the view object, then updates the underlying tables itself,
circumventing any triggers you may have placed on the view. To get around this
create the view using the WITH VIEW METADATA option. This makes the view appear
to be a table as far as ADO is concerned, and ADO won't try to go around the
view.

Note that there are some potential snafus with this. If your view includes a
join of 2 tables, ADO will report bizarre key

information. If all key columns