Excel VBA Userform本地连SQLOLEDB正常，远程报错-2147467259(80004005)

Hey Dave, I’ve seen this exact issue dozens of times with SQL Server Express and remote VBA connections—let’s walk through the most likely fixes to get your Save button working across clients:

1. Enable TCP/IP for SQL Server Express & Set a Fixed Port #

Local connections work because they use shared memory, but remote needs TCP/IP enabled (it’s disabled by default in Express editions):

• On your server, open SQL Server Configuration Manager
• Navigate to SQL Server Network Configuration > Protocols for SQLEXPRESS
• Right-click TCP/IP and select Enable
• Double-click TCP/IP, go to the IP Addresses tab:
  • Scroll down to the IPAll section, clear the TCP Dynamic Ports field, and set TCP Port to 1433 (the standard SQL port)
• Restart the SQL Server (SQLEXPRESS) service from Windows Services (services.msc) to apply changes

2. Open Firewall Ports on the Server & Network #

9 times out of 10, the firewall is blocking the connection:

• On the server, open Windows Defender Firewall with Advanced Security
• Create an inbound rule allowing TCP traffic on port 1433
• If you plan to use SQL Browser later, also allow UDP traffic on port 1434
• Don’t forget to check any network firewalls (routers, office firewalls) between the client and server—they need to allow port 1433 too

3. Update Your VBA Connection String for Remote Access #

Local connection strings use localhost or ., but remote needs the server’s actual network address. Here’s a reliable remote string template:

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

' Replace placeholders with your server/database details
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=YOUR_SERVER_NAME_OR_IP\SQLEXPRESS,1433;" & _
                        "Initial Catalog=YourDatabaseName;" & _
                        "User ID=YourSQLUsername;" & _
                        "Password=YourSQLPassword;"
conn.Open

• Use the server’s network name (e.g., OFFICESERVER\SQLEXPRESS) or public IP address
• Adding ,1433 ensures the client connects to the fixed port we set earlier
• Avoid Trusted_Connection=Yes (Windows Auth) for remote unless the client is on the same domain—SQL Server Auth is more reliable for cross-network access

4. Enable Mixed Authentication Mode in SQL Server #

If you’re using SQL Server Auth, make sure the server allows it:

• Open SQL Server Management Studio on the server, connect to SQLEXPRESS
• Right-click the server instance > Properties > Security
• Select SQL Server and Windows Authentication mode
• Restart the SQL Server service to activate this setting

5. Test Connectivity Outside VBA First #

Rule out network issues before debugging your code:

• On the client machine, open Command Prompt and run:

  telnet YOUR_SERVER_IP 1433
  

  (If telnet isn’t installed, enable it via Control Panel > Programs > Turn Windows features on or off)
• If telnet fails, the problem is network/firewall related—not your VBA code
• You can also try connecting to the server via SSMS from the client using the same credentials—if that fails, fix that first

6. (Optional) Enable SQL Server Browser Service #

If you don’t want to use a fixed port, enable SQL Browser to let clients find the dynamic port:

• On the server, go to services.msc and set SQL Server Browser to Automatic start, then start the service
• Allow UDP port 1434 through the firewall
• Your connection string can then omit the port (e.g., Data Source=YOUR_SERVER_NAME\SQLEXPRESS)

Quick Tip: If you’re still stuck, use SQL Server Profiler on the server to see if the connection attempt is even reaching the server. This will tell you if it’s a network block or an authentication failure.

内容的提问来源于stack exchange，提问作者Dave