A Connections collection contains the current Connection objects of a Workspace object. (ODBCDirect workspaces only).
Remarks
When you open a
Connection object, it is automatically appended to the
Connections collection of the
Workspace. When you close a
Connection object with the
Close method, it is removed from the
Connections collection. You should close all open
Recordset objects within the
Connection before closing it.
At the same time you open a
Connection object, a corresponding
Database object is created and appended to the
Databases collection in the same
Workspace, and vice versa. Similarly, when you close the
Connection, the corresponding
Database is deleted from the
Databases collection, and so on.
The Name property setting of a Connection is a string that specifies the path of the database file. To refer to a Connection object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:
- Connections(0)
- Connections("name")
- Connections![name]
Note
You can open the same data source more than once, creating duplicate names in the Connections collection. You should assign Connection objects to object variables and refer to them by variable name.
|
Note
ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine. A Connections collection contains the current Connection objects of a Workspace object (ODBCDirect workspaces only).
|
Methods
Properties
Example
This example demonstrates the Connection object and Connections collection by opening a Database object and two ODBCDirect Connection objects and listing the properties available to each object.
Sub ConnectionObjectX()
Dim wrkAcc 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 a Database object.
Set wrkAcc = CreateWorkspace("NewWorkspace", _
"admin", "", dbUseJet)
Set dbsNorthwind = wrkAcc.OpenDatabase("Northwind.mdb")
' Create ODBCDirect Workspace object and open Connection
' objects.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
' Note: The DSNs referenced below must be configured to
' use Microsoft Windows NT Authentication Mode to
' authorize user access to the Microsoft SQL Server.
Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
"ODBC;DATABASE=pubs;DSN=Publishers")
Set conPubs2 = wrkODBC.OpenConnection("Connection2", , _
True, "ODBC;DATABASE=pubs;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
wrkAcc.Close
wrkODBC.Close
End Sub