Very Computer

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.

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.

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.

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.