Open and Close Methods Example (VB)

Open and Close Methods Example (VB)

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

Public Sub OpenX()

    Dim cnn1 As ADODB.Connection
    Dim rstEmployees As ADODB.Recordset
    Dim strCnn As String
    Dim varDate As Variant

    ' Open connection.
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    Set cnn1 = New ADODB.Connection
    cnn1.Open strCnn
    
    ' Open employee table.
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.CursorType = adOpenKeyset
    rstEmployees.LockType = adLockOptimistic
    rstEmployees.Open "employee", cnn1, , , 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
    cnn1.Close

End Sub