VBAコーディングパターン：ADO DB Connection Stringの生成

この記事では、Excel VBAでADO DB Connection Stringを作成および編集する機能の実装について説明します。

---

1. ADOの概念と必要性

1.1。 ADO(ActiveX Data Objects)の概念

ADOは、さまざまなデータストアにアクセスしてデータをCRUD（Create、Read、Update、Delete）することができるライブラリです。

Microsoft docsサイトでは、ADOを次のように説明しています。

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.

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

1.2. ADO (Active Data Objects) の必要性

前の記事 Excel VBAコース（1）：Excel VBA概要_1.3。 Excel VBAで何ができますか？ で紹介した内容のうち、Databaseに関連した内容があった。

この中で、Databaseに関してデータを照会、保存、変更、削除するときにADOが必要です。 Excel VBAで利用可能なDatabase関連のライブラリは、いくつか（ADO、OLE DB、ODBCなど）がありますが、ADOが最も汎用的で使いやすいです。

2. ADO DB Connection Stringの生成

2.1。参照を追加

このコードは2つのライブラリを参照して追加する必要があります。

▼ Microsoft ActiveX Data Objects 2.8 Library リファレンスの追加

「Microsoft ActiveX Data Objects 6.1 Library」がより最新バージョンであるが、星の違いが分からず、通常2.8を使用する。

▼ Microsoft OLE DB Service Component 1.0 Type Library リファレンスの追加

Connection Stringを生成または編集できるようにするMSDASC.DataLinksクラスを使用するために参照を追加します。

2.2. ADO DB Connection String生成機能の実装

前の記事 Excel VBAコース（10）：Excel VBAとして開発され使用されているツール_5.7。 Excel Data Downloaderに次のようにConnection Stringを生成するボタンがあった。

上の画面で「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セルから下に複数のConnection Stringを入力することができ、まず現在位置を選択して機能を実行する。
• 行13〜14：ADODB.Connectionオブジェクト（cn）を作成し、そのオブジェクトのConnectionStringプロパティをoCurrentRange値として割り当てます。
• 行15：MSDASC.DataLinksオブジェクトのPromptEdit関数にADODB.Connectionオブジェクト（cn）をパラメータとして渡して実行します。この関数を実行すると、次のように「データ接続プロパティ」ウィンドウが表示されます。 （プロバイダのリストは、インストールされているリストが表示されているため、環境ごとに異なる場合があります。下の「接続」タブは、「Microsoft OLE DB Provider for ODBC Drivers」を選択したときに表示される画面です。）

ちなみに、プロバイダを「Microsoft OLE DB Provider for Oracle」を選択したときは、「接続」タブが次のように変わります。

• 行16：PromptEdit関数の戻り値がTrueの場合、つまり「データ接続プロパティ」ウィンドウの「OK」ボタンをクリックしてConnectionString（連結文字列）を作成/変更完了した場合、その値をoCurrentRangeに記録します。

この方法を適用すると、次のような利点があります。

• エラーの可能性を減らす
  • DB ConnectionStringを直接入力して作成する場合は、タイプミスがあるか、インストールされていないプロバイダを指定してエラーが発生する可能性があります。
  • この方法を適用すると、DB ConnectionStringをエラーなく作成できます。
• 再利用性の向上
  • ソースコード上でDB ConnectionStringを文字列で管理する場合（ハードコーディングされている場合）は、他の環境でその機能を実行しようとしたときに毎回ソースコードを変更する必要があります。これにより、どのソースコードをどのように編集するかを知っているユーザーだけが再利用できます。
  • DB ConnectionStringをソースコードではなくUI(Excelシートなど)に見せて編集可能に機能を提供すれば誰でも再利用でき、再利用性が向上する。
• 利便性の向上（ODBC DSN生成は不要）
  • ODBCを使用する場合は、あらかじめ設定された接続情報とその名称（DSN、Data Source Name）が必要です。
  • この方法を適用すると、ODBC DSNを生成する必要がなく、簡単です。

欠点は、ConnectionStringを作成するときにパスワードを含めると、UIにそのパスワードが公開されることです。パスワードを公開してはならない場合は注意する必要がある。