Keeping RecordSet Open

[CanonShooter]

In my program, I am setting a RecordSet(RS) Source. Then I open the RS, and load the frist 25 records into my form. All that works okay. I have a Next button on there, and when I click it, the RS isn't open anymore, and it dies.

I'm not closing the RS or setting it to nothing or nil. From one Public Sub to another, shouldn't it stay "open", or am I missing something?

TIA

 

[joerd]

Did you Dim the recordset at the procedure level (local) or at the module level (global)?

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[TCARPENTER1]

Hi CanonShooter,

You didn't mention how you retrieved the first 25 records, if you've done it with an SQL statement like: "SELECT TOP 25 * FROM ..." you'll just need to rerun your SQL statement again - in other words, you'll need to close the recordset, and then reopen it again.

HTH
Todd

 

[CanonShooter]

I Dim'ed the recordset like this:

Dim RS as New ADODB.Object

I had to have the "New" in there, else it didn't work as expected. I did use a SQL statement like mentioned. When I reran the SQL statement, it didn't know what RS was, so I had to redim it, and Open the RS again.

 

[TCARPENTER1]

Hi CanonShooter,

The recordset isn't closed, it just doesn't contain anymore than 25 records. You shouldn't have to redim RS though. So somewhere above your modules in the General area of your code, change:

Dim RS as New ADODB.Object

to

Public RS as New ADODB.Object

You may also try:

Public RS as New ADODB.[purple][b]Recordset[/b][/purple]

HTH
Todd

 

[johnwm]

If you are dimming any objects As New then be very careful about closing them and setting bthem to Nothing when you're finished with them, as they are regular sources of memory leaks if left live. It's usually better to Dim xx as ADODB.Recordset, then instantiate it when required, use it and kill it all in one place.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[CanonShooter]

In my General Declarations, I used:

Public RS As New ADODB.Recordset

When the routine that starts filling a table runs, I get a message, and all it says is "Catastrophic Failure".

So if the Dim statement isn't in the routine, it won't work. I currently Dim it right before I do a RS.Source = "Select * FROM Table", and then a Call RS.Open......

 

[CajunCenturion]

Like johnwm, I strongly advise against using the New in the Dim statement, and I would never use New with any object in general declarations. That is asking for memory troubles.

==> In my General Declarations, I used: Public RS As New ADODB.Recordset
==> I currently Dim it right before I do a RS.Source = "Select * FROM Table", and then a Call RS.Open.

That second one cannot be in the general declartions, so if you're doing both, you have two different recordsets in play, each with a different scope, and although it's legal, it can be very confusing.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CanonShooter]

I see what you are saying. I can take the general declaration out, and the thing loads records. If I take the Dim out of the Public Sub, it will croak with the "Catastrophic Failure" message. If I leave the Dim in the one Sub, when I run another Sub that loads the next set of records, it errors out because it doesn't know what RS is (I never closed or nil'd it).

It looks very simple, but it doesn't work. Frustrating

 

[CajunCenturion]

It's all about the scope. If you declare a recordset inside of a sub, then it's scope is limited to that sub, meaning it cannot be used in any other sub.

Sounds like you need to declare the RS in the general declarations section, but without the 'new', because you use it in several different sub. You need to allocate it when necessary, possibly in the Form_Load event, manage it during the run, and finally close and release it, possibly in the Form_Unload event, when you're done.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein