VBA & Macro Problems

Fix VBA ADO Connection Failures to SQL Server 2019

WPS Content Manager Oct 10, 2026 869 views

Question details

The user is unable to open an ADO connection to a local SQL Server 2019 instance via a VBA macro, despite a successful ODBC connection test.

Troubleshoot VBA ADO Connections to SQL Server 2019

Product: Spreadsheets
Device & OS: not provided
Scenario: Attempting to query or modify a local SQL Server 2019 database by executing a VBA script with an ADO connection string.
Observed behavior: The VBA code fails and throws an error at the ADOConn.Open execution line, while the equivalent ODBC connection works perfectly outside the VBA environment.

Before you start

Verify that your SQL Server service is running and that the 'ODBC Driver 17 for SQL Server' is properly installed in your Windows ODBC Data Source Administrator.

Solution 1Recommended

Correct the Connection String Syntax

Remove invalid characters such as square brackets from the connection string to allow ADO to properly identify the server and database.

A common mistake when writing ADO connection strings in VBA is including placeholder brackets. In the provided failing string, the Server and Database values are enclosed in square brackets (e.g., Server=[CTS-LT01]). The ODBC driver treats these brackets literally, leading to a connection failure because it cannot find a server with that exact bracketed name.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor and locate your connection script.

Locate the Connection String

Find the line defining your connection: ADOConn.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=[CTS-LT01];Database=[CTS_Working];Trusted_Connection=yes;"

Remove Square Brackets

Edit the string to remove the brackets. It should look like this: ADOConn.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=CTS-LT01;Database=CTS_Working;Trusted_Connection=yes;"

Run the Macro

Save your code and execute the macro again to test the ADOConn.Open method.

Syntax Validated: Removing the brackets ensures the ODBC Driver correctly parses your local SQL Server instance name.

Solution 2

Enable TCP/IP in SQL Server Configuration Manager

Ensure that the SQL Server instance allows remote and ADO TCP/IP connections, which might be disabled by default on local installations.

Solution 3

Implement ADO Error Handling for Detailed Diagnostics

Add specific error capturing around your ADO connection open method to retrieve the exact failure reason directly from the ODBC driver.

Run Advanced VBA Macros with WPS Office

WPS Office offers comprehensive VBA support in its advanced versions, allowing you to seamlessly connect to SQL Server databases via ADO, automate repetitive tasks, and write custom macros in a highly compatible environment.

1. Open WPS Spreadsheets: Launch WPS Spreadsheets and open the workbook containing your database macros.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon. If it is hidden, enable it in the Options menu.
3. Launch the VBA Editor: Click 'Visual Basic' to open the built-in VBA editor, paste your corrected ADO connection script, and execute.

Fully compatible with Microsoft Excel VBA syntax and objectsSupports native ADO and ODBC connections to external SQL databasesLightweight architecture for faster macro executionHighly cost-effective alternative for advanced spreadsheet automation

microsoft office alternative - wps office

Frequently Asked Questions

Why does my ODBC connection work, but VBA ADO fails to connect to SQL Server?

This usually happens due to a discrepancy between the DSN configuration and the OLE DB/ADO connection string. In VBA, formatting errors like extra spaces, invalid characters, or bracketed variables in the connection string prevent the driver from locating the target server.

How do I check which version of the ODBC Driver for SQL Server is installed?

Open the 'ODBC Data Source Administrator' in Windows, navigate to the 'Drivers' tab, and check the list for 'ODBC Driver 17 for SQL Server' or other versions. Your VBA connection string must perfectly match the name listed here.

Do I need to include the port number for a local SQL Server 2019 instance?

If you are connecting to a named instance or if SQL Server is not using the default 1433 port, you must include the instance name or port in the connection string (e.g., Server=Localhost\SQLEXPRESS or Server=127.0.0.1,1433).

What does 'Trusted_Connection=yes' mean in the VBA connection string?

It instructs the ODBC driver to use your current Windows credentials (Windows Authentication) to log into the SQL Server database instead of requiring a separate SQL Server username and password.

Excel & Data Problems