2

I manage a complex 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 in order to update the database.

Unfortunately, my organisation has decided to scrap our shared drive, and move all files to SharePoint. This works well for 99% of files. However, I have been unable to find a way of dynamically connecting to Access databases on the SharePoint system. I asked a question here on StackOverflow, but the answer was that it was not possible. Within that answer, it was mentioned that SharePoint Lists work similarly to a database - although they are not identical.

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.

This brings me on to my question. Will I be able to execute SQL statements on a SharePoint List, once I connect to it? I have found this answer, which hints at what I am trying to do but does not address it precisely. Therefore I am asking this explicitly. Thank you.

Chris Melville
  • 1,476
  • 1
  • 14
  • 30
  • maybe you haven't grasp the concept,you link your access app to sharepoint list and use can use all comfort of access even update offline, so there is basically no need for a dynamic approach – nbk Apr 19 '23 at 13:57
  • My users won't have use of the Access app. We're moving the whole thing to SharePoint. We don't use MS Access in the execution of this system at all: I only use it as the developer. – Chris Melville Apr 19 '23 at 14:34
  • it will be the best way to have all comforts and it is fully supported – nbk Apr 19 '23 at 14:58
  • @nbk - Access on SharePoint was the first thing I tried, but I found out that it's not fully supported. It's not possible to establish a live connection to an Access database within SharePoint. If it were, then I would not need to ask this. If you know differently, please let me know! – Chris Melville Apr 25 '23 at 12:07

1 Answers1

2

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thank you! This works for me. One thing, however: ListId is simply the plain text name of the list, at least on my company's SharePoint site. I tried the GUID as suggested, but got an error. Then I tried the plain text name and it worked. Thank you :) – Chris Melville Apr 25 '23 at 11:59
  • Additional: is it possible to use cn.execute in a similar fashion, rather than opening a recordset? – Chris Melville Apr 25 '23 at 12:03
  • I Googled CRUD: It seems to relate to SQL Server. I do not have access to any SQL server. I only have Excel and SharePoint. – Chris Melville Apr 27 '23 at 13:47
  • 1
    As said, I haven't had luck with anything but a basic select, but you're free to try. Apparently, your SharePoint instance behaves different than mine since I am required to use the GUIDs in queries (and [connectionstrings.com](https://www.connectionstrings.com/sharepoint/#ace-oledb-12-0) lists that as required too), so you'll have to rely on experimentation and not what others tell you. CRUD stands for create, read, update and delete, the basic database operations, and are in no way exclusive to SQL server. – Erik A Apr 28 '23 at 07:29