Close Method

Applies To Connection object, Database object, Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only–Type Recordset object, QueryDef object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object, Workspace object.

Description

Closes an open DAO object.

Syntax

object.Close

The object placeholder is an object variable that represents an open Connection, Database, Recordset, or Workspace object.

Remarks

Closing an open object removes it from the collection to which it's appended. Any attempt to close the default workspace is ignored.

If the Connection, Database, Recordset, or Workspace object named by object is already closed when you use Close, a run-time error occurs.

Caution If you exit a procedure that declares Connection, Database, or Recordset objects, those objects are closed, all pending transactions are rolled back, and any pending edits to your data are lost.

If you try to close a Connection or Database object while it has any open Recordset objects, the Recordset objects will be closed and any pending updates or edits will be canceled. Similarly, if you try to close a Workspace object while it has any open Connection or Database objects, those Connection and Database objects will be closed, which will close their Recordset objects.

Using the Close method on either an original or cloned Recordset object doesn't affect the other Recordset object.

To remove objects from updatable collections other than the Connections, Databases, Recordsets, and Workspaces collections, use the Delete method on those collections. You can't add a new member to the Containers, Documents, and Errors collections.

An alternative to the Close method is to set the value of an object variable to Nothing (Set dbsTemp = Nothing).

See Also Clone method, Delete method, OpenDatabase method, OpenRecordset method.

Specifics (Microsoft Access)

If a Visual Basic procedure contains an object variable that represents the database currently open in Microsoft Access, using the Close method on that object causes the variable to go out of scope. The Close method will not affect the database that is open in the Microsoft Access Database window.

Example

This example uses the Close method on both Recordset and Database objects that have been opened. It also demonstrates how closing a Recordset will cause unsaved changes to be lost.

Sub CloseX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

    ' Make changes to a record but close the recordset before
    ' saving the changes.
    With rstEmployees
        Debug.Print "Original data"
        Debug.Print "    Name - Extension"
        Debug.Print "    " & !FirstName & " " & _
            !LastName & " - " & !Extension
        .Edit
        !Extension = "9999"
        .Close
    End With

    ' Reopen Recordset to show that the data hasn't
    ' changed.
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    With rstEmployees
        Debug.Print "Data after Close"
        Debug.Print "    Name - Extension"
        Debug.Print "    " & !FirstName & " " & _
            !LastName & " - " & !Extension
        .Close
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example creates a Database object that points to the current database and opens a table-type Recordset object based on a Customers table in the database. The procedure uses the Close method on the Recordset object variable to free the memory resources it has been using. It uses the Set statement with the Nothing keyword to free resources used by the Database object variable.

You can also use the Close method of the Database object to close it and free memory. The Close method of the Database object doesn't actually close the database that's open in Microsoft Access; it only frees the resources used by the Database object variable.

Using an object's Close method and setting the object variable to Nothing are equivalent ways to free memory.

Sub UseClose()
    Dim dbs As Database, rst As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create table-type recordset.
    Set rst = dbs.OpenRecordset("Customers")
    .
    .
    .
    ' Close recordset to free memory.
    rst.Close    
    ' Free memory used by object variable.
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example opens the Customer recordset of the Nwindex.mdb database, counts how many records are available, and enters this number on Sheet1.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Customer")
Set resultsSheet = Sheets("Sheet1")
resultsSheet.Activate
With resultsSheet.Cells(1, 1)
    .Value = "Records in " & rs.Name & " table:"
    .Font.Bold = True
    .EntireColumn.AutoFit
End With
rs.MoveLast
resultsSheet.Cells(1, 2).Value = rs.RecordCount
rs.Close
db.Close