title: Updating views with VBA-ADO in SQL Server requires UPDATE privileges on underlying table (MS Access / VBA)
body: <p>I have a problem updating views with ADO in MS Access.</p>
  <p>I created views for my tables, they contain columns and rows that are eligable to be changed. For example, the table <code>tbl_tablename</code> will have the view: <code>vw_tablename_editable</code> with some <code>WHERE</code> clauses to determine wether the the rows should be editable or not.</p>
  <pre><code>CREATE VIEW vw_tablename_editable 
  AS 
      SELECT tablename_id, tablename_fieldtoupdate 
      FROM dbo.tbl_tablename 
      WHERE table_criteriafield &gt; 1000 -- 1000 being some criteria
  </code></pre>
  <p>The permissions work: when I run <code>UPDATE</code> queries on the view as a user with only <code>UPDATE</code> permissions on the view, the information is correctly updated. Also when using DAO linked tables in Access, I can view and modify the data.</p>
  <p>The problem is when I am using ADO and try to update the views, both using the Bound controls and manual updating in VBA.</p>
  <pre><code>Dim rs as ADODB.Recordset
  Set rs = New ADODB.Recordset
          
      With rs
          Set .ActiveConnection = connectionstr            
          .source = &quot;SELECT tablename_id, tablename_fieldtoupdate FROM vw_tablename_editable &quot; &amp; _ 
          &quot;WHERE tablename_id = &quot; &amp; id_ofrecordtobeupdated
          .cursortype = adOpenStatic
          .cursorlocation = adUseClient
          .locktype = adLockOptimistic
         .Open
     End With 
  
  rs!tablename_fieldtoupdate = &quot;new value&quot;
  
  rs.update
  </code></pre>
  <p>The error message I am getting is:</p>
  <blockquote>
  <p>The UPDATE permission was denied on the object 'tbl_tablename', database 'mydatabase', schema 'dbo'</p>
  </blockquote>
  <p><a href="https://stackoverflow.com/questions/4134740/grant-select-permission-on-a-view-but-not-on-underlying-objects">It seems to be related with this question</a>, however, the SELECT statements in ADO just fine without giving the user access to the underlying tables.</p>
  <p>The problem is solved when I give the users direct UPDATE access to the tables or the DBO schema, but I would like to avoid this. When logged in as DBO updating works both with the MS Access bound controls and the manual VBA way.</p>
  <p>I also tried to make an indexed view, but this also does not seem to make a difference.</p>
  <p>Edit</p>
  <p>By suggestion of user @wqw inserting WITH VIEW_METADATA, it now works perfectly!</p>
  <pre><code>CREATE VIEW vw_tablename_editable 
  WITH VIEW_METADATA
  AS 
      SELECT tablename_id, tablename_fieldtoupdate 
      FROM dbo.tbl_tablename 
      WHERE table_criteriafield &gt; 1000 -- 1000 being some criteria
  </code></pre>