VBA & Macro Problems

How to Fix Excel VBA ACE OLE DB Joins Failing Between External Files

Natalie Taylor Sep 29, 2026 868 views

Question details

Users need to resolve errors when running VBA macros that use ACE OLE DB queries to join external Excel or CSV files.

How to Fix Excel VBA ACE OLE DB Joins Failing Between External Files

Product: Microsoft Excel
Device & OS: not provided
Scenario: Executing a VBA macro that relies on ACE OLE DB to perform SQL joins across external workbooks or CSV files located in different folders.
Observed behavior: The query fails to execute because recent Office security policies block remote table references, whereas joins performed within the same workbook still function correctly.

Before you start

Verify that your external Excel or CSV files are from trusted sources, and ensure you have administrative privileges if you plan to modify Office security or registry settings.

Solution 1Recommended

Use Power Query Instead of VBA ADODB

Power Query is the modern, secure, and officially supported method to combine external Excel or CSV files without triggering ACE OLE DB security blocks.

Microsoft heavily restricts remote table references in ADODB queries for security reasons. Transitioning to Power Query eliminates the need to weaken your security settings while offering a robust interface for merging data.

Open Get Data Menu

In Excel, navigate to the Data tab on the ribbon and click on Get Data, then choose From File and select From Workbook or From Text/CSV depending on your source format.

Import External Sources as Connections

Select your external file and click Import. In the Power Query Editor, load the data by selecting Close & Load To..., and choose Only Create Connection.

Merge Queries

Go back to the Data tab, click Get Data, hover over Combine Queries, and select Merge. Choose the connections you just created to join your external files securely.

Best Practice: Using Power Query is the recommended long-term solution as it avoids security policy conflicts and handles large datasets more efficiently than VBA ADODB.

Solution 2

Import External Data into Local Worksheets First

Bypass the external join restriction by pulling all necessary data into separate sheets within the same workbook before executing the join.

Solution 3

Modify the AllowQueryRemoteTables Registry Setting

Adjust Office security policies to allow remote table queries. This should only be done in highly trusted environments.

Free Microsoft Office alternative

Experience Seamless Data Management with WPS Office

If Microsoft Excel's restrictive security policies are disrupting your workflow, try WPS Office. It provides a lightweight, highly compatible alternative for analyzing and merging data without the heavy legacy restrictions.

1. Download and Install WPS Office: Visit the official WPS website, download the free installer, and complete the lightweight setup process.
2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing .xlsx or .csv files seamlessly with full formatting preserved.
3. Manage Data Easily: Utilize built-in data processing features or run your trusted VBA scripts without navigating complex registry security blocks.

Highly compatible with Microsoft Excel (.xlsx, .csv) formats.Built-in advanced data merging and filtering tools.Supports standard VBA macros for automating your daily tasks.Free, lightweight, and fast performance on any device.

microsoft office alternative - wps office

Frequently Asked Questions

Why did my ACE OLE DB VBA script suddenly stop working?

Recent Microsoft Office security updates restrict ADODB joins between external Excel or CSV files to prevent malicious remote table references. These updates enforce policies that block the query from executing across different folders.

What is the AllowQueryRemoteTables setting?

It is a security policy or registry key in Microsoft Office that determines whether the Access Database Engine (ACE OLE DB) can execute queries referencing external tables or files. Disabling it blocks remote joins.

Can I still join tables located in the same workbook using VBA ACE OLE DB?

Yes. The security policies typically only block joins across different external folders or workbooks. Queries executed on tables or sheets within the same workbook will continue to function normally.

Are there prerequisites for the Access Database Engine to work?

You must ensure that the Microsoft Access Database Engine (ACE OLE DB provider) is installed and updated to a version that matches your Office architecture (32-bit or 64-bit).

Excel & Data Problems