can't open more than one recordsets with a same connection object in 'for' circle?

[ling_lh]

Then I changed my code to like this:
why does the code don't work either?
Set Collection(i) = New ADODB.Recordset
Set CollectCnn(i) = New ADODB.Connection
CollectCnn(i) = PeopleCnn
CollectCnn(i).Open
Collection(i).Open Sql, Collection(i), 3, 3<error>
error message:the recordset using a connection that is closed or invalid.
why?
Thanks for any message.

[DrewDog_21]

One, the reason that the code you have gives you that error
is one of simple syntax. You mistakenly list the Recordset
object instead of the Connection object in your .Open
command. Your code is this:

Set Collection(i) = New ADODB.Recordset 
Set CollectCnn(i) = New ADODB.Connection 
CollectCnn(i) = PeopleCnn 
CollectCnn(i).Open 
Collection(i).Open Sql, Collection(i), 3, 3 'this is the offending line here

The line Collection(i).Open Sql, Collection(i), 3, 3 should
instead be Collection(i).Open Sql, CollectCnn(i), 3, 3

Two, I rearranged some of the code in a for loop (plus I
deleted the PeopleCnn part since I don't know what that
is), and I did not get an error "can't open more than one
recordsets with a same connection object in 'for' circle"

It looks like this

Private Sub Command1_Click()

Dim CollectCnn As ADODB.Connection
Dim Collection(10) As ADODB.Recordset
Dim i

Set CollectCnn = New ADODB.Connection
CollectCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Archivos de programa\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"

For i = 1 To 10
    Set Collection(i) = New ADODB.Recordset
    Collection(i).Open "Select * From Employees", CollectCnn, 3, 3
Next

MsgBox "Done!"

End Sub

hope that helps and good luck

[ling_lh]

Thank you.
I realized my mistake after that.
The code you suggested below could work,and no erro occured.
But I want all the recordset keep the state of 'open',I can't do that.
In each circle,a new recordset is opened,but the previous one is close.
How to manage this?
Thanks very much.

[DrewDog_21]

i added some Debug.Print statements after the code example
I posted, and according to what printed, the recordsets are
all open...

Private Sub Command1_Click()

Dim CollectCnn As ADODB.Connection
Dim Collection(10) As ADODB.Recordset
Dim i

Set CollectCnn = New ADODB.Connection
CollectCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Archivos de programa\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"

For i = 1 To 10
    Set Collection(i) = New ADODB.Recordset
    Collection(i).Open "Select * From Employees", CollectCnn, 3, 3
Next

For i = 1 To 10
    Debug.Print "Open = " & adStateOpen & " Closed = " & adStateClosed & " State = " & Collection(i).State
Next

MsgBox "Done!"

End Sub

that works, so I am not really sure what to tell you...