Proper connection management is critical for efficient use of both client and server resources. Although ADO will disconnect automatically when an object goes out of scope, it is proper coding technique to explicitly close the connections your application opens. This also ensures that any server-side resources are released.

To see a demonstration of how to connect to a data source, click your modem speed or Download below.

(Requires the Microsoft Windows Media Player.)

Connecting to a Data Source

Once you have specified an OLE DB data provider and have passed the ConnectionString information, you use the Open method to establish a connection to the data source.

The following example code creates a connection to a Microsoft SQL Server database called StateUBookstore on the server called MSERIES1 using the SQL Server OLE DB data provider:

Sub cmdConnect_Click()
    ' The connection object variable cnStateUBookstore
    ' was declared at Module level
    ' Instantiate the connection object variable
    Set cnStateUBookstore = New Connection

    ' Establish a connection
    With cnStateUBookstore
        .Provider = "SQLOLEDB"
        .ConnectionString = "User ID=sa;" & _
                            "Data Source=MSERIES1;" & _
                            "Initial Catalog=StateUBookstore"
        ' Open the connection
        .Open
    End With
End Sub
  

Disconnecting from a Data Source

Once you have finished with the connection, you use the Close method to disconnect from a data source. In the case of a SQL Server, any server-side resources that were in use under this active connection will be released. It is proper coding technique for all open connections to be closed before the application is terminated.

The following example code closes an active connection to a data source and releases the Connection object variable:

Sub cmdClose_Click()
    cnStateUBookstore.Close
    Set cnStateUBookstore = Nothing
End Sub