MS Access中用VBA-ADO更新SQL Server视图的权限问题及解决

问题场景 #

为SQL Server数据表tbl_tablename创建了限定可编辑行的视图vw_tablename_editable，创建语句如下：

CREATE VIEW vw_tablename_editable 
AS 
    SELECT tablename_id, tablename_fieldtoupdate 
    FROM dbo.tbl_tablename 
    WHERE table_criteriafield > 1000 -- 1000为判定阈值

正常运行情况 #

• 仅拥有视图UPDATE权限的用户，直接执行UPDATE语句更新视图时，数据可正常修改
• 使用DAO链接表在Access中查看、修改数据均正常

ADO更新报错 #

使用Access VBA的ADO对象更新视图时（无论绑定控件还是手动代码更新），触发权限错误。示例代码：

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset
        
With rs
    Set .ActiveConnection = connectionstr            
    .Source = "SELECT tablename_id, tablename_fieldtoupdate FROM vw_tablename_editable " & _ 
              "WHERE tablename_id = " & id_ofrecordtobeupdated
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open
End With 

rs!tablename_fieldtoupdate = "new value"
rs.Update

报错信息：

The UPDATE permission was denied on the object 'tbl_tablename', database 'mydatabase', schema 'dbo'

补充说明：

• ADO执行SELECT查询时无需访问底层表权限即可正常运行
• 授予用户底层表或dbo架构的直接UPDATE权限可临时解决问题，但需避免此操作
• 以DBO身份登录时，Access绑定控件和VBA手动更新均正常
• 尝试创建索引视图无效果

解决方法 #

在视图创建语句中添加**WITH VIEW_METADATA**选项，修改后的视图创建语句：

CREATE VIEW vw_tablename_editable 
WITH VIEW_METADATA
AS 
    SELECT tablename_id, tablename_fieldtoupdate 
    FROM dbo.tbl_tablename 
    WHERE table_criteriafield > 1000 -- 1000为判定阈值

添加该选项后，ADO更新视图的操作可正常执行，无需授予用户底层表的直接更新权限。

内容的提问来源于stack exchange，提问作者Daniel