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

hi

this is simple but u must implement when ur going to pass query to the same recordset

befor passing next query u must close the recordset using code given below

rsAllocation.close
set rsAllocation=Nothing

then u can pass a new query to the same recordset its not give any error message

bye
wish u happy christmas and new year

brefore rsallocation.OPEN statement put this check

if rsallocation.STATE then rsallocation.close

Thanks for the help. Let me make sure I got this correct.

In my Form_Load event I open the recordset like:

Set cnAllocation = New ADODB.Connection
Set rsAllocation = New ADODB.Recordset
cnAllocation.CursorLocation = adUseClient
cnAllocation.Open strConnection

With my first procedure I will do this:

rsAllocation.Open strSQL
...addtional code
rsAllocation.close
set rsAllocation=Nothing

when I call my next procedure I will do this

if rsallocation.STATE then rsallocation.close
rsAllocation.Open strSQL
...addtional code
rsAllocation.close
set rsAllocation=Nothing

and then repeat this for each procedure. Is this correct? Again, sorry for bothering everyone with something so simple.

TIA
David

You do not need to destroy the recordset object - so do not do "set rs = nothing". Once you have created the recordset object it is reusable as long as the recordset is closed before the next use. Reusing the object results in better perfomance and memory usage.

Anyway - here it is:

Set cnAllocation = New ADODB.Connection
cnAllocation.CursorLocation = adUseClient
cnAllocation.Open strConnection

With my first procedure I will do this:

Set rsAllocation = New ADODB.Recordset
rsAllocation.Open strSQL
...addtional code
if rsallocation.STATE then rsallocation.close

when I call my next procedure I will do this

rsAllocation.Open strSQL
...addtional code
if rsallocation.STATE then rsallocation.close
...
'the end of your recordsets
set rs = nothing
if cn.State then cn.close
set cn = nothing

Thanks for the info. It has been a great help.

David

Happy to help.