title: ADO connection (in VBA) unable to INSERT to Sharepoint list
body: <p>I'm using ADO because I want to write to a Sharepoint list using VBA in Excel.
  Right now I am getting &quot;The Microsoft Access database engine could not find the object 'Isaac Test Excel To Sharepoint', and the code errs on the INSERT line. I suspect it is because of either my site reference being wrong, or my list ID being wrong.</p>
  <p>I don't think my list ID is wrong, because I carefully followed the directions to extract the list ID from the URL that's exposed when you go to List Settings, carefully Replacing the 3 replaceable items as mentioned here: <a href="https://community.nintex.com/t5/Community-Blogs/Obtaining-a-list-id-in-SharePoint-2010-or-2013/ba-p/77664#:%7E:text=Navigate%20to%20the%20list%20and%20click%20List%20Settings.,Guid%20Format%20with%20URL%20encoding" rel="nofollow noreferrer">https://community.nintex.com/t5/Community-Blogs/Obtaining-a-list-id-in-SharePoint-2010-or-2013/ba-p/77664#:~:text=Navigate%20to%20the%20list%20and%20click%20List%20Settings.,Guid%20Format%20with%20URL%20encoding</a>).</p>
  <p>I am passing it in as:</p>
  <pre><code>strSharepointListID = &quot;{3404D534–10CB–4F53–BB9D–37F5612155F1}&quot;
  </code></pre>
  <p>I would like to have concluded, &quot;the connection is correct because the code doesn't err until all the way to the INSERT statement&quot;, but unfortunately I've proved that to be false: If I pass in a totally non-existent Site value, gibberish, the code still doesn't err until all the way at the INSERT statement.</p>
  <p>The name of my list is definitely Isaac Test Excel To Sharepoint</p>
  <p>The site I am passing is like this, with me sanitizing this by replacing some text with &quot;text&quot;: (I've tried all 3 of these):</p>
  <ol>
  <li>strSharepointSite = &quot;https://text.text.text.com&quot;</li>
  <li>strSharepointSite = &quot;https://text.text.text.com/sites/text&quot;</li>
  <li>strSharepointSite = &quot;https://text.text.text.com/sites/text/_layouts/15/start.aspx#/&quot;</li>
  </ol>
  <p>Full code:</p>
  <pre><code>Sub Upd2KPIMember_SP()
      Dim cnt As ADODB.Connection
      Dim mySQL As String
      Dim strSharepointListID As String, strSharepointSite As String
      
      'https://community.nintex.com/t5/Community-Blogs/Obtaining-a-list-id-in-SharePoint-2010-or-2013/ba-p/77664#:~:text=Navigate%20to%20the%20list%20and%20click%20List%20Settings.,Guid%20Format%20with%20URL%20encoding).
      'list ID from sharepoint URL:
      '   %7B3404D534%2D10CB%2D4F53%2DBB9D%2D37F5612155F1%7D
      'list ID after replacing as follows:
      '   %7B3404D534%2D10CB%2D4F53%2DBB9D%2D37F5612155F1}
      strSharepointListID = &quot;{3404D534–10CB–4F53–BB9D–37F5612155F1}&quot;
      strSharepointSite = &quot;[sanitized for SO post]&quot;
      
      Set cnt = New ADODB.Connection
      With cnt
          .ConnectionString = _
          &quot;Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=&quot; &amp; strSharepointSite &amp; &quot;;LIST=&quot; &amp; strSharepointListID &amp; &quot;;&quot;
          .Open
      End With
  
          mySQL = &quot;insert into [Isaac Test Excel To Sharepoint] (column1,column2) values ('col1_val1','col2_val1');&quot;
          cnt.Execute (mySQL)
  
      If CBool(cnt.State And adStateOpen) = True Then cnt.Close
      Set cnt = Nothing
  End Sub
  </code></pre>
  <p>I'm also fairly sure the SQL syntax is good, because the code DID tell me when it was wrong--When at first I used INSERT TABLE instead of INSERT INTO TABLE.</p>