VBA & Macro Problems

How to Connect Excel VBA to an Access Database Stored in SharePoint

WPS Content Manager Sep 30, 2026 869 views

Question details

The user needs to establish an ADODB connection from an Excel VBA workbook to an Access database hosted on SharePoint, specifically avoiding OneDrive synchronization.

How to Connect Excel VBA to an Access Database Stored in SharePoint

Product: Excel, Access, SharePoint
Device & OS: not provided
Scenario: Migrating a local or network-hosted Access database to a SharePoint document library while maintaining a direct VBA ADODB connection from an Excel workbook.
Observed behavior: The ADODB connection fails to open using the original UNC path after the Access database is moved to SharePoint.

Before you start

Ensure you have the exact URL of your SharePoint document library and verify that your network allows WebDAV access to translate the web URL into a Windows-readable path.

Solution 1Recommended

Convert the SharePoint URL to a WebDAV UNC Path

Modify your VBA ADODB connection string to use a WebDAV path (DavWWWRoot), which allows Windows to interact with SharePoint as if it were a standard network folder.

Standard ADODB connections cannot natively read `https://` URLs to connect to Access databases. By converting the SharePoint URL into a WebDAV UNC path, the driver can locate the file securely over the network without requiring OneDrive synchronization.

Format the WebDAV Path

Convert your SharePoint URL (e.g., https://domain.sharepoint.com/sites/mySite/Shared%20Documents/Database.accdb) into a WebDAV format: \\domain.sharepoint.com@SSL\DavWWWRoot\sites\mySite\Shared Documents\Database.accdb.

Update the VBA Connection String

Open your Excel VBA Editor (ALT + F11), locate your ADODB connection code, and replace the old Data Source path with the new WebDAV UNC path.

Authenticate with SharePoint

Open Internet Explorer or Microsoft Edge, log into your SharePoint site, and check 'Keep me signed in'. This generates the necessary authentication token for WebDAV to work.

Convert the SharePoint URL to a WebDAV UNC Path

Authentication Expiry: WebDAV paths require active authentication. If the connection drops with an 'access denied' error, you may need to open the SharePoint library in your browser again to refresh the token.

Solution 2

Seek Specialized Help on Developer Communities

Because ADODB connections to SharePoint involve complex authentication protocols and database drivers, posting on specialized developer forums like Stack Overflow is highly recommended if WebDAV fails.

Free Microsoft Office alternative

Experience a Lightweight and Free Office Suite with WPS Office

While complex ADODB connections to SharePoint-hosted Access databases require specialized Microsoft Office environments, WPS Office provides an excellent, highly compatible alternative for everyday spreadsheet and document tasks without the heavy overhead.

1. Download and Install: Visit the official WPS Office website to download the free, lightweight installer for your device.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and instantly open your existing Excel workbooks without formatting loss.
3. Enjoy Seamless Productivity: Edit, format, and save your documents using an interface designed for effortless migration from Microsoft Office.

Highly compatible with Microsoft Excel (.xls, .xlsx, .xlsm) formatsLightweight design ensures fast loading and smooth data processingFamiliar user interface requires no additional learning curveFree alternative to costly subscription-based office suites

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the standard UNC path work for SharePoint?

Standard UNC paths are designed for local area networks (LANs). Since SharePoint is a web-based platform, it requires a WebDAV path (using DavWWWRoot) to translate the HTTPS web URL into a network path format that Windows and ADODB can interpret.

Does WPS Office support VBA macros?

Yes, WPS Office fully supports VBA macros. You can run automated scripts, create custom functions, and interact with data sources using standard VBA syntax just as you would in Microsoft Excel.

Why should I avoid syncing the Access database with OneDrive?

Syncing an Access database (.accdb) via OneDrive can frequently lead to file corruption, synchronization conflicts, and severe record-locking issues, particularly if multiple users attempt to access or modify the database simultaneously.

How do I map a SharePoint document library as a network drive?

Open 'This PC' in Windows File Explorer, select 'Map network drive', choose a drive letter, and paste your WebDAV SharePoint URL. Check 'Connect using different credentials' to securely log in with your SharePoint account.

Excel & Data Problems