Opening and closing recordsets

[Amr Al-Amir]

I am using VB6 and I have a dataenvironment
what is the correct way of opening and closing recordsets

1 - to open all recordsets in sub main or form_load at the start of the application, and close all in form_unload

or

2- open each recordset when required and close it immediately after use

thanks

sorry, I supposed the stop button of the browser will prevent the submit reply press

[RhinoBull]

Try not to dblpost the same this. If you feel like you made a mistake simply use the EDIT functionality.

Anyway, there are multiple tutorials on how to use ADO library so have a look at them.

[salvelinus]

To answer your question, it depends.
As a general rule, I declare a recordset rs in a code module if needed throughout the app, or in the General Declarations area of a form module, if only needed there.
Then, if I only need the recordset rs for specific functions, I can open & close it as needed. I don't have to declare it again. It's like declaring strSQL at the start - I can keep putting whatever SQL I want into it.
But, if I need a second recordset, say to loop through values from rs, I'll declare that in the sub or function where needed and then close it & set it to Nothing. There may be times when you want more than one recordset to persist, and then treat both like I do for rs - maybe rs1 & rs2.

[si_the_geek]

As a general rule you should only keep recordsets open for as long as you actually need them (where they are declared is not as big an issue, and is basically personal choice).

There are a few reasons for this..

• recordsets use quite a bit of memory.. if you have a 'large' amount of data in recordset(s) then you will probably use up all of the physical memory on the computer - which means the computer will become much slower.
• having a recordset open can cause locking issues for anyone else (or any other programs), basically stopping them from reading/editing data that is in included in the recordset.
• if your program crashes, any currently open recordsets will not be closed. This means that memory usage and locking issues will continue - perhaps until the computer is rebooted.

There are situations where keeping a recordset open is the best option tho, as in Beacons ADO Tutorial (where the entire program is based on working with the data, and there are not expected to be long delays between edits).


No matter which you method use, an important thing is to minimise the data in the recordset(s), as it reduces memory & locking issues. eg: if you only need 3 fields from a table that has 10 fields, only get the 3 fields you need.

sorry, I supposed the stop button of the browser will prevent the submit reply press

Only if you are really quick! I have deleted the duplicates.

[Amr Al-Amir]

Thanks for reply.
I can be more specific

In my case the entire program is based on working with the data, and there are not expected to be long delays between edits.
but in the same time the recordsets are too large.

the problem is opening and closing take long time

so, what is the best practice?

[aikidokid]

Is it possible to open just certain parts of the data, edit it, and then close it and move onto the next section of data to work with?