Advice on how to connect to an MDB database on SharePoint from Excel VBA-CodePudding

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.

CodePudding user response：

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).