Excel VBA 连接各种数据库（三） VBA连接SQL Server数据库

2022-01-25 21:02:06

本文主要涉及：

VBA中的SQL Server环境配置
VBA连接SQL Server数据库
VBA读写SQL Server数据
如何安装SQL Client

系统环境：

Windows 7 64bit
Excel 2016 64bit

1. VBA连接SQL Server前的环境配置

在Excel这边，需要先在VBE中启动数据库连接支持。按下Alt+F11打开VBE，在菜单栏选择“工具”－“引用”，在弹出的引用窗口中，找到"Microsoft ActiveX Data Objects 6.1 Library"和"Microsoft ActiveX Data Objects Recordset 2.8 Library"，把前面的框勾选上，点击确定即可。（如果不是这两个版本，则选择一个版本号最高的勾选即可，如果是需要分享给office2003版的用户，建议勾选版本最低的）

2. VBA连接SQL Server

在按照上述步骤配置了环境支持后，就可以在VBA中使用代码连接SQL Server了。

首先需定义连接对象：

Dim conn as ADODB.Connection

Set conn = new ADODB.Connection

这里也可以简写为：

Dim con As New ADODB.Connection

连接数据库

conn.ConnectionString = "Provider=SQLOLEDB;Server=192.168.1.1;Database=XXXXX;Uid=sa;Pwd=123456"

conn.Open

连接字符串ConnectionString中的各个参数应该很明了，就不一一解释了。

上一段代码也可以简写为

con.Open "Provider=SQLOLEDB;Server=192.168.1.1;Database=XXXXX;Uid=sa;Pwd=123456"

至此，数据库连接成功！

可以使用连接对象的State属性和Version属性查看数据库状态和版本（检查是否连接成功）

MsgBox("连接成功！" & vbCrLf & "数据库状态：" & con.State & vbCrLf & "数据库版本：" & con.Version)

最后关闭数据库连接

con.Close

Set con = Nothing

整个过程的完整代码如下：

Sub 连接SQL Server数据库()

'1. 引用ADO工具

'2. 创建连接对象

Dim con As New ADODB.Connection

'3. 建立数据库的连接

con.ConnectionString = "Provider=SQLOLEDB;Server=192.168.1.1;Database=XXXXX;Uid=sa;Pwd=123456"

con.Open MsgBox ("连接成功！" & vbCrLf & "数据库状态：" & con.State & vbCrLf & "数据库版本：" & con.Version) con.Close Set con = Nothing End Sub

3. VBA读写SQL Server数据表

3.1 读取SQL Server数据到Excel

代码如下：

Sub linkSQL Server()

    Dim conn As ADODB.Connection

    Dim rs As ADODB.Recordset

    Set conn = New ADODB.Connection

    Set rs = New ADODB.Recordset

'配置连接串

    conn.ConnectionString = "Provider=SQLOLEDB;Server=192.168.1.1;Database=XXXXX;Uid=sa;Pwd=123456"

    conn.Open

'从test数据库的YGXM表中取出所有数据

    rs.Open "select * from `YGXM`", conn

'设置表头

    Range("A1:B1").Value = Array("ID", "Name")

'将数据输出到工作表

    Range("A2").CopyFromRecordset rs

'关闭连接

    rs.Close: Set rs = Nothing

    conn.Close: Set conn = Nothing

End Sub

相比前面的代码，以上代码多了 ADODB.Recordset 和 rs.Open，ADODB.Recordset 用于执行SQL语句并接收查询语句返回的结果集。
这里需要提一下的是，在VBA中执行SQL语句有两种方式，其一是使用连接对象执行： conn.Execute ，其第一个参数就是SQL语句；另一种则使用结果集对象执行： rs.Open ，这种方式有两个必要参数，分别是SQL语句和连接对象，如上例中的 rs.Open "select * from `test`", conn 。
接下来的两行Range是用于把查询结果复制到Excel表格中的。

3.2 写入数据到SQL Server

其实写入数据，只需要把上例中的SQL语句改成 UPDATE 或者 INSERT 即可，就不多说了。

番外篇—— 安装SQL Server client 服务

如果你正好需要使用其他语言通过ODBC连接SQL Server，可能需要先安装SQL Server client服务。

可以选择使用官方安装包，或者使用Navicat连接一次SQL Server（第一次连接时如果没安装会提示你安装）

这里以使用官方安装包为例：　　　　　　　下载地址提取码: 913s

一路下一步，在这一步选择“此功能及所有子功能将安装到本地硬盘上

然后继续一路下一步即可。

ODBC的设置和MySQL或Oracle类似，在此不再赘述，如需要可以留言或者发邮件讨论。

PS：数据库连接工具推荐使用Navicat，可以同时连接不同的数据库，非常方便。

我常用的是11.0.16版本下载地址提取码: s5kt

往期链接：

Excel VBA 连接各种数据库（一） VBA连接MySQL数据库

Excel VBA 连接各种数据库（二） VBA连接Oracle数据库

码农公寓