Syntax to reconnect a disconnected recordset?

  • Thread starter Thread starter Webtechie
  • Start date Start date
W

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
 
if you close it, you'll force the recordset to close

so instead, don't close the connection object, just set it to = Nothing
 
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?
 
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.
 
Back
Top