《Excel VBA+SQL数据管理与应用模板开发》_4.0 利用SQL进行数据维护

【摘要】 本书摘自《Excel VBA+SQL数据管理与应用模板开发》一书中第4章，第1节，韩小良著。

4.0 利用SQL进行数据维护

利用ADO+SQL 不仅可以查找数据，还可以对表格数据进行编 辑加工，例如，修改数据、删除数据、添加数据，这样可以避免在工作 表中插入行、删除行、通过筛选或者定位的方法寻找满足条件数据进行 编辑。

4.1 向工作表中添加数据

一般情况下，向工作表中添加数据的方法是定位到数据区域的最后，然后手动 输入各列数据；或者在某个位置插入一个空行，然后输入数据，这种方法大家已经 很熟练了。

但是，如果不允许打开基础数据工作簿，那么如何向该工作簿的指定工作表中添加 数据呢?

另外，假如工作表数据量很多，这样定位输入或者插入空行数据的方法就比较 烦琐。而且，在打开大数据量工作簿时，操作工作簿的速度也是较慢的。在这种情 况下，如何解决数据添加问题呢?

我们可以设计一个用户窗体，先在窗体界面中输入各个字段数据，然后单击按 钮，即可将数据保存到指定的工作表中。关于用户窗体的使用，将在第6章进行介 绍。本章我们主要介绍如何使用ADO+SQL 来实现工作表数据的自动添加，并与 普通的VBA 代码进行对比。

向当前工作簿的工作表中添加数据

向工作表中添加数据的方法是使用Recordset 对象的 AddNew方法，也可以使用SQL 的 INSERT INTO 语句，不论是向当前工作簿添加数据，还是向其他工作簿添加数据(不管是否 已经打开),都可以使用这两种方法，下面举例说明。

1.使用AddNew 方法

使用AddNew 方法的基本语句格式如下：

With rs

.AddNew

.Fields(字段1)=值1

.Fields(字段2)=值2

'….其他字段赋值

.Fields(字段n)= 值n

.Update

End With

2.使用SQL 的 INSERT INTO 语句

(1)一次输入一条记录时的INSERTINTO 语句格式如下：

INSERTINTO 数据表名(字段1,字段2,…)VALUES(值1,值2,…)

(2)如果要输入全部字段数据，那么就可以省略字段列表，即：

INSERT INTO数据表名VALUES(值1,值2,…)

案例4-1

如图4-1所示是一个简单的工作表数据，现在要求在此工作表后面添加一行数据，具体 数据如下。

合同号： GQ-20190301

合同名称：城市污水处理

供货单位：西海水处理新材料有限公司

合同金额：300000.00

签订日期：2019-3-16

交货日期：2019-7-20

( 1 ) 使 用Recordset 对象的 AddNew 方法。

Sub 案例4-10

Dim cnn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim SQL As String

'建立与工作簿的连接

With cnn

.Provider ="microsoft.ace.oledb.12.0"

.ConnectionString="Extended Properties=Excel 12.0;"_

&"Data Source="&ThisWorkbook.FullName

.Open

End With

'建立工作表的查询

SQL="select*from [合同信息$]"

rs.Open SQL,cnn,adOpenKeyset,adLockOptimistic

'添加数据

With rs

AddNew

.Fields("合同号")="GQ-20190301"

.Fields("合同名称")="城市污水处理"

.Fields(" 供货单位")="西海水处理新材料有限公司"

.Fields(" 合同金额")=300000

.Fields("签订日期")=#3/16/2019#

.Fields("交货日期")=#7/20/2019#

.Update

End With

MsgBox "数据添加成功!"

End Sub

( 2 ) 使 用 SQL 的 INSERTINTO 语句。

Sub 案例4-10

Dim cnn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim SQL As String

'建立与工作簿的连接

With cnn

.Provider ="microsoft.ace.oledb.12.0"

.ConnectionString="Extended Properties=Excel 12.0;"_

&"Data Source="&ThisWorkbook.FullName

.Open

End With

'向工作表添加数据

SQL="insert into [合同信息$]values(GQ-20190301; 城市污水处理；西海水处理新材料 有限公司：,300000,#3/16/2019#,#7/20/2019#)”

rs.Open SQL,cnn,adOpenKeyset,adLockOptimistic

MsgBox "数据添加成功!"

End Sub

说明

如果不使用ADO+SQL, 而是使用普通的VBA方法向工作表中添加数据，则参考代码 如下：

Sub 案例4-1-10

Dim ws As Worksheet

Dim n As Integer

'指定工作表

Set ws =This Workbook.Worksheets("合同信息")

'确定要添加数据的行号

n=ws.Range("A1000").End(xIUp).Row+1

'添加数据

With ws

.Range("A"&n)="GQ-20190301"

.Range("B"&n)=" 城市污水处理"

.Range("C"&n)=" 西海水处理新材料有限公司"

.Range("D"&n)=300000

.Range("E"&n)=#3/16/2019#

.Range("F"&n)=#7/20/2019#

End With

MsgBox "数据添加成功!"

End Sub

比较一下这两个程序的代码各有什么优缺点?

使用ADO+SQL添加数据不必考虑各个字段在工作表中的具体位置，只要工作表存在这 个字段即可。

使用普通VBA 方法来添加数据，就必须指定各个字段的列位置，这就带来了一个很严 重的问题：如果在工作表中插入或者删除了列，或调整了各列位置，使得字段的类位置发生 了变化，那么这个程序就需要进行修改调整了。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。