How to Connect 32-Bit Access Data with 64-Bit Excel VBA
Question details
Users need to interact with a 32-bit Access database using VBA macros in a 64-bit version of Excel, but face connection failures due to architecture mismatch.
- Product
- Excel, Access
- Device & OS
- not provided
- Scenario
- Running existing Excel VBA macros that connect to an Access database (.mdb or .accdb) after upgrading Microsoft Office from 32-bit to 64-bit.
- Observed behavior
- The VBA macro stops working and fails to automate the database, throwing errors such as Error 3024 when using methods like Workspaces(0).OpenDatabase.
Verify the exact bitness of your Excel application by navigating to File > Account > About Excel to confirm it is running the 64-bit version.
Install Matching 64-Bit Database Engine Drivers
The most direct method to resolve automation errors is to ensure your database drivers match the 64-bit architecture of your Excel application.
Components of different bitness cannot be automated directly. By installing the 64-bit Access Database Engine, your 64-bit Excel VBA code can successfully communicate with the database file.
Visit the official Microsoft download center and search for the 'Microsoft Access Database Engine 2016 Redistributable'. Click Download and explicitly select the 64-bit executable (AccessDatabaseEngine_X64.exe).
Run the downloaded executable file and follow the on-screen installation prompts to install the 64-bit drivers on your system.
Open your Excel workbook, press ALT + F11 to open the VBA Editor, and locate your database connection string. Change the old 32-bit provider (e.g., Microsoft.Jet.OLEDB.4.0) to the 64-bit provider (Provider=Microsoft.ACE.OLEDB.12.0; or 16.0).
Use a CSV Export Workaround
If you cannot modify your system's drivers or install a 64-bit database engine, use Access to export the data to a CSV file as an intermediate step.
Try WPS Office for Seamless Macro Compatibility
If you frequently encounter compatibility issues or complex driver requirements after Office updates, consider switching to WPS Office. It provides robust VBA support, high compatibility with Excel macros, and a lightweight installation to simplify your workflow.
- 1. Download the Installer: Navigate to the official WPS Office website and click the Free Download button.
- 2. Install WPS Office: Run the downloaded installer file and follow the quick setup instructions.
- 3. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm file to seamlessly run your VBA code.
Frequently Asked Questions
Why does Error 3024 occur when automating Access from Excel?
Error 3024 typically occurs when the database file cannot be found, or when there is a mismatch between the bitness of the database engine (like a 32-bit driver) and the calling application (64-bit Excel).
Can a 64-bit Excel application use the 32-bit Jet OLEDB provider?
No, 64-bit applications cannot utilize 32-bit OLEDB providers. You must install the 64-bit ACE OLEDB provider (Access Database Engine) to connect to databases from a 64-bit installation of Excel.
How do I format my VBA connection string for 64-bit Office?
You need to replace the older Jet provider with the ACE provider. Use 'Provider=Microsoft.ACE.OLEDB.12.0;' or 'Provider=Microsoft.ACE.OLEDB.16.0;' in your VBA connection string after installing the 64-bit Access Database Engine.
