VBA Coding Pattern: Create ADO DB Connection String

This article examines the implementation of functions for creating and editing ADO DB Connection Strings in Excel VBA.

---

1. ADO concept and necessity

1.1. ActiveX Data Objects (ADO) concepts

ADO is a library that can access various data stores and CRUD (Create, Read, Update, Delete) data.

The Microsoft docs site describes ADO as follows.

ActiveX Data Objects (ADO) is a high-level, easy-to-use interface to OLE DB. OLE DB is a low-level, high-performance interface to a variety of data stores. Both ADO and OLE DB can work with relational (tabular) and nonrelational (hierarchical or stream) data.

ADO provides a layer of abstraction between your client or middle-tier application and the low-level OLE DB interfaces. ADO uses a small set of Automation objects to provide a simple and efficient interface to OLE DB. This interface makes ADO a good choice for developers in higher level languages, such as Visual Basic and VBScript, who want to access data without having to learn the intricacies of COM and OLE DB.

source: https://docs.microsoft.com/en-us/sql/ado/guide/ado-introduction?view=sql-server-ver15 

1.2. Need for Active Data Objects (ADO)

previous post Excel VBA Lecture (1): Excel VBA Overview_1.3. What can you do with Excel VBA? Among the contents introduced in , there were contents related to the database.

Among them, ADO is required when searching, saving, changing, or deleting data related to the database. There are several database-related libraries (ADO, OLE DB, ODBC, etc.) that can be used in Excel VBA, but ADO is the most general-purpose and convenient to use.

2. Create ADO DB Connection String

2.1. Add reference

This code needs to add references to two libraries.

▼ Add Microsoft ActiveX Data Objects 2.8 Library Reference

“Microsoft ActiveX Data Objects 6.1 Library” is a more recent version, but 2.8 is usually used because there is no significant difference.

▼ Add Microsoft OLE DB Service Component 1.0 Type Library Reference

Add a reference to use the MSDASC.DataLinks class, which allows you to create or edit Connection Strings.

2.2. Implementation of ADO DB Connection String creation function

previous post Excel VBA Lecture (10): Tools being developed and used with Excel VBA_5.7. Excel Data DownloaderThere was a button to create a connection string as follows.

The click event processing code of the “Connection String” button in the screen above is as follows.

Private Sub btnBuildConnectionString_Click()
On Error GoTo 0

    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range
    Dim eOrgXlEnableCancelKey As XlEnableCancelKey

    Set oCurrentRange = ActiveCell

    Set MSDASCObj = New MSDASC.DataLinks

    eOrgXlEnableCancelKey = Application.EnableCancelKey
    Application.EnableCancelKey = xlDisabled
    Set cn = New ADODB.Connection
    cn.ConnectionString = oCurrentRange
    If MSDASCObj.PromptEdit(cn) = True Then
        oCurrentRange = cn.ConnectionString
    End If

    Set cn = Nothing
    Set MSDASCObj = Nothing

    Application.EnableCancelKey = eOrgXlEnableCancelKey
End Sub

This code implements the function of creating or editing Connection String by using MSDASC.DataLinks and ADODB.Connection classes.

• Line 7: Assign the current location cell (ActiveCell) in the sheet to oCurrentRange. Since you can input multiple connection strings downward from cell C16, first select the current location and then execute the function.
• Lines 13-14: Create an ADODB.Connection object (cn) and assign its ConnectionString property to the oCurrentRange value.
• Line 15: Execute by passing the ADODB.Connection object (cn) as a parameter to the PromptEdit function of the MSDASC.DataLinks object. When this function is executed, the “Data Connection Properties” window appears as follows. (The list of providers can be different depending on the user environment because the list of installed providers is shown. The “Connection” tab below is the screen that is displayed when “Microsoft OLE DB Provider for ODBC Drivers” is selected.)

For reference, when “Microsoft OLE DB Provider for Oracle” is selected as the provider, the “Connection” tab changes as follows.

• Line 16: If the return value of the PromptEdit function is True, that is, if ConnectionString (connection string) is created/changed by clicking the “OK” button in the “Data Connection Properties” window, the value is recorded in oCurrentRange.

Applying this method has the following advantages:

• Reduced chance of error
  • If you create a DB ConnectionString by typing, you may make a typo or specify a provider that is not installed and cause an error.
  • DB ConnectionString can be created without error by applying this method.
• Improved reusability
  • If DB ConnectionString is managed as a string in the source code (hard coded), the source code must be changed every time you want to run the function in a different environment. In this way, only users who know how to edit a certain source code can reuse it.
  • If DB ConnectionString is visible in the UI (Excel sheet, etc.) rather than the source code, and the function is provided so that it can be edited, anyone can reuse it, improving reusability.
• Convenience improvements (no need to create ODBC DSN)
  • When using ODBC, pre-set connection information and its name (DSN, Data Source Name) are required.
  • Applying this method is convenient because there is no need to create an ODBC DSN.

The downside is that if you include a password when creating the ConnectionString, the password can be exposed in the UI. You need to be careful if you do not want to disclose your password.