Accessing Sharepoint List from Excel/VBA using ADODB connection

[Scotster]

Hi there,

I'm migrating an entire suite of excel files that currently access a database in order to see if a person is allowed access. I use an ADODB connection and it works great. I'm hoping that I can use the "Lists" feature in Sharepoint to copy this access solution but I'm really struggling with finding out the correct syntax.

What I have now is as follows:

Dim cnt As ADODB.Connection
    Dim mySQL As String
    Dim strSharepointListID As String, strSharepointSite As String
   

    strSharepointListID = "{Removed}"
    strSharepointSite = "[https://Website.com/sites/Project/]"
   
    Set cnt = New ADODB.Connection
    With cnt
        .ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=" & strSharepointSite '& ";LIST=FileAccess;"
        .Open
    End With
   
Set rs = New ADODB.Recordset

i = 2
With rs
    .ActiveConnection = cnt
    .Open "SELECT * FROM TABLE"
     For i = 1 To .Fields.Count
         Debug.Print .Fields(i - 1).Name
         Debug.Print .Fields(i - 1).Type
     Next i
End With

The main things I'm unsure of are the Sharepoint site, the "LIST" and with those combined, how to select the "Table" for the record set.

For the site, the full link (redacted) to the table is as follows:

https://website.com/sites/Project/Lists/FileAccess/AllItems.aspx

I have the correct list ID which I got from Sharepoint settings {xxxx-xxxx-xxxx-xxxx} etc.

Typically for the connection I would want to select the "Database" name, in this case I'm not sure what it's called. Is it called Lists? FileAccess (from what I understand) is the Table name.

To summarise:

strSharepointSite = "[https://website.com/sites/Project/]" - I'm unsure what this should be given the link above?
LIST=FileAccess - I really want this to be the DB but unsure how to reference/
.Open "SELECT * FROM TABLE" - I really want this to be the FileAccess table but need the correct List first.

Any help would be greatly appreciated.

 

[RoryA]

I believe that the List and table name are the same.

 

[Scotster]

I believe that the List and table name are the same.

I had all but given up but I saw this reply and I kept plodding along. Firstly, you are absolutely spot on, they are both the same. Secondly, for anyone looking to do something similar, the key is to avoid any brackets or anything in the setup. Solution as follows:

Dim cnt As ADODB.Connection
    Dim mySQL As String
    Dim strSharepointListID As String, strSharepointSite As String
   

    strSharepointListID = "2AB5e630-b635f-4FG6-b6e9-10432ca3712g" 'Changed for posting
    strSharepointSite = "https://Website.com/sites/Project/"
   
    Set cnt = New ADODB.Connection
    With cnt
        .ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=" & strSharepointSite '& ";LIST=" & strSharepointListID & ";"
        .Open
    End With
   
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [" & strSharepointListID & "]", cnt
debug.print rs.Fields.Count

Hopefully of use to someone

 

[RoryA]

Thanks for posting back - I'm sure someone will find it helpful.