一、报错3704
在使用Exce模块l完成运行存储过程的操作中,发现不管怎么使用rs.open都会报错3704,”对象关闭时,不允许操作“。
代码如下:
Sub linkSQL()
'定义数据链接对象 ,保存连接数据库信息
Dim CN As Object
'定义记录集对象,保存数据表
Dim rs As Object
'创建数据链接对象
Set CN = CreateObject("ADODB.Connection")
'创建记录集对象,用于接收数据查询获得的结果集
Set rs = CreateObject("ADODB.RecordSet")
'创建用户名
Dim User As String
User = Range("A2").Value
'创建密码
Dim Password As String
Password = Range("B2").Value
'创建连接数据库的库名
Dim Db As String
Db = Range("C2").Value
'创建连接数据库的表名
Dim formName As String
formName = Range("H2").Value
'创建起始时间
Dim STime As String
STime = Range("D2").Value
'创建结束时间
Dim ETime As String
ETime = Range("E2").Value
'创建机构代码
Dim ICode As String
ICode = Range("F2").Value
'链接数据库的字符串变量
Dim strCn As String, strSQL1 As String, strSQL As String
'定义远程数据库链接字符串
strCn = "Provider=SQLOLEDB;Server=SJFXDB01VWKCD\CDDBSERVER;Database=" & Db & ";Uid=" & User & ";Pwd=" & Password & ";"
'打开连接
CN.Open strCn
'提取存储过程
'strPro = "exce cd01_cdtb " & STime & "," & ETime & "," & ICode & ";"
strSQL = "exec cd01_cdtb '" & STime & "','" & ETime & "','" & ICode & "%" & "'"
MsgBox (strSQL)
'读取数据库中的数据
rs.Open strSQL, CN
rs.Close
CN.Close
End Sub
二、尝试解决
为了解决这个问题,查了很多资料,例如:
1、设置SET NOCOUNT ON
打开存储过程,在begin后面加上一句代码:SET NOCOUNT ON,屏蔽掉这些信息。
原理:
SET NOCOUNT
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。
语法
SET NOCOUNT { ON | OFF }
注释
当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。
当 SET NOCOUNT 为 OFF 时,返回计数。
即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。
当 SET NOCOUNT 为 ON 时,将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用microsoft® SQL Server™ 提供的实用工具执行查询时,在 Transact-SQL 语句(如 SELECT、INSERT、UPDATE 和 DELETE)结束时将不会在查询结果中显示nn rows affected。
如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。
SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。
权限
SET NOCOUNT 权限默认授予所有用户。
2、Recordset集合的常用方法
'1)打开一个表
Sql = "select * from 表名" 'SQL查询语句
Set rs = New ADODB.Recordset '新建一个实例
rs.Open SQL, conn '使用 Open 方法打开数据库中的一个表
'注意,这种打开方式只能使用 rs.MoveNext (即,向后移动行坐标)而不能像其他方向,并且不能修改数据内容
'rs.Open SQL, conn,1 '虽然只加了个“1”,但这种方法可以向任何方向移动行坐标。
'以下参数代表了这个可选值的含义
'0 = adOpenForwardOnly (默认值)打开仅向前类型游标。
'1 = adOpenKeyset 打开键集类型游标。
'2 = adOpenDynamic 打开动态类型游标。
'3 = adOpenStatic 打开静态类型游标。
'虽然使用以上方法可以可以实现行坐标(游标)的任意移动,但是仍然无法写入数据。因此需要进一步的对Open 方法进行完善
'rs.Open SQL, conn, 1, 3 '后面的3是确定读写权限的
'以下参数代表了这个可选值的含义
'1 = adLockReadOnly (默认值)只读 — 不能改变数据。
'2 = adLockPessimistic 保守式锁定(逐个) — 在编辑时立即锁定数据源的记录。
'3 = adLockOptimistic 开放式锁定(逐个) — 只在调用 Update 方法时才锁定记录
'4 = adLockBatchOptimistic 开放式批更新 — 用于批更新模式(与立即更新模式相对)。
将原来的rs.Open strSQL, CN
改为rs.Open strSQL, CN,adOpenDynamic ,adLockBatchOptimistic
,仍报错。
三、解决问题
整理了一下思路,报错的原因是使用了已经关闭的ADO对象,但是我只Open了为什么会报已关闭的错误呢?
原因:
rs中的数据没有清理,打开后自动关闭,所以解决方案就是清理rs。
修改后代码如下:
Sub linkSQL()
'定义数据链接对象 ,保存连接数据库信息
Dim CN As Object
'定义记录集对象,保存数据表
Dim rs As Object
'创建数据链接对象
Set CN = CreateObject("ADODB.Connection")
'创建记录集对象,用于接收数据查询获得的结果集
Set rs = CreateObject("ADODB.RecordSet")
'创建用户名
Dim User As String
User = Range("A2").Value
'创建密码
Dim Password As String
Password = Range("B2").Value
'创建连接数据库的库名
Dim Db As String
Db = Range("C2").Value
'创建连接数据库的表名
Dim formName As String
formName = Range("H2").Value
'创建起始时间
Dim STime As String
STime = Range("D2").Value
'创建结束时间
Dim ETime As String
ETime = Range("E2").Value
'创建机构代码
Dim ICode As String
ICode = Range("F2").Value
'链接数据库的字符串变量
Dim strCn As String, strSQL1 As String, strSQL As String
'定义远程数据库链接字符串
strCn = "Provider=SQLOLEDB;Server=SJFXDB01VWKCD\CDDBSERVER;Database=" & Db & ";Uid=" & User & ";Pwd=" & Password & ";"
'打开连接
CN.Open strCn
'提取存储过程
'strPro = "exce cd01_cdtb " & STime & "," & ETime & "," & ICode & ";"
strSQL = "exec cd01_cdtb '" & STime & "','" & ETime & "','" & ICode & "%" & "'"
MsgBox (strSQL)
'读取数据库中的数据
rs.Open strSQL, CN
CN.Close
Set re = Nothing
Set CN = Nothing
End Sub
解决问题!