I'm teaching myself VB6 and working with ADO recordsets to connect to my Access97 db. Looking at several examples and I'm sort of confused on the sequence of events for what I want to do.

1 DB with several tables. On my Form_Load event I call 4 different procedures to pull data from 4 different tables.

In my Declarations I have:
Dim cnAllocation As ADODB.Connection
Dim rsAllocation As ADODB.Recordset

In the Form_Load I setup my connection and set my recordset like:
Set cnAllocation = New ADODB.Connection
Set rsAllocation = New ADODB.Recordset
cnAllocation.CursorLocation = adUseClient
cnAllocation.Open strConnection

rsAllocation.Open strSQL

My first procedure fires and grabs the data. This is where I'm confused. After the procedure is finished do I rsAllocation.Close and then in the next procedure can I issue rsAllocation.Open strSQL or do I leave it Open? Right now I run the first procedure, then on the next procedure I issue a rsAllocation.Open strSQL

I've tried both and I get the error "Not allowed when object is open or closed" depending on whether it was opened or closed by the previous procedure. Do I need to open a new recordset for each procedure even though I'm still connecting to the same database but just using a different table for each procedure.

I apologize if this is a very simple question but all the examples I have found seem to deal with just one table. If someone could lay out the squence needed to do this I would appreciate it. FWIW the procedures all work when run by themselves but the problem comes when I try to run one after another.

TIA
David