ConnectionString, ConnectionTimeout, and State Properties Example (ADO)


		Contents Index Topic Contents

ConnectionString, ConnectionTimeout, and State Properties Example

This 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 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