Fix Excel VBA ADO Error: Database or Object Is Read-Only
Question details
The user encounters a 'Database or object is read-only' error when trying to open a workbook from a local hard drive using an ADO connection in an Excel VBA macro.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Running a VBA macro to execute an ADO connection to a local Excel workbook.
- Observed behavior
- The macro throws a read-only error when accessing files from a local drive, despite the exact same code successfully connecting when the file is stored on a USB drive.
Ensure that the target Excel workbook is fully closed and not currently locked by another background process, user, or active Protected View window.
Update to the ACE OLEDB Provider
Mixing older Jet providers with modern Excel files can cause read-only errors. Standardizing to the Microsoft ACE provider resolves connection and architecture mismatches.
The Microsoft.Jet.OLEDB.4.0 provider is an older 32-bit driver designed for legacy formats (.xls). For modern Excel 365 environments and .xlsx/.xlsm files, you must consistently use the ACE OLEDB provider.
Press ALT + F11 in Excel to open the VBA Editor and locate your ADO connection script.
Change your connection code from Provider=Microsoft.Jet.OLEDB.4.0 to the modern equivalent: Provider=Microsoft.ACE.OLEDB.12.0;.
Ensure your connection string includes the correct extended properties for modern Excel files, formatted as: Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";.
In your code, avoid using ActiveWorkbook as it may reference the wrong file if multiple windows are open. Instead, use ThisWorkbook.FullName to strictly target the workbook executing the macro.
Modify Folder Permissions and Read-Only Attributes
If the macro works on a USB drive but fails on your local C: drive, the local folder's security settings may be restricting write access.
Relocate the File from OneDrive Synced Folders
Active OneDrive synchronization can temporarily lock files in the background, preventing ADO from establishing a writable connection.
Upgrade Your Productivity with WPS Office
Troubleshooting deep VBA and ADO errors in Microsoft Office can be highly time-consuming. Consider WPS Office as a lightweight, fast, and completely free alternative that seamlessly supports Microsoft Excel formats, ensuring your workflows remain uninterrupted.
Frequently Asked Questions
Why does my ADO code work perfectly on a USB drive but fail on my local drive?
This commonly happens because USB drives (often formatted as FAT32 or exFAT) have less restrictive security permissions compared to a local NTFS drive. Folder security settings, user permissions, or active OneDrive synchronization on your local drive may lock the file or restrict ADO's write access.
What is the difference between the Jet.OLEDB and ACE.OLEDB providers?
Microsoft.Jet.OLEDB is a legacy 32-bit provider designed for older Excel formats (.xls). Microsoft.ACE.OLEDB is the modern engine required to connect to current Excel formats (.xlsx, .xlsm) and supports both 32-bit and 64-bit Office environments.
How can I avoid locking the workbook when using ADO?
To prevent locking issues, ensure that the workbook isn't opened in Protected View. Always define your ADO connections strictly to close immediately after the query executes, and avoid updating open worksheets using ADO if Excel is simultaneously writing to them.
How do I correctly reference my current workbook path in ADO?
Avoid using ActiveWorkbook.FullName, as it can lead to errors if the user clicks a different open workbook while the macro runs. Instead, use ThisWorkbook.FullName to explicitly reference the exact workbook where your VBA code resides.
