Help Thinker (Or anyone else as intelligent)

Ok, I've been reading several of your comments on disconnected recordsets and that it's not a good idea to pass out a connected recordset for what I believe to be leaving something open leading to memory problems.

Can you disconnect a connection from the recordset itself?

I keep seeing messages about setting the connection = Nothing.

However here is my problem.

I am working with SQL's tempdb and I'm storing multiple files so I can't close the connection because the instant I close the connection I lose the ability to access the table on tempdb. However I still want to pass out a recordset.

How now do I accomplish this?

How necessary is it for you to use SQL's tempdb? This sounds like a bad idea if you're going to be passing the recordsets (from a COM component?) to another process or application. I would recommend creating non-temporary tables, then deleting them when you're finished, rather than using SQL's built-in temporary table features.

Take this advice with a grain of salt, please, because my experience is with Oracle more than SQL Server. I'd normally leave this one for Thinker, but as he's on vacation, I thought I'd share my 2 cents' worth. :p

Thank you for your input lebb.

However I really need to be able to use the tempdb. My program is somewhat complex, but I've been developing a reporting program that must meet these parameters:

1. For the same user to submit multiple requests to the same report server (though most of the time if we're doing multiple reports we'll hit different servers)

2. If the program crashes it must be able to clean itself off. Thinker agreed with me in a prior post that tempdb was the easiest way to do this. I worked out the details and finally am able to work with tempdb.

I need to get recordsets so the user can make "selections" based on what is in our database.

I've worked out many of the details. (With many days of frustration to say the least).

So how do you clean off tables in Oracle if an application terminates abnormally? I can handle normal termination, it's the abnormal ones that leave garbage files all over the place. I had to use a process number to track multiple queries. The problem is recognizing when a process has terminated abnormally.

Any thoughts are always welcome and greatly appreciated.

I'll add my 2 cents to Lebbs'.

Using a disconnected recordset doesn't mean that you close the connection to your database.

Can you disconnect a connection from the recordset itself?

Yes, use

set rs.ActiveConnection = Nothing

Thats it!

Thanks Rive, somehow doing a search most of this forum people were setting the connection object = Nothing.

I missed setting the Activeconnection.

THANKS!

Well Rive

I tried this and I get the error message:

"Operation is not allowed when object is open"
when I try to set rs.ActiveConnection = Nothing

Can you do this? Or am I missing something?
I don't believe I want to close the recordset if I want to continue using it.

HELP!

:confused:

What are the properties of your recordset?

This is how I use it (from my DataAccess class)

Public Function OpenADORecordsetReadOnly(ByVal sSource As String) As ADODB.Recordset

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Set rs = OpenADORecordset(sSource, adOpenForwardOnly, adLockReadOnly)

'// Disconnect recordset
Set rs.ActiveConnection = Nothing

Set OpenADORecordsetReadOnly = rs

End Function


Private Function OpenADORecordset _
(ByVal sSource As String, _
eCursorType As CursorTypeEnum, _
eLockType As LockTypeEnum) As ADODB.Recordset

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.LockType = eLockType
rs.CursorType = eCursorType
rs.CursorLocation = adUseClient

rs.ActiveConnection = m_oAdoConnection
rs.Open sSource

Set OpenADORecordset = rs

End Function

I don't believe I want to close the recordset if I want to continue using it.
You're correct, that's why you don't close it, but disconnect it.