VBA-Codierungsmuster: ADO-DB-Verbindungszeichenfolge erstellen

Dieser Artikel befasst sich mit der Implementierung von Funktionen zum Erstellen und Bearbeiten von ADO-DB-Verbindungszeichenfolgen in Excel VBA.

---

1. ADO-Konzept und Notwendigkeit

1.1. ActiveX Data Objects (ADO)-Konzepte

ADO ist eine Bibliothek, die auf verschiedene Datenspeicher und CRUD-Daten (Create, Read, Update, Delete) zugreifen kann.

Die Microsoft-Docs-Site beschreibt ADO wie folgt.

ActiveX Data Objects (ADO) ist eine benutzerfreundliche High-Level-Schnittstelle zu OLE DB. OLE DB ist eine Low-Level-Hochleistungsschnittstelle zu einer Vielzahl von Datenspeichern. Sowohl ADO als auch OLE DB können mit relationalen (tabellarischen) und nicht relationalen (hierarchischen oder Stream-) Daten arbeiten.

ADO bietet eine Abstraktionsebene zwischen Ihrer Client- oder Mittelschichtanwendung und den OLE DB-Schnittstellen auf niedriger Ebene. ADO verwendet einen kleinen Satz von Automatisierungsobjekten, um eine einfache und effiziente Schnittstelle zu OLE DB bereitzustellen. Diese Schnittstelle macht ADO zu einer guten Wahl für Entwickler in höheren Programmiersprachen wie Visual Basic und VBScript, die auf Daten zugreifen möchten, ohne sich mit den Feinheiten von COM und OLE DB vertraut machen zu müssen.

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

1.2. Die Notwendigkeit von Active Data Objects (ADO)

vorherigen Post Excel VBA Vorlesung (1): Excel VBA Übersicht_1.3. Was können Sie mit Excel VBA tun? Unter den in eingeführten Inhalten gab es Inhalte, die sich auf die Datenbank bezogen.

Unter anderem muss ADO Daten in Bezug auf die Datenbank abfragen, speichern, ändern oder löschen. Es gibt mehrere datenbankbezogene Bibliotheken, die in Excel VBA (ADO, OLE DB, ODBC usw.) verwendet werden können, aber ADO ist am vielseitigsten und bequemsten zu verwenden.

2. Erstellen Sie eine ADO DB-Verbindungszeichenfolge

2.1. Füge Referenz hinzu

Dieser Code muss einen Verweis auf zwei Bibliotheken hinzufügen.

▼ Microsoft ActiveX Data Objects 2.8 Bibliotheksreferenz hinzugefügt

„Microsoft ActiveX Data Objects 6.1 Library“ ist eine neuere Version, aber normalerweise wird 2.8 verwendet, da es keinen signifikanten Unterschied gibt.

▼ Microsoft OLE DB Service Component 1.0 Type Library Reference hinzugefügt

Fügen Sie einen Verweis hinzu, um die MSDASC.DataLinks-Klasse zu verwenden, mit der Sie eine Verbindungszeichenfolge erstellen oder bearbeiten können.

2.2. Implementierung der Funktion zur Erstellung von ADO-DB-Verbindungszeichenfolgen

vorherigen Post Excel VBA-Kurs (10): Tools, die mit Excel VBA_5.7 entwickelt und verwendet werden. Excel-Daten-DownloaderEs gab eine Schaltfläche zum Erstellen einer Verbindungszeichenfolge wie folgt.

Der Click-Event-Verarbeitungscode der Schaltfläche „Connection String“ im obigen Bildschirm lautet wie folgt.

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

Dieser Code ist der Code, der die Funktion zum Erstellen oder Bearbeiten einer Verbindungszeichenfolge (Verbindungszeichenfolge) mithilfe der Klassen MSDASC.DataLinks und ADODB.Connection implementiert.

• Zeile 7: Weisen Sie oCurrentRange die aktuelle Positionszelle (ActiveCell) im Blatt zu. Mehrere Verbindungszeichenfolgen können von der C16-Zelle abwärts eingegeben werden, sodass die Funktion ausgeführt wird, nachdem zuerst die aktuelle Position ausgewählt wurde.
• Zeilen 13-14: Erstellen Sie ein ADODB.Connection-Objekt (cn) und weisen Sie die ConnectionString-Eigenschaft des Objekts dem oCurrentRange-Wert zu.
• Zeile 15: Ausführen durch Übergeben des ADODB.Connection-Objekts (cn) als Parameter an die PromptEdit-Funktion des MSDASC.DataLinks-Objekts. Wenn diese Funktion ausgeführt wird, erscheint das Fenster „Data Connection Properties“ wie folgt. (Die Liste der Anbieter kann je nach Benutzerumgebung unterschiedlich sein, da die Liste der installierten Anbieter angezeigt wird. Die Registerkarte „Verbindung“ unten ist der Bildschirm, der angezeigt wird, wenn „Microsoft OLE DB-Anbieter für ODBC-Treiber“ ausgewählt wird.)

Als Referenz: Wenn „Microsoft OLE DB Provider for Oracle“ als Anbieter ausgewählt ist, ändert sich die Registerkarte „Verbindung“ wie folgt.

• Zeile 16: Wenn der Rückgabewert der PromptEdit-Funktion True ist, d. h. wenn ConnectionString (Verbindungszeichenfolge) durch Klicken auf die Schaltfläche „OK“ im Fenster „Data Connection Properties“ erstellt/geändert wird, wird der Wert in oCurrentRange aufgezeichnet.

Die Anwendung dieses Verfahrens hat die folgenden Vorteile.

• Reduzieren Sie die Wahrscheinlichkeit von Fehlern
  • Wenn Sie einen DB ConnectionString erstellen, indem Sie ihn selbst eingeben, liegt möglicherweise ein Tippfehler vor, oder es kann ein Fehler auftreten, wenn Sie einen nicht installierten Anbieter angeben.
  • Wenn diese Methode angewendet wird, kann DB ConnectionString fehlerfrei erstellt werden.
• Verbesserte Wiederverwendbarkeit
  • Wenn DB ConnectionString als String im Quellcode verwaltet wird (hard-coded), muss der Quellcode jedes Mal geändert werden, wenn Sie versuchen, die Funktion in einer anderen Umgebung auszuführen. Auf diese Weise können nur Benutzer, die wissen, wie man welchen Quellcode bearbeitet, ihn wiederverwenden.
  • Wenn Sie den DB ConnectionString in der Benutzeroberfläche (Excel-Tabelle usw.) statt im Quellcode sichtbar platzieren und die bearbeitbare Funktion bereitstellen, kann ihn jeder wiederverwenden, wodurch die Wiederverwendbarkeit verbessert wird.
• Verbesserter Komfort (keine ODBC-DSN-Erstellung erforderlich)
  • Bei Verwendung von ODBC sind voreingestellte Verbindungsinformationen und deren Name (DSN, Data Source Name) erforderlich.
  • Wenn diese Methode angewendet wird, muss kein ODBC-DSN erstellt werden, was praktisch ist.

Der Nachteil ist, dass, wenn Sie das Kennwort beim Erstellen des ConnectionString angeben, das Kennwort möglicherweise in der Benutzeroberfläche angezeigt wird. Sie müssen vorsichtig sein, wenn Sie Ihr Passwort nicht preisgeben möchten.