在rsTemp.OPEN的时候总是提示对象已经打开,无法操作
Private Sub Command3_Click()
Dim cn2 As New ADODB.Connection '目标数据
Dim rsTemp As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim connectionstring As String
Dim conn As New ADODB.Connection '目标数据
Dim sql As String
cn2.Open "Provider=SQLOLEDB.1;User ID=sa;pwd=;Initial Catalog=test;Data Source=192.168.111.7 "
'Record1是后台数据库的表
rs2.Open "aaaaaaa ", cn2, adOpenDynamic, adLockBatchOptimistic, adCmdTable
rs.CursorLocation = adUseClient
connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + out.Text + ";Persist Security Info=False "
conn.Open connectionstring
sql = "select bh,rq,sj,sd,jh from SfData where jh <> 0 and rq like ' " & Text1.Text & " ' "
rs.Open sql, conn, adOpenKeyset, adLockPessimistic
Set DataGrid1.DataSource = rs
rs.MoveFirst
While Not rs.EOF
rsTemp.Open "select top 1 * from aaaaaaa where bh= ' " & rs( "bh ") & " ' and rq= ' " & rs( "rq ") & " ' and sd= ' " & rs( "sd ") & " ' ", cn2, adOpenDynamic, adLockReadOnly
'判断记录没有重复则插入表中
If rsTemp.EOF Then
rs2.AddNew
For i = 0 To rs2.Fields.Count - 1
rs2(i) = rs(i)
Next
End If
Wend
End Sub
[解决办法]
凡是在每个记录集的open之前,加上下面的语句
if 记录集.state then 记录集.close
set 记录集=nothing
