Hi,
My problem is that
I have to use a view as a record source in one .ADP project.As the view is joining more than two tables I had to create an Instead Of Trigger in the SQL server end for allowing a table used in the view to be updated.
When I run the view from SQL server end updation is happening fine but from the Access from when ever I am trying to update a record recordset not updatable error is showig.


I have also tried by checking the View proporties "Updatable By Rules" but same result is coming.
Here is the code of the View
CREATE VIEW dbo.testmain4
WITH VIEW_METADATA
AS
SELECT *,(SELECT MIN(date) FROM notes WHERE id = notes.id) AS NoteDate
FROM dbo.Main

and the trigger is


CREATE TRIGGER triTestmain4 ON testmain4
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE main
SET
EmployeeID = I.EmployeeID,
Department = I.Department,
Volunteer = I.Volunteer

FROM testmain4 E, inserted I
WHERE E.ID = I.ID

END



Can Any body give a clue??]
Thanks

I don't do ADP much. But it seems to me that because your view has a calculated field that is based on an aggregate subquery (the field NoteDate), the view will not be updateable by Access rules. Don't know why SQL Server accepts the syntax for update and Access doesn't. But I do know that Access doesn't like to update record sets with aggregate functions such as Min().

Do you have to have the calculated field in the query?

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thanks Randall for your reply. But I suppose I have to use this calculated field in my query.

 Let me give you the problem -- I have two tables one called Main and the other is called Donations and there are two fields in Donations table one is date and another is notes.ID is a field in Main table which is a primary key in Main and foreign key in Donations.
 What we have to do is that -- we have to arrange and display the Main table records in such a manner so that the Ids who have a "Desired Match" against their notes field in the Donations table and minimum date in the date field comes first on ascending order of date arrangement.
Thats all
Can you sort it out without using any (min),(Distinct) etc key word
Thanks
ps_kaushik

Truthfully, I'm confused about what you're trying to accomplish.

If you're simply trying to display the Min(date) from table notes as it relates to the currently displayed record from table Main, you might try building a subform that has your "Select Min(date)..." query as the record source. The subform will be linked to the main form using Link Master field of "id" and Link Child field of "notes.id".

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org