March 21, 2010 at 8:36 am
I want to present some view f the data to my users and authorise update on these views via "Instead of triggers".
My view works fine and the updates do what I want but it looks like MS Access is too smart and fires the update directly on the underlying tables rather than the view, therefore bypassing my "Instead Of" triggers.
Through TSQL, everything works fine as long as you fire the update on the view...
Is there a way to tell Access not to try and be too smart?
Eric:ermm:
March 21, 2010 at 12:39 pm
Alter the view and include the "WITH VIEW_METADATA" option. You may also want to also include the "SCHEMABINDING" option, which sometimes improves performance.
From Books On-line:
Specifies that the instance of SQL Server will return to the DB-Library, ODBC, and OLE DB APIs the metadata information about the view, instead of the base table or tables, when browse-mode metadata is being requested for a query that references the view. Browse-mode metadata is additional metadata that the instance of SQL Server returns to these client-side APIs. This metadata enables the client-side APIs to implement updatable client-side cursors. Browse-mode metadata includes information about the base table that the columns in the result set belong to.
For views created with VIEW_METADATA, the browse-mode metadata returns the view name and not the base table names when it describes columns from the view in the result set.
When a view is created by using WITH VIEW_METADATA, all its columns, except a timestamp column, are updatable if the view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers.
SQL = Scarcely Qualifies as a Language
March 28, 2010 at 8:31 am
Now, I run into the problem of Access thinking the view is not updateable...
How can switch my form back to Read/Write mode?
April 18, 2010 at 10:00 am
Actually, I have been testing using a simple view with two tables. No form involved.
I create an INSTEAD OF INSERT and INSTEAD OF UPDATE triggers, yet MS Access still views the recordset as Not updateable...
Any trick?
:crying:
April 18, 2010 at 10:49 am
does the view include primary keys from both tables? Even if they're not used for anything else, this will help access "make sure" it know what record in each table might be affected. This is usually the root cause for Access making queries not updatable (even its own).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 18, 2010 at 2:28 pm
That's interesting because I kind of removed the primary key from one of the tables to make sure it could not do an update on it without my triggers!
I did this because I thought the idea of the INSTEAD OF trigger was to "trust the trigger" to do the right thing, in which case Access does not really need to understand what's going on...
I tried adding the missing key but still the recordset is read-only
Of course, I can update the view myself through TSQL (via the Instead of triggers)
April 19, 2010 at 10:53 am
Eric Mamet (4/18/2010)
That's interesting because I kind of removed the primary key from one of the tables to make sure it could not do an update on it without my triggers!I did this because I thought the idea of the INSTEAD OF trigger was to "trust the trigger" to do the right thing, in which case Access does not really need to understand what's going on...
I tried adding the missing key but still the recordset is read-only
Of course, I can update the view myself through TSQL (via the Instead of triggers)
I believe Access actually needs a unuiqe key for the VIEW. It will probably ask for that information when you initially link the view.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply