本文主要涉及:
- 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
特别提醒:本人看博客园的消息还是比较少,如果对本文有疑问,可以发邮件到 shandongdxl2004@126.com 我看邮箱还是比较勤快的 :)
往期链接:
Excel VBA 连接各种数据库(一) VBA连接MySQL数据库
Excel VBA 连接各种数据库(二) VBA连接Oracle数据库