Multiple Recordsets

[Rick]

How do I open more than one table at a time in the same database using ODBC
and ADO? The following code abends when I attempt to open the second table.

Sub MultipleTables()
'
' Create the Recordset attributes
'
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rcd_cnt1 As Integer
Dim rcd_cnt2 As Integer
'
' Initialize the Recordset attributes
'
rcd_cnt1 = 1
rcd_cnt2 = 1
'
' Create the Recordset objects
'
Set rs1 = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")
'
' Open the recordset objects
'
With rs1
.Source = "Table1"
.ActiveConnection = "DatabaseName"
.LockType = adLockOptimistic
.Open
End With

With rs2
.Source = "Table2"
.ActiveConnection = "DatabaseName"
.LockType = adLockOptimistic
.Open
End With

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

End Sub


Thanks for your assistance.

 

[Douglas J. Steele]

Try declaring a Connection object and instantiating it, then setting the
ActiveConnection for both recordsets to that Connection object.

 

[Rick]

Thanks for your assistance. I found a typo that corrected the problem. The
problem was that the error message masked the actual error. I'm still pretty
new to VBA and am not used to what to look for if the error message doesn't
appear to be related to the actual problem you're having.