问题描述
我使用以下代码将记录集检索到两个本地表中，
来自WAN上的mdb文件。工作正常，但我想调整速度，
我有一些问题：
1.我尝试了两个ADOX创建的存储程序和常规访问
参数查询。经过几次测试，我得出的结论是，两个人之间没有统计差异。这是否有意义，或者我可以做一些不同的事情来加快速度（两个查询只是
标准三表连接选择带一个参数的查询）？
2.当我在第三个记录集中打开下面的两个调用，在用户
表上打开时，这会加快速度 - 换句话说，当记录集rs是
已关闭并重新打开，与WAN mdb的连接丢失，必须重新建立
- 这是显而易见的广域网。有没有其他方式
来保持连接打开...为什么关闭记录集关闭它？
3.还有其他建议吗？
-------------------------------------------- ---------------
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2作为ADODB.Recordset
Dim prm1作为ADODB.Parameter
Dim cmd1作为ADODB.Command
设置rs =新ADODB.Recordset
设置rs2 =新ADODB.Recordset
设置cnxn =新ADODB.Connection
设置cmd1 =新ADODB.Command
cnxn.Provider =" Microsoft.Jet.OLEDB.4.0"
cnxn.Open" Data Source =" &安培; GetServer（）
设置prm1 = cmd1.CreateParameter（" PO"，adVarChar，adParamInput，
Len（GetSelectedPO（）））
prm1.Value = GetSelectedPO（）
使用cmd1
.ActiveConnection = cnxn
.CommandText =" HomeInvestigationPQuery"
.CommandType = adCmdStoredProc
.Parameters.Append prm1
结束
rs2.Open" HomeInvestigationTable"，CurrentProject.Connection，adOpenKeyset，
adLockOptimistic，adCmdTable
with rs
。打开cmd1 ,, adOpenForwardOnly，adLockReadOnly，adCmdStoredProc
请勿.EOF
rs2.AddNew
对于f = 0到5
rs2.Fields（f） = .Fields（f）
下一页f
rs2.Update
.MoveNext
循环
。关闭
rs2.Close
rs2.Open" HomeCaseloadTable"，CurrentProject.Connection，
adOpenKeyset ，adLockOptimistic，adCmdTable
cmd1.CommandText =" HomeCaseloadPQuery"
。打开cmd1 ,, adOpenForwardOnly，adLockReadOnly，adCmdStoredProc
Do While Not .EOF
rs2.AddNew
对于f = 0到2
rs2.Fields（f）= .Fields（f）
下一个f
rs2.Update
.MoveNext
循环
。关闭
结束用
rs2.Close
cnxn.Close
设置cmd1 =无什么
设置prm1 = Nothing
-
Darryl Kerkeslager
电力腐败。
绝对电力完全腐败。
知识就是力量。
见
I use the following code to retrieve to recordsets into two local tables,
from an mdb file over a WAN. Works fine, but I''m trying to tweak the speed,
and I have some questions:
1. I tried both an ADOX-created stored procedure and a regular Access
parameter query. After several tests, I came to the conclusion that there
was no statistcal difference between the two. Does this make sense, or
could I do something different to speed it up (the two queries are just
standard three-table joined Select queries with one parameter)?
2. When I wrap the two calls below in a third recordset, opened on the user
table, this speeds things up - in other words, when the recordset ''rs'' is
closed and re-opened, the connection to the WAN mdb is lost, and has to be
re-established - which is noticeable over the WAN. Is there any other way
to keep the connection open ... and why does closing the recordset close it?
3. Any other suggestions?
-----------------------------------------------------------
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim cmd1 As ADODB.Command
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set cnxn = New ADODB.Connection
Set cmd1 = New ADODB.Command
cnxn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnxn.Open "Data Source=" & GetServer()
Set prm1 = cmd1.CreateParameter("PO", adVarChar, adParamInput,
Len(GetSelectedPO()))
prm1.Value = GetSelectedPO()
With cmd1
.ActiveConnection = cnxn
.CommandText = "HomeInvestigationPQuery"
.CommandType = adCmdStoredProc
.Parameters.Append prm1
End With
rs2.Open "HomeInvestigationTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.Close
rs2.Close
rs2.Open "HomeCaseloadTable", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic, adCmdTable
cmd1.CommandText = "HomeCaseloadPQuery"
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 2
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.Close
End With
rs2.Close
cnxn.Close
Set cmd1 = Nothing
Set prm1 = Nothing
--
Darryl Kerkeslager
Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
推荐答案
实际上，如果你将记录集打开为静态，批量乐观，那么断开连接就没有好处。在您进行批量更新或断开连接之前，它不会再与数据库通信
。
断开连接的唯一原因是你真的想要关闭连接到conserver
资源。
Actually, if you open the recordset as static, batch optimistic, there''s no
benefit to disconnecting it. It''s not going to talk to the database again
until you do the batch update, disconnected or not. The only reason to
disconnect is if you really do want to close the connection to conserver
resources.
我已停止为记录集执行此操作，但是我不确定是否同样适用于
命令和参数对象。
Darryl Kerkeslager
电力腐败。
绝对电力绝对腐败。
知识就是力量。
见
这篇关于ADO参数查询和存储过程 - 以及Connections的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！