VBA Working with ADO Making a connection to a data source


The first step in accessing a data source via ADO is creating an ADO Connection object. This is typically done using a connection string to specify the data source parameters, although it is also possible to open a DSN connection by passing the DSN, user ID, and password to the .Open method.

Note that a DSN is not required to connect to a data source via ADO - any data source that has an ODBC provider can be connected to with the appropriate connection string. While specific connection strings for different providers are outside of the scope of this topic, ConnectionStrings.com is an excellent reference for finding the appropriate string for your provider.

Const SomeDSN As String = "DSN=SomeDSN;Uid=UserName;Pwd=MyPassword;"

Public Sub Example()
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    If Not database Is Nothing Then
        '... Do work.
        database.Close          'Make sure to close all database connections.
    End If
End Sub

Public Function OpenDatabaseConnection(ConnString As String) As ADODB.Connection
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = New ADODB.Connection
    With database
        .ConnectionString = ConnString
        .ConnectionTimeout = 10             'Value is given in seconds.
    End With
    OpenDatabaseConnection = database
    Exit Function
    Debug.Print "Database connection failed. Check your connection string."
End Function

Note that the database password is included in the connection string in the example above only for the sake of clarity. Best practices would dictate not storing database passwords in code. This can be accomplished by taking the password via user input or using Windows authentication.