VBA连接SQL Server数据库：从ADODB连接到高效查询与写入实践

简介：一份面向Excel/VBA开发者的数据库交互实战文档，重点演示如何通过ADO连接SQL Server并完成数据查询。内容围绕具体VBA代码展开，包含连接字符串的配置、ADODB.Connection与Recordset对象的使用、SELECT查询语句的拼接，以及将查询结果写入Excel工作表的完整流程；同时说明adOpenStatic与adLockBatchOptimistic参数对性能的影响。文档还总结了连接安全、错误处理、性能优化、事务管理及关闭连接等注意事项，并给出基于用户窗体输入条件动态筛选数据的具体写法，便于直接改造复用。资源为1个doc文档，约16KB，轻量易用，适合需要从Excel直接操作SQL Server的入门与进阶人员参考。目前已有1059人学习，具备一定的实践参考价值。

1. VBA连接SQLSERVER数据库实例：Excel拿不下的大表，用ADO接回工作表

SQL Server 的“数据库实例”（instance）不是指某一个库，而是运行着 SQL Server 服务的入口；同一个实例下可以挂很多个数据库。Office 上的 VBA 通过 ADODB 连接这个入口，把 SELECT、INSERT、UPDATE、DELETE 发过去，再把查询结果直接拉回工作表。最典型的场景是财务月底要对几百万行流水做汇总，或者人事要按几百个工号去查员工状态——在 Excel 里 VLOOKUP 会卡到假死，而让 SQL Server 先把结果算好、只把明细拉到本地，速度会是两个世界。这套方案的入口是连接字符串，难点全在“连上之前”的配置；适合 Excel 用得熟、SQL 还停留在 select 阶段的办公人员，也适合想把 Excel 变成数据库操作台的开发者。

2. 连接方式选型：为什么是ADODB直连，而不是ODBC数据源或Power Query

2.1 三条路：OLEDB Provider、ODBC Driver、System DSN怎么选

VBA 访问 SQL Server 的常见做法并不止一种。从业多年看到最多的是 ADODB 里的三种写法：第一种用“SQLOLEDB”这个 OLEDB Provider；第二种用“SQL Server Native Client”或者新版“ODBC Driver 18”，在连接字符串里体现为“Driver={ODBC Driver 18 for SQL Server}”；第三种是在 Windows 的“ODBC 数据源管理器”里建一个“System DSN”，然后在 VBA 里直接引用 DSN。

SQLOLEDB 是最老的 Provider，SQL Server 2000 时代就有了。驱动老不代表不能用，它在基本查询和类型映射上足够可靠；但它不支持一些新特性，比如新版加密和更精细的日期时间类型。想用新特性的，建议安装“MSOLEDBSQL”这个新的 OLE DB Provider，它由微软官方维护，连接字符串写法基本不变，只要把“Provider=SQLOLEDB.1”换成“Provider=MSOLEDBSQL”即可。ODBC Driver 则是另一条平行路线，在功能上和官方当前支持线路贴合更紧，如果你的环境里已经装了 ODBC Driver 17/18，直接用 Driver 写法也完全可行。System DSN 可以把账号密码存在系统里，VBA 代码只写一句“DSN=MySqlServer”，代码里不暴露密码；但它把配置绑定到了当前机器，换一台电脑就报“找不到数据源”，运维成本并不低。

三条路实际可以同时存在，并不冲突。我的习惯是：开发调试时用 OLEDB 直连，因为报错信息直观；交付给同事时如果不想让对方改代码，才考虑 DSN。新手不建议一上来就碰 DSN，因为 DSN 一旦配错，VBA 报错信息是通用的“未找到数据源”，你很难判断是驱动坏了还是名字写错了。先让一段最朴素的 Provider 直连跑通，再考虑封装。

2.2 ADODB与QueryTables、Power Query的本质差别

很多人会用 Excel 自带的“数据”选项卡里的“获取数据”来导入 SQL Server，也能成功，那为什么还要用 VBA 写 ADODB？因为工具型操作和程序型操作的控制力度完全不同。QueryTables 是一块一次性的查询区，刷新时机得手动或者依赖连接属性，它无法在一个宏里跑十次不同的参数并分别写回不同区域。Power Query 适合做数据清洗和定时刷新，但查询条件写死在 M 语言里，想根据某一个单元格的值动态换参数，就要去改查询定义，普通用户改不了。

ADODB 把连接、命令、结果集拆成三个对象，你可以在 VBA 循环里随时改变 SQL 文本和参数值，拿到结果后决定是写进单元格还是继续做内存计算。另一个容易被忽略的点是内存占用：QueryTables 会把数据“填”进工作表单元格，行数一大 Excel 底层就开始吃力；而 ADODB 的 Recordset 是一个独立的客户端数据集，你可以一行一行取，可以只取需要的列，也可以在取数前用 SQL 聚合把返回量压到很小。SQL Server 负责大计算量，Excel 只负责展示小结果集，这正好是“数据库实例”存在的意义。

2.3 环境准备：ADO引用、SQL Server驱动程序、TCP/IP协议三个前置条件

写第一条代码之前，先做三件事。第一件：在 VBA 编辑器里按“工具→引用”，勾选“Microsoft ActiveX Data Objects 2.8 Library”。版本号在 2.5 到 6.1 之间都能用，Excel 会按你机器上注册的库显示；如果列表里找不到，说明机器缺 ADO 运行库，需要安装微软数据访问组件 MDAC。第二件：确认本机有 SQL Server 驱动。Windows 自带老版本 ODBC 驱动，但更可靠的是安装“Microsoft ODBC Driver for SQL Server”或“Microsoft OLE DB Provider for SQL Server”。第三件：SQL Server 那台机器要在“SQL Server 配置管理器”里把“TCP/IP 协议”的“已启用”设为“是”，端口设为 1433；如果是默认实例，端口通常已经固定，命名实例则会占用动态端口。

配完可以做一次快速自检，不写连接代码，直接在 VBA 立即窗口（Ctrl+G）运行下面这句：

Debug.Print CreateObject("ADODB.Connection").ConnectionString

这行如果返回空字符串而不是“自动化错误”，说明 ADODB 组件本身可用。注意 WPS 的 VBA 对 ADODB 的支持不太完整，调试环境尽量用微软 Office Excel，功能验证后再考虑 WPS 兼容性。

这段脚本很短，但它验证的是组件层，不是网络层。如果在这句就报错，先回头装 MDAC 或检查 Office 位数；如果这句通过但在连接 SQL Server 时失败，那才是后面要排查的网络和认证问题。

3. 最小可运行连接：连接字符串拆解、Recordset查询和增删改查

3.1 查询一行数据的最小代码：连上、取数、断开的完整写法

先给一个完整可运行的最小片段，作用是把 SQL Server 当前时间写到 A1 单元格：

Sub Demo_SelectOneValue() Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String Set conn = New ADODB.Connection conn.ConnectionString = "Provider=SQLOLEDB.1;" & _ "Data Source=127.0.0.1,1433;" & _ "Initial Catalog=DemoDB;" & _ "User ID=sa;Password=你的密码;" conn.Open sql = "SELECT GETDATE() AS NowTime" Set rs = conn.Execute(sql) Sheet1.Range("A1").Value = rs.Fields("NowTime").Value rs.Close Set rs = Nothing conn.Close Set conn = Nothing End Sub

这段代码是“连接→执行→取结果→释放”四步骨架。顺序上先创建 ADODB.Connection，再给 ConnectionString 赋值，然后 Open。Execute 返回一个 Recordset，哪怕只有一个字段也要当成结果集来读。“rs.Fields("NowTime").Value”取出第一行的列值，然后立刻关闭并清空对象。

关键参数有三个。Data Source 写成“127.0.0.1,1433”，逗号后面是端口，这在默认实例上最省事；写成“计算机名\实例名”则是命名实例的写法。Initial Catalog 是数据库名，不是实例名。Provider 用 SQLOLEDB 最简单，如果前面装了新版 OLEDB Provider，可以改成“Provider=MSOLEDBSQL;”，差异主要在加密和数据类型映射上。连接串里出现密码属于明文，生产环境建议换成“Integrated Security=SSPI”，也就是用 Windows 账号登录，密码不出现在代码里。

3.2 查询多行多列：Recordset遍历和CopyFromRecordset怎么选

查询一张订单表并写回工作表，有两种常见写法。第一种是逐行遍历：

Sub Demo_ReadByRecordset() Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim i As Long Set conn = New ADODB.Connection conn.Open "Provider=SQLOLEDB.1;Data Source=127.0.0.1,1433;Initial Catalog=DemoDB;User ID=sa;Password=你的密码;" Set rs = New ADODB.Recordset rs.Open "SELECT OrderID, CustomerName, Amount FROM Orders WHERE Status='已发货'", conn i = 1 Do Until rs.EOF Sheet1.Cells(i, 1).Value = rs.Fields("OrderID").Value Sheet1.Cells(i, 2).Value = rs.Fields("CustomerName").Value Sheet1.Cells(i, 3).Value = rs.Fields("Amount").Value rs.MoveNext i = i + 1 Loop rs.Close conn.Close End Sub

遍历的好处是可以对每一行做判断、格式化，或者根据某个字段值决定是否继续读取；缺点是数据量大时逐格写单元格会比较慢。第二种是用 Recordset 的 CopyFromRecordset，一次把整个结果集倒进一个区域：

Sub Demo_CopyFromRS() Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Set conn = New ADODB.Connection conn.Open "Provider=SQLOLEDB.1;Data Source=127.0.0.1,1433;Initial Catalog=DemoDB;User ID=sa;Password=你的密码;" Set rs = conn.Execute("SELECT OrderID, CustomerName, Amount FROM Orders WHERE Status='已发货'") Sheet1.Cells.ClearContents Sheet1.Range("A1").CopyFromRecordset rs rs.Close conn.Close End Sub

注意 CopyFromRecordset 只复制值，不复制公式、批注、列宽，也不会自动加表头。而且目标区域要和结果集列数匹配，否则多余列会被覆盖。如果返回行数是任意数量，建议先“Cells.ClearContents”再复制，避免上一次的旧数据残留。对几万行以内的结果集会比逐格写快好几倍；但真要处理百万行，正确做法是继续用 SQL 聚合，而不是把百万行搬到 Excel。

3.3 INSERT、UPDATE、DELETE怎么执行：Command对象与Execute的取舍

非查询语句的写法更多人用 conn.Execute，因为它简单：

Sub Demo_Update() Dim conn As ADODB.Connection Dim sql As String Set conn = New ADODB.Connection conn.Open "Provider=SQLOLEDB.1;Data Source=127.0.0.1,1433;Initial Catalog=DemoDB;User ID=sa;Password=你的密码;" sql = "UPDATE Orders SET Amount = Amount * 1.1 WHERE Status='已发货'" conn.Execute sql MsgBox "执行成功，错误数：" & conn.Errors.Count conn.Close End Sub

conn.Execute 适合执行没有返回值的增删改。值得注意，UPDATE 成功不一定代表影响行数符合预期，除非你在 SQL 里用“OUTPUT inserted/deleted”把受影响的键值取回来，或者先查 COUNT 再对比。更稳妥的做法是把 UPDATE 写成事务，配合错误处理和回滚。

涉及用户输入拼接的增删改，用 Command 对象更安全。典型场景是 Excel 某两列要写入表，逐条生成 INSERT 字符串容易在引号、单引号、日期格式上翻车。常见做法是先把数据放进数组，再通过 ADODB.Command 的 Parameters 传参：

Sub Demo_InsertWithCommand() Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim i As Long Dim arr As Variant Set conn = New ADODB.Connection conn.Open "Provider=SQLOLEDB.1;Data Source=127.0.0.1,1433;Initial Catalog=DemoDB;User ID=sa;Password=你的密码;" Set cmd = New ADODB.Command Set cmd.ActiveConnection = conn cmd.CommandText = "INSERT INTO Orders(CustomerName, Amount) VALUES(?, ?)" cmd.Parameters.Append cmd.CreateParameter("pName", adVarWChar, adParamInput, 50, "") cmd.Parameters.Append cmd.CreateParameter("pAmount", adDouble, adParamInput, , 0) arr = Sheet1.Range("A2:B10").Value For i = 1 To UBound(arr, 1) cmd.Parameters("pName").Value = arr(i, 1) cmd.Parameters("pAmount").Value = CDbl(arr(i, 2)) cmd.Execute Next i conn.Close End Sub

“VALUES(?, ?)”里的问号是参数占位符，CreateParameter 指定类型和长度，adVarWChar 对应字符串，adDouble 对应浮点数；使用参数化之后，单元格里的单引号不用再手工转义，这是和直接用字符串拼接最大的差别。这套逻辑同时也是后面批量写入模板的基础。如果只是临时跑一次，用 conn.Execute 没问题；要是把这段代码放进每月的报表宏里反复用，建议直接用 Command 对象。

4. 连接字符串参数配置：实例名、端口、超时、加密和日期数字类型

4.1 连接字符串参数表和生产环境推荐模板

连接字符串里的参数名不区分大小写，大部分可以省略，但真到生产环境时，省略的参数往往就是出问题的参数。整理一张常用参数表如下。

给出一个生产环境推荐模板：

conn.ConnectionString = "Provider=MSOLEDBSQL;" & _ "Data Source=10.20.1.8,1433;" & _ "Initial Catalog=ERP;" & _ "Integrated Security=SSPI;" & _ "Persist Security Info=False;" & _ "Connection Timeout=30;"

这个模板适用于内网开发场景。如果服务器没启用加密证书，不要把 Encrypt 明确写成 True，否则客户端会校验证书，证书不合规就拒绝连接。Command Timeout 单独写在 conn.CommandTimeout 属性上更好，因为它是连接级的一个可调属性，不必挤在字符串里。

4.2 默认实例与命名实例：端口、SQL Browser与防火墙的配合

SQL Server 的实例有两类。默认实例叫 MSSQLSERVER，监听 1433 端口，连接时写“IP地址,1433”；命名实例是自己起名，例如“10.20.1.8\FINANCE”，默认监听动态端口，需要 SQL Server Browser 服务配合，连接时写“IP地址\FINANCE,端口号”或者不用端口号让 Browser 去解析。

很多人的连接失败都出在命名实例上：能 ping 通服务器，但每次都是超时才报错。原因往往是 SQL Browser 服务没启动，或者防火墙只放行了 1433，而命名实例实际监听的是随机端口。处理方法是先在“SQL Server 配置管理器”里查看“TCP/IP 协议”下的“IPAll→TCP 动态端口”里记录的具体端口，再把这个端口写进连接字符串，例如“Data Source=10.20.1.8\FINANCE,49357”。这样能跳过 SQL Browser 解析这一步，让连接更直接。

注意：命名实例的动态端口在服务重启后可能变化，写死端口前先确认是不是被 DHCP 或重启影响。

验证服务器端口是否可通，不要用 Excel 傻等，直接在 PowerShell 里输入：

Test-NetConnection -ComputerName 10.20.1.8 -Port 1433

返回的 TcpTestSucceeded 为 True 说明端口可达，否则请先检查 SQL Server 服务和云安全组。这一步能区分问题在网络层还是认证层，是排查连接超时的第一条命令。

4.3 字符串转数字与日期传参：SQL侧和VBA侧的配合写法

Excel 单元格里存的是“00123”这类文本，SQL Server 里存的是 int 列，直接 WHERE 比较经常会隐式转换出奇怪结果。SQL 侧的推荐写法是先用 TRY_CAST 而不是 CAST：

SELECT OrderID FROM Orders WHERE TRY_CAST(OrderNo AS INT) = 123;

CAST 遇到无法转换的字符串会直接让整条查询失败，TRY_CAST 则返回 NULL，更适合处理从 Excel 粘贴过来的脏数据。反过来，如果 Excel 里的数字在 SQL 那边被当成 varchar，就用 CAST 把数字转成字符串再比较，避免尾随空格干扰。

日期类型则相反，SQL Server 的 DATETIME 和 Excel 的序列号日期不是一回事，直接把 Variant Date 拼进 SQL 字符串经常出现“字符串被截断”的报错。正确做法是继续用参数对象显式声明日期类型：

cmd.CommandText = "SELECT * FROM Orders WHERE CreateDate >= ? AND CreateDate < DATEADD(DAY, 1, ?)" cmd.Parameters.Append cmd.CreateParameter("pStart", adDBTimeStamp, adParamInput, , CDate(Range("B1").Value)) cmd.Parameters.Append cmd.CreateParameter("pEnd", adDBTimeStamp, adParamInput, , CDate(Range("B2").Value))

参数类型 adDBTimeStamp 会告诉 SQL Server 这个值按 DATETIME 处理；日期范围用“>=当天的0点 AND <（当天+1天的0点）”写法，可以避开 BETWEEN 在 DATETIME 精度下漏掉当天最后一秒的经典问题。这一条在生产里很容易被忽略，但它决定了报表月末取数时会不会少最后半小时的数据。

5. 避坑与排查：连接失败、驱动版本、锁表、中文乱码的常见场景

5.1 连接超时：卡了20秒才报错，原因多半在防火墙

现象是 VBA 里执行 conn.Open 后要等 10 到 20 秒，最后弹“超时时间已到”而不是“登录失败”。很多人第一反应去怀疑账号密码，其实账号密码错会立刻返回，只有网络层不通才会慢慢超时。原因基本是防火墙没放行 1433 端口、SQL Server 没启用 TCP/IP、或者服务器上有云安全组拦截。解决顺序：先用 PowerShell 的 Test-NetConnection 测端口，再去看 SQL Server 服务是否运行，最后检查防火墙入站规则。端口通了再回来查账号密码，能节省大量排查时间。

5.2 64位Office与驱动位数不匹配：Provider报“未找到”的解决办法

现象是代码完全照教程写的，但 conn.Open 时报“未找到提供程序”。原因大概率是 Office 是 64 位，驱动装成了 32 位，或者反过来。ADO 是老组件，Provider 的注册表信息分位数存放，64 位进程找不到 32 位的 Provider，这是位数隔离，不是环境坏了。解决方式是安装和 Office 位数一致的 ODBC 驱动，或者换用不必依赖本地 Provider 的写法。另一个常见细节是“Provider=SQLNCLI11”在老机器上已经没有对应驱动，建议退回“Provider=SQLOLEDB.1”，它在多数系统自带，兼容性最广。驱动版本不是越新越好，够用就行，这是这个方向最容易翻车的黑匣子。

5.3 已连接但报证书错误：数据源加密与TrustServerCertificate的配套

现象是信息提示“连接成功建立，但随后发生错误”，后面跟着证书相关的文字，连接最终失败。原因在于新版 ODBC Driver 和 MSOLEDBSQL 默认 Encrypt=Yes，服务器如果只有自签名证书，客户端校验证书失败就会中断连接。解决方式是显式在连接字符串里加“Encrypt=no;TrustServerCertificate=yes”。TrustServerCertificate 只在自签名证书环境用，生产环境用的是正规 CA 证书时，应当保持 True 的安全校验，两条参数要配套，单独加其中一条往往不起作用。

5.4 中文读出乱码：排序规则、AutoTranslate与NChar字段

现象是 SQL Server 里看到中文正常，但 VBA 读回工作表后变成问号或繁体。原因通常是列字段是 varchar 而不是 nvarchar，加上驱动和客户端之间做了代码页转换，丢失了 Unicode 信息。解决方式分两层：结构上把关键字段改成 nvarchar，这是根治法；连接层可以在连接字符串里加“Auto Translate=False”，阻止驱动自动把字符转成本地代码页。如果数据已经脏了，SQL 侧用 CONVERT(NVARCHAR(50), 列名) 临时处理也能救一部分。判断是哪种原因很简单：直接在 SQL Server Management Studio 里查同一行数据，如果那边正常而 VBA 乱码，问题就在客户端转换链路。

5.5 Recordset不释放导致锁表：错误处理里的Close兜底

现象是宏第一次运行正常，第二次再跑就报“表正在被其他用户使用”，或者 SQL Server 里对应的表一直有锁。原因往往是 VBA 里 rs.Open 之后因为某种原因中途跳出，后面的 rs.Close、conn.Close 没执行到，事务没提交，连接还挂在服务器端。解决方式是给过程包一层错误处理：

On Error GoTo errHandler ' 连接和查询代码 errExit: If Not rs Is Nothing Then rs.Close If Not conn Is Nothing Then conn.Close Exit Sub errHandler: MsgBox Err.Description Resume errExit

这段代码的关键在于无论哪一步出错，都会走到 errExit 统一关闭对象。锁表是这类方案最隐蔽的副作用，因为它不在客户端报错，而是把问题留给下一次运行才暴露。养成“所有数据库过程都带错误处理”的习惯，是给后续运维买单的后悔药。

6. 把连接封装成工具：复用连接、状态栏进度和批量参数化写入

6.1 用全局对象复用连接，避免循环里反复建连

如果只是手工跑一次，每次新建连接没有问题；但每次连接 SQL Server 都要完成 TCP 握手、身份认证、权限检查，几十秒里反复执行几十次就很明显。我一般会在标准模块里声明一个模块级连接变量，配合惰性连接函数：

Public gConn As ADODB.Connection Public Function GetConn() As ADODB.Connection If gConn Is Nothing Then Set gConn = New ADODB.Connection gConn.Open "Provider=SQLOLEDB.1;Data Source=127.0.0.1,1433;Initial Catalog=DemoDB;User ID=sa;Password=你的密码;" ElseIf gConn.State = adStateClosed Then gConn.Open End If Set GetConn = gConn End Function

调用前先用“Set conn = GetConn()”，用完不急着 Close，等整个宏结束再统一回收。这套写法在批量写入和同步任务里的收益最明显，连接只建一次，后面的循环全部复用。

6.2 状态栏反馈同步进度，避免宏“看起来卡死”

批量同步上千条数据时，Excel 界面会处于无响应状态，很多人以为是死机直接强制关闭，结果连接没释放，SQL Server 那边锁了一堆表。用状态栏可以把这个黑匣子变成可见进度：

Application.StatusBar = "正在处理第 " & i & " 条 / 共 " & n & " 条"

循环结束后记得执行“Application.StatusBar = False”恢复默认状态。这一招成本极低，但能大幅降低同事误关宏的概率，这是我被坑过一次后才坚持加的。

6.3 一个通用的批量写入模板：命令复用 + 进度提示

把前面的参数化命令、全局连接、状态栏合并成一个通用模板，处理 Excel 的 A 列工号、B 列金额批量更新订单表：

Sub BatchUpdateFromSheet() Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim arr As Variant Dim i As Long Set conn = GetConn() Set cmd = New ADODB.Command Set cmd.ActiveConnection = conn cmd.CommandText = "UPDATE Orders SET Amount = ? WHERE OrderNo = ?" cmd.Parameters.Append cmd.CreateParameter("pAmt", adDouble, adParamInput, , 0) cmd.Parameters.Append cmd.CreateParameter("pNo", adVarWChar, adParamInput, 30, "") arr = Sheet1.Range("A2:B" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row).Value For i = 1 To UBound(arr, 1) cmd.Parameters("pAmt").Value = CDbl(arr(i, 2)) cmd.Parameters("pNo").Value = CStr(arr(i, 1)) cmd.Execute Application.StatusBar = "正在更新第 " & i & " 条 / 共 " & UBound(arr, 1) & " 条" Next i Application.StatusBar = False End Sub

这套模板的复用价值在于：换表名和字段即可应付多数 Excel 回写数据库的需求。回看这几年做过的数据接口，最影响体验的从来不是 SQL 语法，而是连接生命周期管理。我第一次做这种批量同步时，给每条 UPDATE 都新建连接，速度慢到同事以为电脑坏了；后来改成连接复用加参数化，才真正跑进生产环境。希望帮到你。

本文还有配套的精品资源，点击获取