0

Several years ago, I coded from scratch a complex Excel-based system for my organisation, which used an Access-compatible database as a back-end. This was in response to the broken Excel "shared spreadsheet" functionality, which would frequently crash and cause data corruption or loss.

The working system was (and still is) stored on our shared network drive.

It has now been decided that the shared network drive will be decommissioned, and all shared files will be on SharePoint. For the most part, this works well, and all other files have successfully been transferred. However I have not yet managed to find a way to make my system (Excel front end and Access back end) work with it.

When I try to run:

Dim LocalCnn As New ADODB.Connection
With LocalCnn
    .Provider = CheckProvider(strPath:=DBPath)
    Select Case Mode
        Case "RW"
            .Mode = adModeReadWrite
        Case "RO"
            .Mode = adModeRead
    End Select
    .Open ConnStr ' ERROR LINE
End With

.. then I get an error

ConnStr evaluates to:

Data Source=https://myorganisation.sharepoint.com/sites/proc2126/Shared Documents/Archive/Test.xlsmDB_ADMIN.mdb;Jet OLEDB:Database Password=mypassword

It is possibly something to do with the space in the file path? This is not something I can do anything about, as the highest directory I have access to is "Shared Documents" (with the space). The exact error message I get is:

Run-time error '-2147467259 (80004005)':

Automation error

Unspecified error

I have tried to Google the problem, connecting to a database file using VBA on SharePoint, but it seems to be inconclusive whether it's possible.

To clarify: this needs to work 100% within SharePoint, without having to download files to the local drive first.

Grateful for any advice you can provide!

NOTE: I am NOT using the MS Access program at all. This question has been tagged with ms-access because the mdb files are Access-compatible, that's all. I'm using an Excel front end, but using VBA to directly connect to the database files, bypassing the Access software entirely.

Chris Melville
  • 1,476
  • 1
  • 14
  • 30

1 Answers1

2

Tough luck! Access is only supported on local drives or SMB shares with leasing disabled, and previous options to run Access on SharePoint have been removed (since 2010 afaik).

You can directly migrate the data from Access to SharePoint lists (Database tools -> Move data -> SharePoint), and then use the undocumented WSS option of the ACE OLEDB provider to connect to SharePoint lists directly. See connectionstrings.com on the ACE OLEDB provider at the very bottom.

I highly recommend not doing this, and either get your organization to get a decent RDBMS that can be used as a backend (best) or get them to not fully remove the SMB share. Migrating an application to SharePoint lists will come with substantial slowdowns, limitations in what queries can run, and general misery, even though it can sort-of work (speaking from experience, unfortunately).

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • I'm not using MS Access at all. I'm using Excel with VBA to DIRECTLY connect to the database files. I only tagged this with ms-access because the database files are Access compatible. Sorry - I should have made that more clear in my question. – Chris Melville Nov 23 '22 at 12:55
  • I get that. It's not relevant to my answer. Access as in storing data in .mdb or .accdb files using the Access database engine is still not supported on anything but local drives or SMB shares. – Erik A Nov 23 '22 at 12:56
  • Thanks. Is there any external source you can show me which explicitly states that you can not establish live connections to MDB files in SharePoint? That would be helpful. – Chris Melville Nov 23 '22 at 13:56
  • 1
    Nope, since documentation generally states what is supported, not what isn't, and the Access Database Engine also is poorly documented (the proposed solution to use WSS in the connection string is not documented by Microsoft and sparsely mentioned elsewhere). But I didn't get my reputation here by spreading nonsense. – Erik A Nov 23 '22 at 14:10
  • I'm unfamiliar with WSS, and your link points to "SharePoint lists", which I'm also unfamiliar with. It's a regular .mdb file, entirely created by the JET engine with Excel VBA. Do you think there is any chance I can do wat I want to do? – Chris Melville Nov 23 '22 at 15:01
  • Yes, if you have Access installed, you should be able to open the database in Access and do an easy migration from Access tables to SharePoint lists (which are essentially online tables in SharePoint that support multiple users at the same time, barring any complex data such as binary objects), then check the list identifier each table has, use WSS and adjust table names to SharePoint IDs. There are often complications such as SQL support being limited and weird compliance fields showing up, but those should be manageable. – Erik A Nov 23 '22 at 15:09
  • 1
    Thanks Erik A - I'll have to Google how to migrate tables to SharePoint lists! In the meantime, take my up-vote - and watch out for any follow up questions I may have when I try to learn how to do it :) – Chris Melville Nov 23 '22 at 15:22
  • Please see my follow-up question here: https://stackoverflow.com/questions/76054880/is-it-possible-to-update-records-in-a-sharepoint-list-by-sql-via-vba-ado – Chris Melville Apr 19 '23 at 13:09