Syntax to reconnect a disconnected recordset?

[Webtechie]

Hello,

I'm not doing something right with the disconnected recordset. I am
searching the internet, but not finding something.

Could someone tell me how to reconnect this disconnected recordset?

It is looking at an Access database:

'Get connection from the pool
'****************************
myConnection.Open

'Create SQL Statement
'********************
mySQL = "select * from tblGuest " _
& " where employeeid = " & thisEmployeeID _
& " and deptid = " & deptID

With rsGuestdata
.CursorLocation = adUseClient
.Open mySQL, myConnection, adOpenDynamic, adLockOptimistic
.MoveLast
.MoveFirst
rsDeptCount = .RecordCount

'Now disconnect the recordset
'****************************
rsGuestdata.ActiveConnection = Nothing
End With

Now in another sub routine, I want to populate a userform with the
disconnected recordset.
===========================================
What is the correct syntax to put in the sub routine that opens the
dataform? I want to reconnect the recordset and populate the text fields.
===========================================

I've tried:

Set rsGuestdata = New ADODB.Recordset

'Is connection open?
'*******************
If myConnection Is Nothing Then
ConnectToDatabase
End If

'Get connection from the pool
'****************************
myConnection.Open

With rsGuestdata
.ActiveConnection = myConnection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With

But I'm gettin an error. This is my first time trying to use a recordset to
populate a userform. I'm trying to move the data out of my spreadsheet.
Better programming and all that...

Thanks.

Tony

 

[Patrick Molloy]

if you close it, you'll force the recordset to close

so instead, don't close the connection object, just set it to = Nothing

 

[Webtechie]

Patrick,

Sorry, but I'm not getting it.

I've seen that on some websites, so I have set the connection = nothing.

Now, in another sub routine, I want to use that recordset.

1) how to I reconnect it?
2) Do I set rsGuestData = NEW ADODB.RECORDSET
3) Is that creating another recordset?
3) Do I open the request all over again with a SQL Statement, connection,
cursortype, locktype, options?

 

[Tim Williams]

This link shows to to create a disconnected recordset and then reconnect it.

http://support.microsoft.com/kb/184397

Tim

 

[Patrick Molloy]

once you have populated the recordset, settign th econnection to Nothing
disconnects the recordset, but keeps it populated. You can re-connect by
openning the coonection object then setting it as the recordsets connection .
Tim's link is a good read, since this is expalined quite clearly.
And No, don't set the variable to a NEW recordset as you will definitely
lose any data. Unless of course, that was the intent.