Open and Close Methods Example (VB)

This example uses the Open and Close methods on both Recordset and Connection objects that have been opened.

'BeginOpenVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub OpenX()

   Dim Cnxn As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnxn As String
   Dim strSQLEmployees As String
   Dim varDate As Variant

   ' Open connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Set Cnxn = New ADODB.Connection
   Cnxn.Open strCnxn
   
   ' Open employee table
   Set rstEmployees = New ADODB.Recordset
   strSQLEmployees = "employee"
   rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable

   ' Assign the first employee record's hire date
   ' to a variable, then change the hire date
   varDate = rstEmployees!hire_date
   Debug.Print "Original data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fname & " " & _
      rstEmployees!lname & " - " & rstEmployees!hire_date
   rstEmployees!hire_date = #1/1/1900#
   rstEmployees.Update
   Debug.Print "Changed data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fname & " " & _
      rstEmployees!lname & " - " & rstEmployees!hire_date

   ' Requery Recordset and reset the hire date
   rstEmployees.Requery
   rstEmployees!hire_date = varDate
   rstEmployees.Update
   Debug.Print "Data after reset"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fname & " " & _
      rstEmployees!lname & " - " & rstEmployees!hire_date

   rstEmployees.Close
   Cnxn.Close
   Set rstEmployees = Nothing
   Set Cnxn = Nothing

End Sub
'EndOpenVB

See Also

Close Method | Connection Object | Open Method (ADO Connection) | Open Method (ADO Recordset) | Recordset Object

© 1998-2001 Microsoft Corporation. All rights reserved.