Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 11th, 2005, 04:07 AM
Registered User
 
Join Date: Mar 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default .Adp(Access) from not using "Instead of Triggers'

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

 
Old March 11th, 2005, 09:54 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old March 14th, 2005, 03:35 AM
Registered User
 
Join Date: Mar 2005
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 18th, 2005, 12:40 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
access adp subform connected to sql 2005 skype Access 1 February 5th, 2007 08:45 AM
Access ADP - Getting Current User Name Scripts82 Access VBA 2 March 8th, 2006 09:38 PM
SQL 2000 SERVER+ACCESS ADP DLOOKUP MHONG Access ASP 1 October 25th, 2005 08:36 AM
Delete Query in Access Data Project (ADP) Pavesa Access 1 February 18th, 2005 08:38 AM
Adding new record in Access 2003 adp subform rayo Access 1 September 4th, 2004 04:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.