EXCEL VBA与SQL server的交互（二）

引言

书接上回，接着讲VBA与SQL server的交互

本篇主要内容包括VBA执行存储过程，以及VBA与SQL server交互的一些技术细节

偏技术的会放在文章的最后，不感兴趣的可以直接跳过

正文

我们上回用VBA从SQL server中获取了一些数据，现在让我们来使用VBA传递数据给SQL server

代码和上回几乎一样，我们就来简单的复习一下

首先要创建连接以及数据集：

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

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

然后呢打开连接，执行insert语句：

conn.ConnectionString = "Provider=SQLOLEDB;Server=127.0.0.1;Database=Test;Uid=sa;Pwd=123456789"
conn.Open

Dim sql_text As String
sql_text = "insert into  [Test].[dbo].[test] values('fakecountry',111,111,222)"

rs.Open sql_text, conn

最后关闭连接释放资源：

Set rs = Nothing
conn.Close
Set conn = Nothing

就这么简单，我们就传递了一行数据到数据库test表中

但在实际的生产环境中，情况要复杂很多

首先，实际工作中我们可能没有这么大的数据库权限，会允许我们随意增改删查数据库中的表

一般可能开放视图或者某些存储过程的权限

所以呢接下来让我们看看怎么通过VBA执行存储过程，来达到数据抽取和数据批量存入的目的

先来看怎么用存储过程来从数据库获取数据

我的test库中有这样一个存储过程：

非常简单的一个存储过程，功能是通过入参（国家名）来获取整行数据

我们只需要在上次的代码基础上做略微的修改即可：

Sub test()

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

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=SQLOLEDB;Server=127.0.0.1;Database=Test;Uid=sa;Pwd=123456789"
conn.Open
conn.[population] "China", rs

Dim counter As Integer
counter = 0

Dim col_name() As String
Dim col_count As Integer

With rs
 
    col_count = .Fields.Count
    ReDim Preserve col_name(0 To col_count - 1)
    
    For counter = 0 To col_count - 1
    
        col_name(counter) = .Fields(counter).Name
        
    Next counter
    
    With ThisWorkbook.Worksheets("Sheet1")
    
        .Range("A1").Resize(1, col_count) = col_name
        .Range("A2").CopyFromRecordset rs
    
    End With

End With

Set rs = Nothing
conn.Close
Set conn = Nothing

End Sub

和上一篇的代码对比少了：

Dim sql_text As String
sql_text = "SELECT top 100 * FROM [Test].[dbo].[test]"
rs.Open sql_text, conn

这三行，多了：

conn.[population] "China", rs

这一行，结果如图所示：

从存储过程获取数据非常的简单，接下来看怎么插入数据

假设我们有这样一个存储过程：

代码和直接用insert比较类似，就不一一讲解了，直接上代码：

Sub test4()

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

conn.ConnectionString = "Provider=SQLOLEDB;Server=127.0.0.1;Database=Test;Uid=sa;Pwd=123456789"
conn.Open
conn.[population_in] "fakecountry", 111, 111, 222

conn.Close
Set conn = Nothing

End Sub

就是这么的简单，不过上面的语句很很大的优化空间，这个先按下不表，等到第三篇再细说

通过第一篇以及上面的内容我们已经能写语句在VBA里面完成对数据表格数据的增和查，改和删同理大家可以自行研究

接下来要讲的偏技术，主要讲解VBA与SQL server交互的原理，也是为第三篇，代码的优化做铺垫；如果是实用主义的同学看到这里就可以关网页了

回忆一下第一篇文章，我们在写代码前引用了两个库，也就是：

Microsoft ActiveX Data Objects 6.1 Library

Microsoft ActiveX Data Objects Recordset 6.0 Library

这里的ActiveX Data Objects，简称ADO，是微软推出的通用数据访问技术

我们使用VBA与外部数据源做交互的时候都会使用到ADO，比如和SQL server的交互、和计算机本地文本文件的交互、和Oracle的交互、和Mysql的交互、以及和Access的交互等

ADO本身并不直接与外部数据交互，它只是一套规范；与外部数据直接接触的是一个名为OLE DB的技术，ADO是OLE DE与VBA之间的桥梁

而ADO的优势就是，不管访问的是什么外部数据源，ADO的使用者只需要一组相同的命令。

打个比方，SQL server中我们可以使用select * into <table1> from <table2>的写法来插入数据，但mysql中没有这个语法。也就是说同样的插入数据，对于这两个数据库，可能需要写两个不同的语句。有了ADO就不同的，只要按照ADO的规则，那么对于SQL server和MySQL来说插入的语句的写法是一样的。作为VBA使用者，我们就不需要考虑数据源的个性问题了。

看到这里有些同学可能就会发问了，上面写的插入和查询语句都是写死的，也就是：

sql_text = "insert into  [Test].[dbo].[test] values('fakecountry',111,111,222)"

这完全没有用到ADO的优势呀，不要急，这个我们到第三章再做优化

VBA,ADO,OLE DB，以及数据源的关系是这样的：

ADO中常用的对象有三个（总共有多少个楼主不清楚），还有四个集合：

在前文中我们用到了，connection和recordset object

connection object顾名思义是用来创建连接的，但有时connection对象也不是必要的

在recordset对象中就有一个可以替代connection object对象的activeconnection属性

connection object常用的属性包括：

1.connectionstring，这个我们上面已经用到过了：

conn.ConnectionString = "Provider=SQLOLEDB;Server=127.0.0.1;Database=Test;Uid=sa;Pwd=123456789"

这个连接字符串中有5个参数，其中Provider这个，它告诉ADO使用哪个OLE DB

后面的参数都是给具体的某个OLE DB的参数

Provider其实不是必填的参数，如果省略，ADO就默认使用ODBC

2.ConnectionTimeout，这个参数就不解释了，懂的都懂

3.State,这个属性用来查询当前的连接状态，值包括：

• adStateClosed，连接已关闭
• adStateOpen，连接已打开
• adStateconnecting，正在创建连接
• adStateExecuting，正在执行命令

然后是connection object的方法，包括：

1.open,这个方法我们在上面也用过了，用于打开连接

不过多介绍这个方法了，但有一点比较有趣的是，open这个方法支持异步连接

如果你的程序需要长时间的连接，那么这个异步就很有用了，具体的就不展开了

2.Execute,这个方法我们在上面其实也用到过：

conn.[population_in] "fakecountry", 111, 111, 222

上面的语句虽然没有直接写出Execute,但实际是一样的

一般Execute用来执行那些没有返回结果的语句，也就是增改删语句

当然也可以用来和Recordset配合来执行查询语句：

set recordset = conn.Execute("select * from table")

3.Close,这个方法也不用说了，只要记住关闭连接不代表释放资源

connection这个对象有两个集合，Error和Properties

这里就不作介绍了，大家只要知道，Error包含一组Error（也包括警告和消息）对象,这些信息和ADO本身没有关系，是各个OLE DB提供的

Properties包含了一些OLE DB提供的扩展信息

Recordset这个对象我们在上面也使用过了，首先来看一下常用的属性：

1.ActiveConnection

这个属性有两种使用方法，一种是配合Connection对象，另一种是抛弃Connection对象自己创建连接:

set rs.ActiveConnection = conn
或者
set rs.ActiveConnection = "Provider=SQLOLEDB;Server=127.0.0.1;Database=Test;Uid=sa;Pwd=123456789"

2.BOF，也就是beginning of file，数据集的第一条记录

3.EOF，也就是end of file，数据集的最后一条

4.Filter,也就是筛选器：

rs.Filter = "country like 'Ch*'"

筛选器支持使用通配符，这让筛选器变得很好用

5.State,这个属性和Connection对象的State属性一样

结尾

本篇就先讲到这里了，本来想讲完三个对象的，但内容有点多，还是在下篇来讲吧

下一篇我们会把Recordset这个对象的方法和集合讲完，然后讲Command对象

如果顺利的化=话还会做一些代码优化的讲解，如果顺利的话。。。