ADO disconnected rs NOT disconnected

ADO disconnected rs NOT disconnected

Post by Jim » Thu, 25 May 2000 04:00:00



Simply put, I am have a function that is suppose to return a
disconnected recordset, but I have discovered that the recordset is not
disconnected, it is still holding a connection to the database untill
the recordset is set to nothing.

Here is my Function

Public Function GetPerson() As adodb.Recordset

Dim rs as adodb.recordset

'uses string to fill in the sql statement
sSql = ""

sSql = "SELECT blah blah......."  'long select statement but does
return what I need.

Set rs = New adodb.Recordset

OpenConnClient   'a sub that opens the connection to the database. and
the Conn.CursorLocation = adUseClient

With rs
    .ActiveConnection = Conn
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Open (sSql)
End With

Set rs.ActiveConnection = Nothing
Conn.Close
Set Conn = Nothing

Set GetPerson = rs
Set rs = Nothing

End Function

In my form I set rsPerson = DA.GetPerson , so why is rsPerson still
connected to the database?

TIA,

Jim

Never play leapfrog with a unicorn.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

ADO disconnected rs NOT disconnected

Post by Michael D. Lon » Thu, 25 May 2000 04:00:00


Try the following:

With rs
    ' use "Set" to assign; otherwise you are passing
    ' the default connection property (which is a string)
    ' and a secondary connection is spawned.
    Set .ActiveConnection = Conn
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Open (sSql)
End With

' don't use "Set" on disconnect, as it is slower
rs.ActiveConnection = Nothing

--
Michael D. Long
http://extremedna.homestead.com


Quote:> Simply put, I am have a function that is suppose to return a
> disconnected recordset, but I have discovered that the recordset is not
> disconnected, it is still holding a connection to the database untill
> the recordset is set to nothing.

> Here is my Function

> Public Function GetPerson() As adodb.Recordset

> Dim rs as adodb.recordset

> 'uses string to fill in the sql statement
> sSql = ""

> sSql = "SELECT blah blah......."  'long select statement but does
> return what I need.

> Set rs = New adodb.Recordset

> OpenConnClient   'a sub that opens the connection to the database. and
> the Conn.CursorLocation = adUseClient

> With rs
>     .ActiveConnection = Conn
>     .CursorLocation = adUseClient
>     .CursorType = adOpenStatic
>     .LockType = adLockBatchOptimistic
>     .Open (sSql)
> End With

> Set rs.ActiveConnection = Nothing
> Conn.Close
> Set Conn = Nothing

> Set GetPerson = rs
> Set rs = Nothing

> End Function

> In my form I set rsPerson = DA.GetPerson , so why is rsPerson still
> connected to the database?

> TIA,

> Jim

> Never play leapfrog with a unicorn.

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

ADO disconnected rs NOT disconnected

Post by Jim » Thu, 25 May 2000 04:00:00


Michael,

It always comes down to the little things in life doesn't it?  

I greatly appreciate you spotting the missing set command.

In the morning I will make the needed change (also will remove the set from the nothing line)

Much thanks,

Jim.

* Sent from Devdex.com http://www.devdex.com The Web Developers Index *
The world's largest index site for web developers.

 
 
 

ADO disconnected rs NOT disconnected

Post by Paul Stone » Fri, 23 Jun 2000 04:00:00


I'm using disconnect recordsets virtually the same way, however mine do not
seem to still be connected.  Let me ask you why you think the recordset is
stll connected and how you are checking it.

Thanks

Paul


Quote:> Simply put, I am have a function that is suppose to return a
> disconnected recordset, but I have discovered that the recordset is not
> disconnected, it is still holding a connection to the database untill
> the recordset is set to nothing.

> Here is my Function

> Public Function GetPerson() As adodb.Recordset

> Dim rs as adodb.recordset

> 'uses string to fill in the sql statement
> sSql = ""

> sSql = "SELECT blah blah......."  'long select statement but does
> return what I need.

> Set rs = New adodb.Recordset

> OpenConnClient   'a sub that opens the connection to the database. and
> the Conn.CursorLocation = adUseClient

> With rs
>     .ActiveConnection = Conn
>     .CursorLocation = adUseClient
>     .CursorType = adOpenStatic
>     .LockType = adLockBatchOptimistic
>     .Open (sSql)
> End With

> Set rs.ActiveConnection = Nothing
> Conn.Close
> Set Conn = Nothing

> Set GetPerson = rs
> Set rs = Nothing

> End Function

> In my form I set rsPerson = DA.GetPerson , so why is rsPerson still
> connected to the database?

> TIA,

> Jim

> Never play leapfrog with a unicorn.

> Sent via Deja.com http://www.deja.com/
> Before you buy.