ADO Connection Object ConnectionString Property

Contains the information used to establish a connection to a data source.

ConnectionString Property Return Values (ADO Connection Object)

Sets or returns a String value.

ConnectionString Property Remarks (ADO Connection Object)

Use the ConnectionString property to specify a data source by passing a detailed connection string containing a series of argument = value statements separated by semicolons.

ADO supports seven arguments for the ConnectionString property; any other arguments pass directly to the provider without any processing by ADO. The arguments ADO supports are as follows:

Argument	Description
Provider	Specifies the name of the provider to use for the connection.
DataSource	Specifies the name of a data source for the connection, for example, an Oracle database registered as an ODBC data source.
UserID	Specifies the user name to use when opening the connection.
Password	Specifies the password to use when opening the connection.
FileName	Specifies the name of a provider-specific file (for example, a persisted data source object) containing preset connection information.
RemoteProvider	Specifies the name of a provider to use when opening a client-side connection (Remote Data Service only).
RemoteServer	Specifies the path name of the server to use when opening a client-side connection (Remote Data Service only).

After you set the ConnectionString property and open the Connection object, the provider may alter the contents of the property, for example, by mapping the ADO-defined argument names to their provider equivalents.

The ConnectionString property automatically inherits the value used for the ConnectionString argument of the ADO Connection Object Open Method, so you can override the current ConnectionString property during the Open method call.

Because the File Name argument causes ADO to load the associated provider, you cannot pass both the Provider and File Name arguments.

The ConnectionString property is read/write when the connection is closed and read-only when it is open.

Remote Data Service Usage: When used on a client-side Connection object, the ConnectionString property can only include the Remote Provider and Remote Server parameters.

ConnectionString Property Example (ADO Connection Object)

This Visual Basic example demonstrates different ways of using the ConnectionString property to open a Connection object. It also uses the ConnectionTimeout property to set a connection timeout period, and the ADO Connection Object State Property to check the state of the connections. The GetState function is required for this procedure to run.

Public Sub ConnectionStringX()

Dim cnn1 As ADODB.Connection

Dim cnn2 As ADODB.Connection

Dim cnn3 As ADODB.Connection

Dim cnn4 As ADODB.Connection

' Open a connection without using a Data Source Name (DSN).

Set cnn1 = New ADODB.Connection

cnn1.ConnectionString = "driver={SQL Server};" & _

"server=bigsmile;uid=sa;pwd=pwd;database=pubs"

cnn1.ConnectionTimeout = 30

cnn1.Open

' Open a connection using a DSN and ODBC tags.

Set cnn2 = New ADODB.Connection

cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"

cnn2.Open

' Open a connection using a DSN and OLE DB tags.

Set cnn3 = New ADODB.Connection

cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"

cnn3.Open

' Open a connection using a DSN and individual

' arguments instead of a connection string.

Set cnn4 = New ADODB.Connection

cnn4.Open "Pubs", "sa", "pwd"

' Display the state of the connections.

MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _

"cnn2 state: " & GetState(cnn1.State) & vbCr & _

"cnn3 state: " & GetState(cnn1.State) & vbCr & _

"cnn4 state: " & GetState(cnn1.State)

cnn4.Close

cnn3.Close

cnn2.Close

cnn1.Close

End Sub

Public Function GetState(intState As Integer) As String

Select Case intState

Case adStateClosed

GetState = "adStateClosed"

Case adStateOpen

GetState = "adStateOpen"

End Select

End Function