Connection Object, Connections Collection Example

This example demonstrates the Connection object and Connections collection by opening a Microsoft Jet Database object and two ODBCDirect Connection objects and listing the properties available to each object.

Sub ConnectionObjectX()

    Dim wrkJet as Workspace
    Dim dbsNorthwind As Database
    Dim wrkODBC As Workspace
    Dim conPubs As Connection
    Dim conPubs2 As Connection
    Dim conLoop As Connection
    Dim prpLoop As Property

    ' Open Microsoft Jet Database object.
    Set wrkJet = CreateWorkspace("NewJetWorkspace", _
        "admin", "", dbUseJet)
    Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

    ' Create ODBCDirect Workspace object and open Connection
    ' objects.
    Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
        "admin", "", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
    Set conPubs2 = wrkODBC.OpenConnection("Connection2", , _
        True, "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    Debug.Print "Database properties:"

    With dbsNorthwind
        ' Enumerate Properties collection of Database object.
        For Each prpLoop In .Properties
            On Error Resume Next
            Debug.Print "  " & prpLoop.Name & " = " & _
                prpLoop.Value
            On Error GoTo 0
        Next prpLoop
    End With

    ' Enumerate the Connections collection.
    For Each conLoop In wrkODBC.Connections
        Debug.Print "Connection properties for " & _
            conLoop.Name & ":"

        With conLoop
            ' Print property values by explicitly calling each
            ' Property object; the Connection object does not
            ' support a Properties collection.
            Debug.Print "  Connect = " & .Connect
            ' Property actually returns a Database object.
            Debug.Print "  Database[.Name] = " & _
                .Database.Name
            Debug.Print "  Name = " & .Name
            Debug.Print "  QueryTimeout = " & .QueryTimeout
            Debug.Print "  RecordsAffected = " & _
                .RecordsAffected
            Debug.Print "  StillExecuting = " & _
                .StillExecuting
            Debug.Print "  Transactions = " & .Transactions
            Debug.Print "  Updatable = " & .Updatable
        End With

    Next conLoop

    dbsNorthwind.Close
    conPubs.Close
    conPubs2.Close
    wrkJet.Close
    wrkODBC.Close

End Sub