locked
Trouble Inserting Into Sharepoint Access DB with ADODB connection RRS feed

  • Question

  • I am stuck on this after exhausting other resources. The code below does (should do) the following:(

    a) creates an insert command for sql (strSQL)

    (b) connects to a sharepoint site using an ADODB connection

    (c) executes the sql command

    This all worked when the accdb was stored locally. But once the table was moved to sharepoint, the execute command is erroring out ("Syntax Error in Insert INTO statement"). I am 99% sure that the issue is that I have the wrong GUID for the table. The connection opens fine but I can't locate the correct table.

    According to these <a href="http://www.connectionstrings.com/sharepoint" target="_blank">instructions</a>, one refers to the table or list name by GUID while the insert statement is "INSERT into List" or "INSERT into Table" (I tried both). I pulled this GUID from the accdb documenter but am still stuck:

    GUID={39885376-6EF9-4EF7-9D0B-E040FF2FCCC2D}

    I really appreciate any help

        strSQL = "INSERT INTO Table(Reviewer, FeedbackDate, Project) " & _
        "VALUES (" & txtReviewer & ", " & txtFeedbackDate & ", " & txtProject & ")"
           
        Const URL = "https://thisconsultinggroup.com/lc/b...ck%20Database/"
      
        strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIDS=Yes;" & "Database=" & URL & ";Table={39885376-6EF9-4EF7-9D0B-E040FF2FCCC2D};"
        
        Set cnn = New ADODB.Connection
        cnn.Open strConnection
        
        cnn.Execute strSQL, lngSuccess
        cnn.Close

    • Edited by J. Scranton Wednesday, January 23, 2013 6:29 PM fixed html
    Wednesday, January 23, 2013 6:24 PM

Answers

  • Andrey, thanks so much.

    Not sure why but this string is now working:

    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=" & dbURL & ";List=" & listGUID & ";"

    I am marking this as the answer but the entire thread is obviously instructive.

    • Marked as answer by J. Scranton Thursday, January 31, 2013 7:44 PM
    • Edited by J. Scranton Thursday, January 31, 2013 7:48 PM marked as answer
    Thursday, January 31, 2013 6:54 PM

All replies

  • The connection string is correct but for DAO. It will work as expected if you link SharePoint list as you usually do with any other linked tables. And the conneciton string in this case will be exactly like you provided. AFAIK, there is no way to connect to SharePoint backend via ADO.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Wednesday, January 23, 2013 6:30 PM
  • The connection seems to work when I try to debug it (cnn.State= adStateOpen). But, I can't seem to write to the table.

    Wednesday, January 23, 2013 6:40 PM
  • These are the instructions I followed:

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strConn As String
    Dim strSQL As String
    ' Substitute the URL of your SharePoint server and the GUID of your SharePoint list
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://sharepointdevserver.connectionstringexamples.com/Documents/;LIST={32427232-3235-22A5-32DF-ACAD3832CA3};"
    ' Substitute the correct username and password
    cnn.Open ConnectionString:=strConn, UserID:="MyUser", Password:="topsecret"
    strSQL = "SELECT * FROM [List]"
    rst.Open Source:=strSQL, Connection:=cnn

    Wednesday, January 23, 2013 6:59 PM
  • So I may be mistaken, of course. But what ADO definitely does not support are multivalued and Attachment fields. Maybe you may answer why you are using ADO? All you need to create a linked table and work (almost) like you do with any other table. If you still want to use ADO, unfortunately, I'm not a helper here. 

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Wednesday, January 23, 2013 7:22 PM
  • Andrey, first, thanks for your help.

    The answer is that the accdb is actually integrated into Sharepoint using access web services. For all purposes, it is a web database hosted on Sharepoint. The goal is to host all the tables and forms on Sharepoint and not link them to client side Access.

    The secondary goal, however, is to be able to add records to the Sharepoint Web Database remotely. The code I posted above is a snippet from a document that a user fills out. When the document is saved and closed, it inserts a new record into the Access Web Database.

    Finally, are you saying that I can accomplish this using DAO only? Or, that I can accomplish this using DAO AND by linking my tables? [B/c the latter isn't really an option]

    Wednesday, January 23, 2013 7:41 PM
  • Ah, this code is not in the Access .accdb file? If so, I understand your requirements. 

    By using Access (I mean linked tables) you can do everything with SharePoint lists. But it still should be Access. I did a quick googling and saw many people confirm your issue. BTW, some people say to use IMEX=0 to avoid read-only mode, but I see you already do and still no luck.

    The final question, I think, is where this code snippet from your first post comes from? What' this application? 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Wednesday, January 23, 2013 8:38 PM
  • Right. This is not in the .accdb file.

    The snippet is a private sub in the Document_Close() handler of a Microsoft Word Document.

    I still think the issue is with the GUID. In the code I am using the insert statement is "INSERT INTO Table(Field1, Field2, Field) Values(Value1,Value2,Value3) where Table is the GUID. I also tried it as "List" but that didn't work either (although I got different error message).

    Since I can open the connection, is it possible to loop through the Sharepoint site and return the GUID of any tables that are found?

    Wednesday, January 23, 2013 8:44 PM
  • If it is about GUID, things  to check and try:

    1. Try GUID of the list itself, try to create a view and use its guid. Just in case you don't know, the link to the list settings is still available even for Access Web Databases. Smth like https://thisconsultinggroup.com/lc/b...ck%20Database/_layouts/listedit.aspx?List=%7B39885376-6EF9-4EF7-9D0B-E040FF2FCCC2D%7D I think it's a kind of answer for your last question about guids. To view all content use https://bla-bla/_layouts/viewlsts.aspx

    2. The strange thing about syntax (!) error. Could you post the original SQL statement? Maybe the issue is easier than it seems to be.

    3. Different error message: what did it say? 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Wednesday, January 23, 2013 9:17 PM
  • Here is the entire sql command

        strSQL = "INSERT INTO Table(Reviewer, FeedbackDate, Project, QCer, TeamLeaders, NoDefect, ResponsiveDefect, PrivilegeDefect, IssueDefect, KeyDefect, ConfidentialityDefect, ExampleFeedback, ProtocolSections, BriefComments) " & "VALUES (" & txtReviewer & ", " & txtFeedbackDate & ", " & txtProject & ", " & txtQCer & ", " & txtTeamLeaders & ", " & txtNoDefect & ", " & txtResponsiveDefect & ", " & txtPrivilegeDefect & ", " & txtIssueDefect & ", " & txtKeyDefect & ", " & txtConfidentialityDefect & ", " & txtExampleFeedback & ", " & txtProtocolSections & ", " & txtBriefComments & ")"

    Here is the error message when I use "INSERT INTO List( . . . " instead of Table:

    The Microsoft Access database engine could not find the object 'List'. Make sure the object exists and that you spell its name and the path name correctly. If 'List' is not a local object, check your network connection or contact the server administrator.

    I will check the GUID for the list

    Wednesday, January 23, 2013 10:28 PM
  • So, I defiinitely have the correct GUID for the list now: {603af06c-c791-435c-9c1f-b0a9f7bc50b0}

    Here is the SQL statement and the string connection:

    INSERT INTO List(Reviewer, FeedbackDate, Project, QCer, TeamLeaders, NoDefect, ResponsiveDefect, PrivilegeDefect, IssueDefect, KeyDefect, ConfidentialityDefect, ExampleFeedback, ProtocolSections, BriefComments) VALUES ('Sample Name, '1/16/2013', 'Test', 'Sample Name, 'Sample Name, 'Select', 'Responsiveness Defect', 'A. Privilege Defect', 'Issue Defect', 'Key/Hot Defect', 'Confidentiality Defect', 'Feedback', 'Protocol', 'Comments')

    Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIDS=Yes;Database=https://thisconsultinggroup.com/lc/bd/drfi/Feedback%20Database/;List={603af06c-c791-435c-9c1f-b0a9f7bc50b0};

    Wednesday, January 23, 2013 10:46 PM
  • I was able to successfully insert a record into a SharePoint list using your code. It seems to be really syntax error. First of all, date should be enclosed with # signs. And string values - with " signs. So, it should be like this:

    VALUES (""" & txtReviewer & """,#" & txtFeedbackDate & "#,""" & txtProject... etc.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Thursday, January 24, 2013 8:52 AM
  • So, the strSQL is fixed but the underlying problem is not: the connection can not find the list (even though I definitely have the correct GUID now). If I write the strSQL as "insert into Table", I still get the syntax error message. If I write the strSQL as "insert into List", I get the cannot find the list error.

    If the code worked for you, then I am not sure what is happening on my end? Can you post the code that worked for you?

    Thursday, January 24, 2013 1:33 PM
  • So here is where I am at with everything:

    (a) GUID is now correct

    (b) strSQL is now correct

    (c) Error is still occuring.

    I now think the issue is related to Sharepoint Web Access DBs. So, when I set the database to this:

    https://thisconsultinggroup.com/lc/bd/drfi/Feedback%20Database/

    I get the error described above (that the list can't be found).

    I tried set the URL Const to this:

    https://thisconsultinggroup.com/lc/bd/drfi/Feedback%20Database/Lists/FeedbackDB/

    But, in that case I get a "cannot connect to Sharepoint Site. Try again later."

    If that latter URL is correct, then I likely need to fix the Sharepoint settings.

    Friday, January 25, 2013 1:26 PM
  • Sorry for the late response, I had no access to this forum.

    The code I used:

    Public Sub testSP() 
    Dim cnn As Object, strConn$, strSQL$ 
        Set cnn = CreateObject("ADODB.Connection") 
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes; DATABASE=siteURL;List=listGUID;" 
        cnn.Open ConnectionString:=strConn 'I use Trusted Connection 
        strSQL = "INSERT INTO List(Region, Branch, MyDate) VALUES(""Test Region"",""Test Branch"",#1/16/2013#)" 
        cnn.Execute strSQL 
        cnn.Close 
    End Sub 

    Where site URL is exactly the path to the site (!), not to the list of lists as you tried in you previous post.

    For example, http://biztoolbox.sharepoint.com/TeamSite

    Then to find GUID:

    find the list you need, on the Ribbon: List Tools -> List -> List Settings

    The URL would be http://biztoolbox.sharepoint.com/TeamSite/_layouts/listedit.aspx?List=%7B428B8326-048E-4944-B234-0E4F444A5080%7D it's GUID indeed.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Friday, January 25, 2013 4:59 PM
  • Andrey,

    Just wanted to thank you for your help. Still not working although the code is correct. For a reason I still don't understand, the List (even though the GUID is correct) can still not be found. I even created a new linked list and tried that and it still can't find the object.

    But, I am glad the code works. I will keep searching for an answer as to why the object can't be found and circle back to close this thread when/if I do.

    Monday, January 28, 2013 3:04 PM
  • Just an update. I thought at first that List didn't have the contribute level permissions but it turns out that it does. My current best guess is that I need to include password & ID parameters (the strConnection is definitely correct).

    So, I am currently using this to open the connection:

    cnn.Open ConnectionString:=strConnection, UserID:="MyUserName", Password:="MyPassword"

    However, when I do so, I am now getting this error message:

    "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."

    Since this is a secured DB, I think this means I need to point the registry key to the full working group pathway but I am not sure how to do so.

    Any help?

    Thursday, January 31, 2013 4:11 PM
  • Don't you use Windows Authentication for your SharePoint site? 

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Thursday, January 31, 2013 4:48 PM
  • I think the answer is no but I am double checking.
    Thursday, January 31, 2013 6:05 PM
  • It's NTLM not IWA.
    Thursday, January 31, 2013 6:34 PM
  • OK, let's try with Office 365. The previous success was with Access 2010 and local intranet SharePoint 2007.

    This test is with Access 2013 and SharePoint Online within Office 365 subscription (2010 version).

    I downloaded a sample Northwind Traders template and published it here http://biztoolbox.sharepoint.com/Northwind%20Traders

    Then go to Options - Site Permissions 

    On this page All site content is available, click it. Let's choose list Customers. Go to its settings and get its GUID http://biztoolbox.sharepoint.com/Northwind%20Traders/_layouts/listedit.aspx?List=%7B9E7A2140-C117-413C-86F8-1CF6E61060B7%7D - {9E7A2140-C117-413C-86F8-1CF6E61060B7}

    Now open smth with VBA, I use a blank Access database. The code is:

    Public Sub testSP()
    Dim cnn As Object, strConn$, strSQL$
        Set cnn = CreateObject("ADODB.Connection")
        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes; DATABASE=http://biztoolbox.sharepoint.com/Northwind%20Traders;List={9E7A2140-C117-413C-86F8-1CF6E61060B7};"
        cnn.Open ConnectionString:=strConn
        strSQL = "INSERT INTO List(Company, LastName, FirstName) VALUES(""My Company"",""Smith"",""John"")"
        cnn.Execute strSQL
        cnn.Close
    End Sub

    I didn't specify my credentials, right after pressing Enter button to execute this Sub the authentication form from SharePoint site appeared. I entered my login and password and that's all. The result is:

    I also checked Keep me signed in, thus when I executed the routine second time, it appended a record immediately w/o asking for credentials. I mean you may give it a try and DO NOT specify credentials in code. Let the user do it manually. Since there are no examples with credentials at connectionstrings.com for Sharepoint, I suppose this is by design.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Thursday, January 31, 2013 6:43 PM
  • Andrey, thanks so much.

    Not sure why but this string is now working:

    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=" & dbURL & ";List=" & listGUID & ";"

    I am marking this as the answer but the entire thread is obviously instructive.

    • Marked as answer by J. Scranton Thursday, January 31, 2013 7:44 PM
    • Edited by J. Scranton Thursday, January 31, 2013 7:48 PM marked as answer
    Thursday, January 31, 2013 6:54 PM
  • I think the connection itself triggers the authentication. Until there are no credentials in cookies, it triggers this page for FBA (form based authentication), of course. That's how I understand it. And it's very interesting that even ADO connection is detected and this page is offered. Try this, I'm just not sure it will be so for any application. But if it will, I hope this is exactly the solution for your case.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Thursday, January 31, 2013 7:06 PM
  • I used this solution in Excel 2013 to INSERT a new record into SharePoint 2010 List.  This solution has eluded me for a few months, so I truly appreciate the dialogue.  :-)

    Wednesday, October 9, 2013 11:48 AM