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.

'BeginConnectionStringVB

    'To integrate this code replace
    'the database, DSN or Data Source values
    
Public Sub ConnectionStringX()

   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 = "driver={SQL Server};server=srv;uid=sa;pwd=pwd;database=Pubs"
   Cnxn1.ConnectionTimeout = 30
   Cnxn1.Open
   
    ' Open a connection using a DSN and ODBC tags
   Set Cnxn2 = New ADODB.Connection
   Cnxn2.ConnectionString = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=;"
   Cnxn2.Open
   
    ' Open a connection using a DSN and OLE DB tags
   Set Cnxn3 = New ADODB.Connection
   Cnxn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
   Cnxn3.Open
   
    ' Open a connection using a DSN and individual
    ' arguments instead of a connection string
   Set Cnxn4 = New ADODB.Connection
   Cnxn4.Open "Pubs", "sa", "pwd"
 
    ' Display the state of the connections using
    ' GetState function from below
   MsgBox "Cnxn1 state: " & GetState(Cnxn1.State) & vbCr & _
      "Cnxn2 state: " & GetState(Cnxn2.State) & vbCr & _
      "Cnxn3 state: " & GetState(Cnxn3.State) & vbCr & _
      "Cnxn4 state: " & GetState(Cnxn4.State)

   Cnxn4.Close
   Cnxn3.Close
   Cnxn2.Close
   Cnxn1.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
'EndConnectionStringVB

ConnectionString, ConnectionTimeout, and State Properties Example (VB)

See Also