Questions And Answers

More Tutorials

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, 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.


In this page (written and validated by ) you learned about VBA Working with ADO . What's Next? If you are interested in completing VBA tutorial, your next topic will be learning about: VBA Attributes.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue:

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using, you agree to have read and accepted our terms of use, cookies and privacy policy.