Access: Views with columns from a udf

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