Access2002 - Ignoring instead of triggers on Views

  • Thread starter Thread starter Micah Miller
  • Start date Start date
M

Micah Miller

I am been brought to confusion! Does an Access ADP even pay any attention
to triggers?! I have a table that has ordering on it. The instead of
delete trigger on the view deletes the record and decrements the ordinal of
any remaining records (they are all linked by a common number i.e. fileID).
When I execute the delete statement in Query Analyzer it works like it's
supposed to, but... When I try deleting the record using the form having the
recordsource pointing to the view w/ the instead of trigger - nada. It's as
if it goes directly to the source table and makes modification rather than
using the view. I even tried opening the view up in Access and deleted the
record.. same result. I have closed down my app & reopened it, still to no
avail.

Is their anyway to make sure it will conform and use the trigger on the view
I gave it rather than Access thinking for itself and going directly to the
table?!

Thank you for your help,

Micah Miller
 
Well, the ADP shouldn't have to pay attention to triggers, they're invisible
to a front-end trying to do an update via a view or table that has one. In
the case of ADO, though, it's not using the view. ADPs try to be smart and
look at the source tables a view or stored procedure is based on, and update
the tables directly. This is what allows an ADP to do updates on otherwise
non-updateable output.

To force ADO to not see the structure behind a view, you must creat the view
using the WITH VIEW METADATA option. With that option, the real data sources
are not reported to the client, so the client will try to update through the
view itself, and your triggers will fire like you want them to.
 
Thanks Steve!


Steve Jorgensen said:
Well, the ADP shouldn't have to pay attention to triggers, they're invisible
to a front-end trying to do an update via a view or table that has one. In
the case of ADO, though, it's not using the view. ADPs try to be smart and
look at the source tables a view or stored procedure is based on, and update
the tables directly. This is what allows an ADP to do updates on otherwise
non-updateable output.

To force ADO to not see the structure behind a view, you must creat the view
using the WITH VIEW METADATA option. With that option, the real data sources
are not reported to the client, so the client will try to update through the
view itself, and your triggers will fire like you want them to.
Click to expand...
 
I guess I responded too soon. :S Now access won't let me add aditional rows
nor make edits on the form using the view w/ INSTEAD OF triggers. I am going
to try dropping and recreating the INSTEAD OF triggers and see how that
goes.

--Micah
 
Well, the ADP shouldn't have to pay attention to triggers, they're
invisible to a front-end trying to do an update via a view or table that
has one. In the case of ADO, though, it's not using the view. ADPs try
to be smart and look at the source tables a view or stored procedure is
based on, and update the tables directly.
Click to expand...

Arggggggggggggggggggggggggghhhhhhhhh!

I, who used to champion ADPS, am getting less and less happy about them.
Their workings are so arcane and bizarre, that I'm wondering whether it
might be more efficient to use them (or MDBs) without any global connection
at all, (all forms and reports unbound), or to use some other front end
with an OLEDB connection.

Microsoft wants to do so much. I would love it if they would do less, but
be more open about what they are doing.
 
Well, you may have run into one of the many things that will keep ADO from
being able to edit a view that has the WITH VIEW METADATA option specified.
I've noticed that a view with a join will have this problem. If you don't
include the primary keys of all tables involved in the join, no key field is
reported, so ADO won't allow editing. If you do include the key fields from
all the tables, ADO thinks that all the key fields are part of a compound key,
so it will allow edits OK, but it thinks the record is deleted after you
change a foreign key link during an edit.
 
Arggggggggggggggggggggggggghhhhhhhhh!

I, who used to champion ADPS, am getting less and less happy about them.
Their workings are so arcane and bizarre, that I'm wondering whether it
might be more efficient to use them (or MDBs) without any global connection
at all, (all forms and reports unbound), or to use some other front end
with an OLEDB connection.

Microsoft wants to do so much. I would love it if they would do less, but
be more open about what they are doing.
Click to expand...

IMO, Microsoft has a tendency to overengineer and have the software try to
think for the user, and insist that its thinking is right, even when it
differs from the user's intentions (e.g. trying to get Word to preserve a
strange capitalization). To me, the SQL Server OLEDB driver and ADPs are
extreme examples of this tendency.
 
Steve,

Well I thank you for help clearing up Microsoft's logic on this. Honestly, I
wanted to make a tier at the database level to handle cascading changes and
let the data server handle the data itself instead of managing all this code
in VB. Now I ask myself, after having a better understanding of the logic
for Access ADPs, why again should I write views or put triggers on if the
ADP is going to do it's own thing?! When I want things to work a certain
(expected) way, and I can't do anything about it, that's the worst! I have
found that if I create an ADODB.Connection object and execute the
statement - the triggers work. This tells me it's more of an Access ADP
thing than strictly ADO.

Wouldn't it take more time, more bandwidth, and more resources then to have
the application manage everything then it would to break the processes up
into different tiers and distribute them?

Is the only workaround to develop the application forms as unbound and
program all the updates myself, or can I do a sort-of instead of delete do
this routine in Access forms? Is there any way disable"smart" off and
enable "obedient"(do what I program)?

I'm sorry but it's a little frustrating to find out that a tier what I put
in place (triggers & views etc) is going to be flat out ignored by Access
because it will do it's own thing, considering the fact that it was
developed developed by Microsoft which also developed MSSQL Server. There
is a point convenience crosses the border on absurd...

I thank you for your time and efforts to help me with this, and I appreciate
what you have gone through to learn what you know about all of this. Please
don't take me as griping, I am just trying to determine what to do next.

Micah Miller
 
The best answer I've been able to come up with is to follow these rules:

1. Only put triggers on tables and "WITH VIEW METADATA" views.
2. If I need instead of triggers, and a multi-table view, I make single table
views to put the instead of triggers on, then make another view without "WITH
VIEW METADATA" to join those views.

This doesn't cover everything you could want to do, but it covers a lot of it.
 
Steve,

I've actually found a workaround for all of this earlier today. With Access
Forms, for example, I set Cancel = True in the onDelete event procedure and
then tell the CurrentProject.Connection (ADODB.Connection in ADP) to execute
the delete then requery the form. So where there are triggers I can use
this and other methods to cancel out what Access is going to do and
programatically tell it what it *should* be doing.

Thank you for that last suggestion also,

Micah
 
Micah,

http://support.microsoft.com/?kbid=301987

This paper discusses the role of the "Client Data Manager" (CDM) which you
might like to read too...

Improved Update Semantics with Multi-Table Views
Improved update semantics is one of the best improvements the CDM offers in
Microsoft Access 2002. In Microsoft Access 2000, forms that were based on
multi-table views were read-only unless the developer supplied a table name
for the form's UniqueTable property. This would allow the form to be
partially updateable, so that only fields from the table specified in the
UniqueTable property could be edited.

When using Microsoft Access 2002 with Microsoft SQL Server 2000, the CDM
automatically retrieves unique table information by calling SQL Server
stored procedures. This allows fields from both sides of a multi-table view
to be updateable without having to supply the form's UniqueTable property.
This allows developers to create client/server solutions that are more
similar to Access solutions based on Jet.
 
Micah,

http://support.microsoft.com/?kbid=301987

This paper discusses the role of the "Client Data Manager" (CDM) which you
might like to read too...

Improved Update Semantics with Multi-Table Views
Improved update semantics is one of the best improvements the CDM offers in
Microsoft Access 2002. In Microsoft Access 2000, forms that were based on
multi-table views were read-only unless the developer supplied a table name
for the form's UniqueTable property. This would allow the form to be
partially updateable, so that only fields from the table specified in the
UniqueTable property could be edited.

When using Microsoft Access 2002 with Microsoft SQL Server 2000, the CDM
automatically retrieves unique table information by calling SQL Server
stored procedures. This allows fields from both sides of a multi-table view
to be updateable without having to supply the form's UniqueTable property.
This allows developers to create client/server solutions that are more
similar to Access solutions based on Jet.
Click to expand...

All of which -
1. Breaks some things that used to work in Access 2000.
2. Allows some things that cause problems that weren't allowed in Access 2000.
3. Doesn't solve the problem in updating WITH VIEW METADATA queries, nor the
problem of view triggers not firing when the views are not WITH VIEW METADATA
which is what this thread is about.
 
Steve Jorgensen said:
All of which -
1. Breaks some things that used to work in Access 2000.
2. Allows some things that cause problems that weren't allowed in Access 2000.
3. Doesn't solve the problem in updating WITH VIEW METADATA queries, nor the
problem of view triggers not firing when the views are not WITH VIEW METADATA
which is what this thread is about.
Click to expand...

Hmmm. Though it does not solve the problem, I find it related to
understanding the problem, but perhaps that is my misunderstanding.

The relationship that I see of this article to the topic of view
updateability is this: updateability sematics will be different depending
upon what OLEDB provider you use. If you use currentproject.connection you
get the new one (Microsoft.Access.OLEDB.10.0) which has the CDM semantics
that is under discussion. If you use MSDASQL then (I beleive) you get the
old semantics that you are used to from Access 2000 (so Micah does not have
to use DLookup).

Perhaps this is not explicit in the article, or perhaps my understanding is
incorrect. I will bow out of this thread now.

Sincerely,

Malcolm Cook
 
Hmmm. Though it does not solve the problem, I find it related to
understanding the problem, but perhaps that is my misunderstanding.

The relationship that I see of this article to the topic of view
updateability is this: updateability sematics will be different depending
upon what OLEDB provider you use. If you use currentproject.connection you
get the new one (Microsoft.Access.OLEDB.10.0) which has the CDM semantics
that is under discussion. If you use MSDASQL then (I beleive) you get the
old semantics that you are used to from Access 2000 (so Micah does not have
to use DLookup).
Click to expand...

OK, I agree with that. I guess I just didn't see that was the point you were
trying to make - that and I was in "irritation with ADP" mode.
 
11kfk
Micah Miller said:
Steve,

Well I thank you for help clearing up Microsoft's logic on this. Honestly, I
wanted to make a tier at the database level to handle cascading changes and
let the data server handle the data itself instead of managing all this code
in VB. Now I ask myself, after having a better understanding of the logic
for Access ADPs, why again should I write views or put triggers on if the
ADP is going to do it's own thing?! When I want things to work a certain
(expected) way, and I can't do anything about it, that's the worst! I have
found that if I create an ADODB.Connection object and execute the
statement - the triggers work. This tells me it's more of an Access ADP
thing than strictly ADO.

Wouldn't it take more time, more bandwidth, and more resources then to have
the application manage everything then it would to break the processes up
into different tiers and distribute them?

Is the only workaround to develop the application forms as unbound and
program all the updates myself, or can I do a sort-of instead of delete do
this routine in Access forms? Is there any way disable"smart" off and
enable "obedient"(do what I program)?

I'm sorry but it's a little frustrating to find out that a tier what I put
in place (triggers & views etc) is going to be flat out ignored by Access
because it will do it's own thing, considering the fact that it was
developed developed by Microsoft which also developed MSSQL Server. There
is a point convenience crosses the border on absurd...

I thank you for your time and efforts to help me with this, and I appreciate
what you have gone through to learn what you know about all of this. Please
don't take me as griping, I am just trying to determine what to do next.

Micah Miller


field compound instead on directly
Click to expand...
 
Back
Top