ADO connection state and object destruction Problem/Question

From: Eric (Eric_at_discussions.microsoft.com)
Date: 08/23/04 

Date: Mon, 23 Aug 2004 13:53:02 -0700

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