Re: ADO connection state and object destruction Problem/Question

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 08/23/04 

Date: Mon, 23 Aug 2004 15:37:14 -0700

The fact that you're disposing the objects will doubly assure that the
objects will be released. However, the pooled connections will remain in
place until the application ends. 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:C5E4633F-D89D-40F4-A199-C013540F9C23@microsoft.com...
> Greetings:
> I have a function(in a module) that  returns an ADO recorset. I call this
> function from a windows form. How do I control the connection state of the
> CONN object and recordset objects when I close the forms. I wish to avoid 
> any
> memory leaks and be sure that I close and destroy and objects when the
> application exits.  Here is how I call the function from a button and the
> function itself is just below. Thanks all.
> Private Sub CmsSearch_Click()
> Set DataGridEmp.DataSource = FindEmployees(txtSearchName.Text,
> TxtEmpCode.Text)
> End Sub
>
> Public Function FindEmployees(ByVal Name As String, ByVal EmpID As String)
> As ADODB.Recordset
> Dim rs As ADODB.Recordset
>      Set rs = New ADODB.Recordset
>      Dim conn As ADODB.Connection
>      Set conn = New ADODB.Connection
>      conn.Open "PROVIDER=MSDASQL;driver={SQL
> Server};server=CMSOPEN;uid=User1;pwd=;database=CMSOPEN;"
>
>      SQL2 = SQL2 & "SELECT     HBM_PERSNL.EMPLOYEE_CODE AS [Employee 
> Code],
> HBM_PERSNL.EMPLOYEE_NAME AS Name, HBM_PERSNL.PHONE_NO AS Extension,"
>      SQL2 = SQL2 & "HBL_OFFICE.OFFC_DESC AS Office FROM HBM_PERSNL INNER
> JOIN "
>      SQL2 = SQL2 & "HBL_OFFICE ON HBM_PERSNL.OFFC = HBL_OFFICE.OFFC_CODE "
> SQL2 = SQL2 & "WHERE     (HBM_PERSNL.INACTIVE = 'n') AND "
>
> If Name = "" Then
>  SQL2 = SQL2 & "(HBM_PERSNL.EMPLOYEE_NAME = HBM_PERSNL.EMPLOYEE_NAME) AND 
> "
>  Else
>  SQL2 = SQL2 & "(HBM_PERSNL.EMPLOYEE_NAME LIKE '%" & Name & "%') AND "
>  End If
>  If EmpID = "" Then
>  SQL2 = SQL2 & "(HBM_PERSNL.EMPLOYEE_CODE = HBM_PERSNL.EMPLOYEE_CODE) "
>  Else
>   SQL2 = SQL2 & "(HBM_PERSNL.EMPLOYEE_CODE LIKE '%" & EmpID & "%') "
>  End If
>
>      SQL2 = SQL2 & " ORDER BY HBM_PERSNL.EMPLOYEE_NAME"
>
>      rs.Open SQL2, conn, adOpenStatic, _
>                  adLockReadOnly, adCmdText
>
>   If rs.RecordCount > 0 Then
>       Set FindEmployees = rs
>          Else
>               MsgBox "Your request returned zero records", vbInformation
>          Exit Function
>      End If
>
>      Set rs = Nothing
>      Set conn = Nothing
> End Function
>