The Connection Object and the Connections Collection

After you’ve created an ODBCDirect workspace, you can connect to an ODBC data source. To connect to an ODBC data source, you can use the OpenConnection method to open a new Connection object, or you can use the OpenDatabase method to open a new Database object. This section explains how to use the Connection object. For information on how to use a Database object, see the following section, “The Database Object and the Databases Collection.”

A Connection object represents a connection to an ODBC database in an ODBCDirect workspace. The Connections collection contains all currently open Connection objects. When you open a Connection object, it is automatically appended to the Connections collection of the Workspace object. When you close a Connection object with the Close method, it is removed from the Connections collection.

Note Before you close a Connection object, close all open Recordset objects within it.

When you open a Connection object, a corresponding Database object is also created and appended to the Databases collection in the same workspace. This allows you to use the features of both the Connection and Database objects. For information on how to use the Database object from an ODBCDirect workspace and how to switch between Connection and Database objects, see the following section “The Database Object and the Databases Collection.”

The Connection object provides the following advantages for accessing ODBC data:

Asynchronous Connection Your application can connect to an ODBC data source asynchronously. Rather than pausing execution while the connection is established, your code can continue to perform other operations, and can later check to determine whether the connection was made successfully.
Asynchronous Queries Your application can run queries against your ODBC data source asynchronously. Rather than pausing execution while a long query runs, your code can perform other tasks, and then check later to determine whether the query has run successfully.
QueryDef Objects You can define QueryDef objects that represent queries in the ODBC data source.

Use the OpenConnection method to create a Connection object. The syntax of the OpenConnection method is:

Set connection = workspace.OpenConnection (name, options, readonly, connect)

The connection argument is the name of the new Connection object. The workspace argument is the name of an ODBCDirect Workspace object from which you’re creating the new Connection object.

The name argument indicates the name of the registered data source. You can reference the new Connection object by using either the data source name (DSN) or the Connection object’s ordinal position within its collection. The options argument determines if and when to prompt the user to establish the connection, and whether or not to open the connection asynchronously. The readonly argument controls the updatability of the data accessed through the connection. Set this argument to True to prevent updates; set it to False to allow updates.

The connect argument is a valid connection string that supplies parameters to the ODBC Driver Manager. These parameters can include user name, password, default database, and DSN, which overrides the value provided in the name argument.

The connection string must start with "ODBC;", and must contain a series of values needed by the driver to access the data. The actual connection string can vary depending on the data source you’re trying to access; different ODBC data sources require different parameters in the connect argument. Usually, the minimum requirement is a user ID, a password, and a DSN, as shown in the following example:

ODBC;UID=JamesK;PWD=OpenSesame;DSN=MasterData

When the ODBC driver processes the connection string and one or more of the parameters required by the data source is missing, the driver displays a dialog box that asks for the information. If you don’t want this dialog box displayed, you must make sure that the connection string has all the required information.

Note If you are trying to connect to a Microsoft SQL Server database that uses integrated security, omit the user ID (UID) and password (PWD) values because your Windows NT user name and password are automatically used. For example, the connection string may look something like the following:

ODBC;UID=;PWD=;DATABASE=Pubs;DSN=Pubs

See Also For more information about formatting connection strings, see Chapter 8, “Accessing External Data.”

The following example illustrates how to use the OpenConnection method to open a new Connection object.

Function OpenPubsConnection() As Boolean
	Dim wrk As Workspace, cnn As Connection, rst As Recordset, fld As Field
	Dim strConnect As String, strSQL As String

	On Error GoTo Err_OpenPubsConnection
	' Create connect string.
	strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
	' Create SQL string.
	strSQL = "SELECT * FROM Authors WHERE State = 'CA';"

	' Create ODBCDirect workspace.
	Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "Admin", "", dbUseODBC)
	' Open connection.
	Set cnn = wrk.OpenConnection("Pubs", dbDriverNoPrompt, False, strConnect)
	' Open recordset on connection.
	Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset)
	' Print values in recordset.
	Do Until rst.EOF
		For Each fld In rst.Fields
			Debug.Print fld.Name, fld.Value
		Next fld
		Debug.Print
		rst.MoveNext
	Loop
	OpenPubsConnection = True

Exit_OpenPubsConnection:
	On Error Resume Next
	rst.Close
	cnn.Close
	Exit Function

Err_OpenPubsConnection:
	VerboseErrorHandler
	OpenPubsConnection = False
	Resume Exit_OpenPubsConnection
End Function

After you’ve created a Connection object, you can open Recordset objects and run queries using the Connection object.

Note Most servers don’t support string expressions surrounded by double quotation marks (") in SQL statements such as:

SELECT * FROM Authors WHERE State = "CA"

When using a Microsoft Jet workspace, Microsoft Jet translates double quotation marks into single quotation marks (') before sending the statement to the server. However, when using an ODBCDirect workspace, no translation is performed and an error will occur. When writing SQL statements to be executed using an ODBCDirect connection, make sure to use single quotation marks around strings in SQL statements as shown in the following example:

SELECT * FROM Authors WHERE State = 'CA'