Expand Minimize

ConnectionString, ConnectionTimeout, and State Properties Example (VB)

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.

Note Note

If you are connecting to a data source provider that supports Windows authentication, you should specify Trusted_Connection=yes or Integrated Security = SSPI instead of user ID and password information in the connection string.


    'To integrate this code replace
    'the database, DSN or Data Source values
Public Sub Main()
    On Error GoTo ErrorHandler

    Dim Cnxn1 As ADODB.Connection
    Dim Cnxn2 As ADODB.Connection
    Dim Cnxn3 As ADODB.Connection
    Dim Cnxn4 As ADODB.Connection
     ' Open a connection without using a Data Source Name (DSN)
    Set Cnxn1 = New ADODB.Connection
    Cnxn1.ConnectionString = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    MsgBox "Cnxn1 state: " & GetState(Cnxn1.State)
     ' Open a connection using a DSN and ODBC tags
     ' It is assumed that you have created DSN 'Pubs' with a user name as
     ' 'MyUserId' and password as 'MyPassword'.
    Set Cnxn2 = New ADODB.Connection
    Cnxn2.ConnectionString = "Data Source='Pubs';" & _
        "User ID='MyUserId';Password='MyPassword';"
    Cnxn2.ConnectionTimeout = 30
    MsgBox "Cnxn2 state: " & GetState(Cnxn2.State)
     ' Open a connection using a DSN and OLE DB tags
     ' It is assumed that you have created DSN 'Pubs1' with windows authentication.
    Set Cnxn3 = New ADODB.Connection
    Cnxn3.ConnectionString = "Data Source='Pubs1';"
    MsgBox "Cnxn2 state: " & GetState(Cnxn3.State)
     ' Open a connection using a DSN and individual
     ' arguments instead of a connection string
     ' It is assumed that you have created DSN 'Pubs' with a user name as
     ' 'MyUserId' and password as 'MyPassword'.
    Set Cnxn4 = New ADODB.Connection
    Cnxn4.Open "Pubs", "MyUserId", "MyPassword"
    MsgBox "Cnxn4 state: " & GetState(Cnxn4.State)
    ' clean up
    Set Cnxn1 = Nothing
    Set Cnxn2 = Nothing
    Set Cnxn3 = Nothing
    Set Cnxn4 = Nothing
    Exit Sub
    ' clean up
    If Not Cnxn1 Is Nothing Then
        If Cnxn1.State = adStateOpen Then Cnxn1.Close
    End If
    Set Cnxn1 = Nothing
    If Not Cnxn2 Is Nothing Then
        If Cnxn2.State = adStateOpen Then Cnxn2.Close
    End If
    Set Cnxn2 = Nothing
    If Not Cnxn3 Is Nothing Then
        If Cnxn3.State = adStateOpen Then Cnxn3.Close
    End If
    Set Cnxn3 = Nothing
    If Not Cnxn4 Is Nothing Then
        If Cnxn4.State = adStateOpen Then Cnxn4.Close
    End If
    Set Cnxn4 = Nothing
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
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
© 2014 Microsoft