Connection Object

Description

A Connection object represents a connection to an ODBC database (ODBCDirect workspaces only).

Remarks

A Connection is a non-persistent object that represents a connection to a remote database. The Connection object is only available in ODBCDirect workspaces (that is, a Workspace object created with the type option set to dbUseODBC).

Note Code written for earlier versions of DAO can continue to use the Database object for backward compatibility, but if the new features of a Connection are desired, you should revise code to use the Connection object. To help with code conversion, you can obtain a Connection object reference from a Database by reading the Connection property of the Database object. Conversely, you can obtain a Database object reference from the Connection object's Database property.

Properties

Connect property, Database property, Name property, QueryTimeout property, RecordsAffected property, StillExecuting property, Transactions property, Updatable property.

Methods

Cancel method, Close method, CreateQueryDef method, Execute method, OpenRecordset method.

See Also Connection property, Database object.

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