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
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. .Open End With OpenDatabaseConnection = database Exit Function Handler: 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.