A personal repository of random information in compensation for a fatigued biological computer
You can use a UDF sourced column in a view but if you include it in the select statement MS Access (2003) will then treat the view as not updatable
Had a view with left outer joins Access can sometimes throw up multi step errors even when you are updating 1 field in the main (non outer joined) table.
Moved the outer joins to user defined functions and if fixed the issue.
Doesnt work | SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ALTER View [dbo].[vwKeyValues] AS SELECT tss.tiID, tss.suID, tss.tsValue, ss.suNumber AS Number, ss.suOrder, ss.suSectionOrder, ss.seID, ss.suRowType, ss.suUpdatable, us.seID as UserSection, -- Including these fields makes the view not updatable in MSAccess -- Uncomment for debugging when needed --UserAdmin.IsAdmin, --UserRegionBySection.reID as UserRegionBySection, --UserRegionAll.AllRegions as UserRegionAll, ssp.SSP, dy.dyLocked, dy.dyFirstQuarterLocked, dy.dySecondQuarterLocked, dy.dyThirdQuarterLocked, dy.dyFourthQuarterLocked, LEFT(ss.suDescn + CASE WHEN suRowType IN('T','TT') THEN ' Total' WHEN ss.suRowType IN('PP') THEN ' Percent' WHEN ss.suRowType IN('P') THEN ' Percent' WHEN ss.suRowType IN('DD') THEN ' Completed' WHEN ss.suRowType IN('D') AND CHARINDEX('%',ss.suDescn) > 0 THEN ' Completed' ELSE '' END + REPLICATE(' ',130),130) AS Descn, re.reDisplayRegion AS Region, CASE WHEN NOT ss.suRowType IN ('D','T') OR ss.suUpdatable = 0 OR (UserRegionBySection.reID IS NULL AND UserRegionAll.AllRegions = 0) OR (us.seID IS NULL AND UserAdmin.IsAdmin = 0) THEN 1 ELSE CASE WHEN ssp.SSP = 0 THEN dy.dyLocked WHEN ssp.SSP <> 0 AND dm.dmQuarter = 1 THEN dy.dyFirstQuarterLocked WHEN ssp.SSP <> 0 AND dm.dmQuarter = 2 THEN dy.dySecondQuarterLocked WHEN ssp.SSP <> 0 AND dm.dmQuarter = 3 THEN dy.dyThirdQuarterLocked WHEN ssp.SSP <> 0 AND dm.dmQuarter = 4 THEN dy.dyFourthQuarterLocked END END AS NotEditable, -- 0 = false, -1 = true CASE WHEN ss.suRowType IN('S','P','TT','PP','DD') THEN 1 ELSE 0 END AS Header FROM tbTimeSubSection tss JOIN tbSubSection ss ON tss.suID = ss.suID JOIN tbRegion re ON re.reID = ss.reID JOIN tbTime ti ON tss.tiID = ti.tiID JOIN tbDocumentYear dy ON dy.dyDocumentYear = ti.tiFinancialYear JOIN tbDocumentMonth dm ON dm.dmDocumentMonth = ti.dmDocumentMonth JOIN tbSSP ssp ON ssp.suID = ss.suID AND ssp.dyDocumentYear = dy.dyDocumentYear LEFT JOIN ( SELECT re.reID, re.reRegion, re.reDisplayRegion FROM tbRegion re JOIN ( SELECT distinct reDisplayRegion FROM tbregion re JOIN tbUser u ON re.reID = u.reID WHERE u.usUserName IN (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER))) AND u.usDeleted = 0 ) tre1 ON re.reDisplayRegion = tre1.reDisplayRegion ) UserRegionBySection ON UserRegionBySection.reID = ss.reID LEFT JOIN ( SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER)) as UserName, dbo.udfGetIsUserAllRegions() AS AllRegions ) UserRegionAll ON UserRegionAll.UserName = (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER))) LEFT JOIN ( SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER)) as UserName, CASE WHEN dbo.udfGetUserRole() = 'Administrator' THEN 1 ELSE 0 END AS IsAdmin ) UserAdmin ON UserAdmin.UserName = (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER))) LEFT JOIN tbUserSection us ON us.seID = ss.seID AND us.usUserName = (SELECT SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER))) GO ----------------- |
Works: | SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ALTER View [dbo].[vwKeyValues] AS SELECT tss.tiID, tss.suID, tss.tsValue, ss.suNumber AS Number, ss.suOrder, ss.suSectionOrder, ss.seID, ss.suRowType, ss.suUpdatable, ssp.SSP, dy.dyLocked, dy.dyFirstQuarterLocked, dy.dySecondQuarterLocked, dy.dyThirdQuarterLocked, dy.dyFourthQuarterLocked, LEFT(ss.suDescn + CASE WHEN suRowType IN('T','TT') THEN ' Total' WHEN ss.suRowType IN('PP') THEN ' Percent' WHEN ss.suRowType IN('P') THEN ' Percent' WHEN ss.suRowType IN('DD') THEN ' Completed' WHEN ss.suRowType IN('D') AND CHARINDEX('%',ss.suDescn) > 0 THEN ' Completed' ELSE '' END + REPLICATE(' ',130),130) AS Descn, re.reDisplayRegion AS Region, CASE WHEN NOT ss.suRowType IN ('D','T') OR ss.suUpdatable = 0 OR dbo.udfGetDoesUserHaveRegionRights(re.reDisplayRegion) = 0 OR dbo.udfGetDoesUserHaveSectionRights(ss.seID) = 0 THEN 1 ELSE CASE WHEN ssp.SSP = 0 THEN dy.dyLocked WHEN ssp.SSP <> 0 AND dm.dmQuarter = 1 THEN dy.dyFirstQuarterLocked WHEN ssp.SSP <> 0 AND dm.dmQuarter = 2 THEN dy.dySecondQuarterLocked WHEN ssp.SSP <> 0 AND dm.dmQuarter = 3 THEN dy.dyThirdQuarterLocked WHEN ssp.SSP <> 0 AND dm.dmQuarter = 4 THEN dy.dyFourthQuarterLocked END END AS NotEditable, -- 0 = false, -1 = true CASE WHEN ss.suRowType IN('S','P','TT','PP','DD') THEN 1 ELSE 0 END AS Header FROM tbTimeSubSection tss JOIN tbSubSection ss ON tss.suID = ss.suID JOIN tbRegion re ON re.reID = ss.reID JOIN tbTime ti ON tss.tiID = ti.tiID JOIN tbDocumentYear dy ON dy.dyDocumentYear = ti.tiFinancialYear JOIN tbDocumentMonth dm ON dm.dmDocumentMonth = ti.dmDocumentMonth JOIN tbSSP ssp ON ssp.suID = ss.suID AND ssp.dyDocumentYear = dy.dyDocumentYear GO |