I manage a system with an Excel front end and an Access-compatible back-end, which is updated via code. I use Excel VBA with an ADO library to execute SQL statements to update the database.
My organisation has decided to move all files to SharePoint.
I have been unable to find a way of dynamically connecting to Access databases on the SharePoint system. I asked a question, but the answer was that it was not possible. Within that answer, it was mentioned that SharePoint Lists work similarly to a database.
Wherever possible, I would like to preserve as much functionality as I can from my existing VBA code. I use VBA to construct strings of SQL, which I send to the database via an .execute command.
Will I be able to execute SQL statements on a SharePoint List, once I connect to it? I found this answer, which hints at what I am trying to do but does not address it precisely.
Certainly. However, as far as I know, this is all uncodumented, and SQL support is extremely limited.
You can expand on the following:
Public Sub Update_some_list(ListId As String, SharePointSite As String)
Dim c As New ADODB.Connection
c.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;DATABASE=" & SharePointSite & ";LIST=" & ListId
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM [" & ListId & "]", c, adOpenDynamic, adLockOptimistic
rs.AddNew
rs.Fields!SomeField.Value = "Some value"
rs.Update
End Sub
Where ListId is the list GUID including curly braces (can be found in the URL when changing list settings) and SharePointSite is the URL to the SharePoint site.
I haven't had luck with anything but a basic SELECT, so I recommend doing CRUD through the recordset, and doing any joining/filtering/pivoting in code (or not at all). But I haven't done that much work, you might get further than me.
As noted in the comments, you can also link the SharePoint site in an Access database, which will have much more extensive SQL support, more convenient names, and documented behaviour.