Connection Close Method, Table Type Property Example (VB)

Connection Close Method, Table Type Property Example (VB)

Setting the ActiveConnection property to Nothing should "close" the catalog. Associated collections will be empty. Any objects that were created from schema objects in the catalog will be orphaned. Any properties on those objects that have been cached will still be available, but attempting to read properties that require a call to the provider will fail.

Sub CloseConnectionByNothing()

    Dim cnn As New Connection
    Dim cat As New Catalog
    Dim tbl As Table

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb;"
    Set cat.ActiveConnection = cnn
    Set tbl = cat.Tables(0)
    Debug.Print tbl.Type    ' Cache tbl.Type info
    Set cat.ActiveConnection = Nothing
    Debug.Print tbl.Type    ' tbl is orphaned
    ' Previous line will succeed if this was cached
    Debug.Print tbl.Columns(0).DefinedSize
    ' Previous line will fail if this info has not been cached

End Sub

Closing a Connection object that was used to "open" the catalog should have the same effect as setting the ActiveConnection property to Nothing.

Sub CloseConnection()

    Dim cnn As New Connection
    Dim cat As New Catalog
    Dim tbl As Table

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb;"
    Set cat.ActiveConnection = cnn
    Set tbl = cat.Tables(0)
    Debug.Print tbl.Type    ' Cache tbl.Type info
    cnn.Close
    Debug.Print tbl.Type    ' tbl is orphaned
    ' Previous line will succeed if this was cached
    Debug.Print tbl.Columns(0).DefinedSize
    ' Previous line will fail if this info has not been cached

End Sub