VBA 编码模式：创建 ADO DB 连接字符串

本文检查在 Excel VBA 中创建和编辑 ADO 数据库连接字符串的函数的实现。

---

一、ADO概念及必要性

1.1. ActiveX 数据对象 (ADO) 概念

ADO 是一个可以访问各种数据存储和 CRUD（创建、读取、更新、删除）数据的库。

Microsoft 文档站点对 ADO 的描述如下。

ActiveX 数据对象 (ADO) 是一个高级的、易于使用的 OLE DB 接口。 OLE DB 是各种数据存储的低级、高性能接口。 ADO 和 OLE DB 都可以处理关系（表格）和非关系（分层或流）数据。

ADO 在您的客户端或中间层应用程序与低级 OLE DB 接口之间提供了一个抽象层。 ADO 使用一小组自动化对象为 OLE DB 提供简单而有效的接口。此接口使 ADO 成为使用高级语言（例如 Visual Basic 和 VBScript）的开发人员的不错选择，他们希望访问数据而无需学习 COM 和 OLE DB 的复杂性。

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

1.2.需要活动数据对象 (ADO)

上一篇 Excel VBA讲义（一）：Excel VBA概览_1.3。您可以使用 Excel VBA 做什么？ 在介绍的内容中，有与数据库相关的内容。

其中，查找、保存、更改或删除与数据库相关的数据时，需要用到ADO。有几个数据库相关的库（ADO、OLE DB、ODBC 等）可以在 Excel VBA 中使用，但 ADO 是最通用且使用方便的。

2.创建ADO DB连接字符串

2.1.添加参考

这段代码需要添加对两个库的引用。

▼ 添加 Microsoft ActiveX 数据对象 2.8 库参考

“Microsoft ActiveX Data Objects 6.1 Library”是较新的版本，但通常使用 2.8，因为没有显着差异。

▼ 添加 Microsoft OLE DB 服务组件 1.0 类型库参考

添加一个引用以使用 MSDASC.DataLinks 类，它允许您创建或编辑连接字符串。

2.2. ADO DB Connection String创建函数的实现

上一篇 Excel VBA 讲座（10）：Excel VBA_5.7 开发和使用的工具。 Excel 数据下载器有一个按钮可以创建连接字符串，如下所示。

上图“Connection String”按钮的点击事件处理代码如下。

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

这段代码使用MSDASC.DataLinks 和ADODB.Connection 类实现了创建或编辑Connection String 的功能。

• 第 7 行：将工作表中的当前位置单元格（ActiveCell）赋值给 oCurrentRange。由于可以从C16单元格向下输入多个连接字符串，所以先选中当前位置，再执行函数。
• 第 13-14 行：创建一个 ADODB.Connection 对象 (cn) 并将其 ConnectionString 属性分配给 oCurrentRange 值。
• 第 15 行：通过将 ADODB.Connection 对象 (cn) 作为参数传递给 MSDASC.DataLinks 对象的 PromptEdit 函数来执行。执行此功能时，“数据连接属性”窗口如下所示。 （根据用户环境的不同，提供程序列表可能会有所不同，因为会显示已安装的提供程序列表。下面的“连接”选项卡是选择“用于 ODBC 驱动程序的 Microsoft OLE DB 提供程序”时显示的屏幕。）

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

• 第 16 行：如果 PromptEdit 函数的返回值为 True，即在“数据连接属性”窗口中单击“确定”按钮创建/更改了 ConnectionString（连接字符串），则将该值记录在 oCurrentRange 中。

应用这种方法有以下优点：

• 减少出错的机会
  • 如果您通过键入创建 DB ConnectionString，您可能会输入错误或指定未安装的提供程序并导致错误。
  • 通过应用该方法，可以无误地创建 DB ConnectionString。
• 改进的可重用性
  • 如果 DB ConnectionString 在源代码中作为字符串进行管理（硬编码），则每次要在不同环境中运行该函数时都必须更改源代码。这样，只有知道如何编辑某个源代码的用户才能重用它。
  • 如果 DB ConnectionString 在 UI（Excel 工作表等）而不是源代码中是可见的，并且提供了可以编辑的功能，那么任何人都可以重用它，从而提高重用性。
• 便利性改进（无需创建 ODBC DSN）
  • 使用 ODBC 时，需要预先设置连接信息及其名称（DSN，数据源名称）。
  • 应用此方法很方便，因为无需创建 ODBC DSN。

不利之处在于，如果您在创建 ConnectionString 时包含密码，则密码可能会暴露在 UI 中。如果您不想泄露您的密码，则需要小心。