RDBMS-Zugriff per VBA: Verbindungen

2016, 5/2016, SQL Server und Co.

Lies diesen Artikel und viele weitere mit einem kostenlosen, einwöchigen Testzugang.

Wenn Sie von Access aus auf die Daten einer SQL Server-Datenbank (und neuerdings auch auf LocalDB-Datenbanken) zugreifen wollen, müssen Sie mit Bordmitteln arbeiten, die häufig nicht zufriedenstellend sind. Wir haben einen Satz von Tools entwickelt, mit denen Sie eine Reihe von Aufgaben sehr schnell erledigen können: Verbindungen definieren, Tabellen verknüpfen und SQL-Abfragen direkt an den Server schicken. Dieser Beitrag zeigt, wie Sie mithilfe eines Teils dieser Tools per VBA auf SQL Server und Co. zugreifen.

Mal eben eine Auswahlabfrage oder Pass-Through-Abfrage per VBA an den SQL Server absetzen – das wäre doch eine praktische Sache.

Sie werden an verschiedenen Stellen per VBA auf die Tabellen der SQL Server-Datenbank zugreifen müssen – sei es, um eine ODBC-Verknüpfung herzustellen oder zu aktualisieren, das Ergebnis einer gespeicherten Abfrage abzurufen oder eine solche auszuführen oder auch um ein Recordset auf Basis einer gespeicherten Prozedur einem Formular oder einem Steuerelement zuzuweisen. Alles, was Sie wissen müssen, um dies zu erledigen, erfahren Sie in diesem Beitrag.

Beispieldatenbank

Als Beispieldatenbank verwenden wir die Datenbank Suedsturm.mdf, die Sie wie im Beitrag Access und LocalDB (www.access-im-unternehmen.de/1057) beschrieben nutzen können. Alternativ können Sie diese natürlich auch per SQL Server nutzen. Die Tools, mit denen Sie die in diesem Beitrag verwendeten Verbindungszeichenfolgen zusammenstellen können, stellen wir im Beitrag SQL Server-Tools (www.access-im-unternehmen.de/1061) in der nächsten Ausgabe vor.

Verbindungszeichenfolgen

Als Erstes benötigen Sie Zugriff auf die SQL Server-Datenbank. Voraussetzung dafür ist eine geeignete Verbindungszeichenfolge. Bei den Verbindungszeichenfolgen gibt es verschiedene Ansätze.

Sie sind relativ flexibel, wenn Sie die notwendigen Informationen in einer lokalen Tabelle im Access-Frontend speichern und von Access aus per VBA darauf zugreifen, um Verbindungszeichen-fol-gen daraus zu erstellen. Alternativ können Sie Verbindungszeichenfolgen in einer DSN-Datei oder als System-DSN in der Registry speichern – wir gehen an dieser Stelle jedoch auf die Variante der tabellenbasierten Verbindungszeichenfolge ein. Die notwendigen Daten speichern wir in den Beispieldatenbanken in einer Tabelle namens tblVerbindungszeichenfolgen (s. Bild 1). Diese Tabelle haben Sie bereits im Beitrag RDBMS-Tools: Verbindungen verwalten (www.access-im-unternehmen.de/976) kennengelernt.

Entwurf der Tabelle zum Speichern der Verbindungszeichenfolgen

Bild 1: Entwurf der Tabelle zum Speichern der Verbindungszeichenfolgen

Außerdem benötigen wir eine Tabelle namens tblTreiber, welche die Daten der gängigen Treiber enthält (s. Bild 2). Die Tabelle tblVerbindungszeichenfolgen ist über das Fremdschlüsselfeld TreiberID mit der Tabelle tblTreiber verknüpft.

Entwurf der Tabelle zum Speichern der Treiber

Bild 2: Entwurf der Tabelle zum Speichern der Treiber

In der Beispielanwendung verwenden wir die Prozedur aus Listing 1, um eine Verbindungszeichen-folge aus den Daten der Tabelle tblVerbindungszeichenfolgen zu ermitteln.

Public Function VerbindungszeichenfolgeNachID(lngVerbindungszeichenfolgeID As Long, _
         Optional bolZugangsdatenAusVariablen As Boolean) As String
     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Dim strTemp As String, strTreiber As String, strServer As String, strDatenbank As String
     Set db = CurrentDb
     Set rst = db.OpenRecordset("SELECT * FROM tblVerbindungszeichenfolgen WHERE VerbindungszeichenfolgeID = " _
         & lngVerbindungszeichenfolgeID)
     strTreiber = DLookup("Treiber", "tblTreiber", "TreiberID = " & rst!TreiberID)
     strServer = rst!Server
     strDatenbank = rst!Datenbank
     strTemp = "ODBC;DRIVER={" & strTreiber & "};" & "SERVER=" & strServer & ";" _
         & "DATABASE=" & strDatenbank & ";"
     If rst!TrustedConnection = True Then
         strTemp = strTemp & "Trusted_Connection=Yes"
     Else
         If Len(Nz(rst!Benutzername, "")) > 0 And bolZugangsdatenAusVariablen = False Then
             strBenutzername = rst!Benutzername
         End If
         If Len(Nz(rst!Kennwort, "")) > 0 And bolZugangsdatenAusVariablen = False Then
             strKennwort = rst!Kennwort
         End If
         strTemp = strTemp & "UID=" & strBenutzername & ";"
         strTemp = strTemp & "PWD=" & strKennwort
     End If
     VerbindungszeichenfolgeNachID = strTemp
End Function

Listing 1: Ermitteln einer Verbindungszeichenfolge aus der Tabelle tblVerbindungszeichenfolgen

Die Funktion arbeitet direkt mit der Tabelle tblVerbindungszeichenfolgen, in der die Verbindungsprarameter gespeichert sind. Dabei liest sie den Datensatz der Tabelle tblVerbindungszeichenfolgen ein, dessen Primär-schlüs-selwert dem mit dem Parameter lngVerbindungszeichenfolgeID übergebenen Wert entspricht. Der zweite Parameter der Funktion heißt bolZugangsdatenAusVariablen und legt fest, ob auf jeden Fall die Zugangsdaten aus den beiden Variablen strBenutzername und strKennwort verwendet werden sollen. Dies benötigen wir für einen Aufruf aus der später erläuterten Funktion VerbindungTesten, die gegebenenfalls die Zugangsdaten vorher per Dialog abfragt und diese aus Sicherheitsgründen nur in den beiden Variablen strBenutzername und strKennwort speichert, aber nicht in der Tabelle.

Es gibt zwei Variablen namens strBenutzername und strKennwort, die speziell für den Einsatz mit der SQL Server-Authentifizierung vorgesehen sind (oder auch für die Anmeldung an einem anderen SQL-Server-System wie MySQL mit der Anforderung von Benutzerdaten). In diesem Fall muss die Access-Anwendung die Benutzerdaten bereitstellen, um eine Verbindung herzustellen. Diese sollen aber nicht in der Datenbank gespeichert werden, da die Daten sonst für jedermann zugänglich wären. Die Variablen sollen zuvor per Formular vom Benutzer einmalig pro Sitzung abgefragt und in entsprechenden Variablen gespeichert werden, die wie folgt deklariert werden:

Public strBenutzername As String
Public strKennwort As String

Die Funktion VerbindungszeichenfolgeNachID prüft dann, ob die Tabelle eigene Werte für Benutzername und Kennwort enthält, und trägt diese gegebenenfalls in die Variablen strBenutzername und strKennwort ein. Danach setzt die Funktion die einzelnen Elemente dann zu einer Verbindungszeichen-folge zu-sammen. Abhängig davon, ob die Windows-Authentifizierung oder SQL Server-Authentifizie-rung gewählt wurde, erhält die Verbindungszeichenfolge das Name-Wert-Paar Trusted_Con-nec-tion=Yes, anderenfalls die Benutzerdaten in der Form UID=;PWD=. Woher die Werte der beiden Variablen strBenutzername beziehungsweise strKennwort kommen, haben wir ja weiter oben bereits erläutert.

Aus dem obersten Eintrag der Tabelle tblVerbindungszeichenfolgen aus Bild 3 würde die Funktion mit dem Wert 9 als Parameter etwa folgendes Ergebnis liefern:

Tabelle mit den Daten einer Verbindungszeichenfolge

Bild 3: Tabelle mit den Daten einer Verbindungszeichenfolge

  VerbindungszeichenfolgeNachID(9)
ODBC;DRIVER={ODBC Driver 11 for SQL Server};SERVER=(localdb)\MSSQLLocalDB;DATABASE=Suedsturm;Trusted_Connection=Yes

Standardverbindungszeichenfolge

In der Tabelle tblVerbindungszeichenfolgen finden Sie auch ein Boolean-Feld namens Aktiv. Dieses legt fest, welche Verbindungszeichenfolge für die aktuelle Datenbank standardmäßig verwendet werden soll.

Der generelle Vorteil des Speicherns der Daten für die Verbindungszeichenfolge in einer Tabelle ist, dass Sie diese bei Bedarf einfach ändern können. Der zweite Vorteil ist: Sie können auch mehrere Verbindungszeichenfolgen angeben und zwischen diesen Zeichenfolgen wechseln. Genau dies ermöglicht das Feld Aktiv.

Während Sie nun mit der Funktion VerbindungszeichenfolgeNachID immer die ID der aktuell benötigten Verbindungszeichenfolge angeben müssen, ermöglicht die Funktion Standardverbindungszeichenfolge, direkt die als Aktiv markierte Verbindungszeichenfolge zu ermitteln (s. Listing 2).

Public Function Standardverbindungszeichenfolge() As String
     Dim lngAktivID As Long
     If Not lngAktivID = 0 Then
         Standardverbindungszeichenfolge = VerbindungszeichenfolgeNachID(lngAktivID)
     Else
         MsgBox "Achtung: Es ist keine Verbindungszeichenfolge als aktiv gekennzeichnet."
     End If
End Function

Listing 2: Ermitteln der Standardverbindungszeichenfolge aus der Tabelle tblVerbindungszeichenfolgen

Die Funktion ermittelt per DLookup-Funktion den ersten Eintrag der Tabelle tblVerbindungszei-chen-folgen, dessen Feld Aktiv den Wert True aufweist. Dieser wird dann der Funktion Ver-bin-dungs-zeichenfolgeNachID übergeben, um die entsprechende Verbindungszeichenfolge zu ermitteln. Sollte keine Verbindungszeichenfolge als aktiv markiert sein, erscheint eine entsprechende Mel-dung.

Wenn hingegen mehr als eine Verbindungszeichenfolge den Wert True im Feld Aktiv enthält, liefert die Funktion die Verbindungszeichenfolge für den ersten markierten Eintrag zurück.

ID der aktiven Verbindungszeichenfolge ermitteln

Andere Routinen erwarten gegebenenfalls die ID der zu verwendenden Verbindungszeichenfolge. Wenn Sie nicht explizit einen Wert des Feldes VerbindungszeichenfolgeID der Tabelle tblVerbindungszeichenfolgen übergeben möchten, können Sie mit der Funktion aus Listing 3 den Wert für den aktiven Datensatz ermitteln.

Public Function AktiveVerbindungszeichenfolgeID() As Long
     Dim lngAktivID As Long
     lngAktivID = Nz(DLookup("VerbindungszeichenfolgeID", "tblVerbindungszeichenfolgen", "Aktiv = True"), 0)
     If lngAktivID = 0 Then
         MsgBox "Achtung: Es ist keine Verbindungszeichenfolge als aktiv gekennzeichnet."
     End If
     AktiveVerbindungszeichenfolgeID = lngAktivID
End Function

Listing 3: Ermitteln der ID der Standardverbindungszeichenfolge aus der Tabelle tblVerbindungszeichenfolgen

Auch diese Funktion liefert eine Meldung, wenn keine aktive Verbindungszeichenfolge gefunden werden konnte.

Verbindung und Zugriffsdaten prüfen

Aufbauend auf den vorherigen Funktionen wollen wir nun eine weitere Funktion einführen, die prüft, ob eine Verbindungszeichenfolge funktioniert. Diese heißt VerbindungTesten (s. Listing 4). Die Funktion erledigt gleich drei Aufgaben:

Public Function VerbindungTesten(lngVerbindungszeichenfolgeID As Long, _
         Optional strVerbindungszeichenfolge As String) As Boolean
     Dim bolTrustedConnection As Boolean
     Dim bolVerbindungHergestellt As Boolean
     bolTrustedConnection = DLookup("TrustedConnection", "tblVerbindungszeichenfolgen", "VerbindungszeichenfolgeID = " _
         & lngVerbindungszeichenfolgeID)
     strBenutzername = Nz(DLookup("Benutzername", "tblVerbindungszeichenfolgen", "VerbindungszeichenfolgeID = " _
         & lngVerbindungszeichenfolgeID), strBenutzername)
     strKennwort = Nz(DLookup("Kennwort", "tblVerbindungszeichenfolgen", "VerbindungszeichenfolgeID = " _
         & lngVerbindungszeichenfolgeID), strKennwort)
     If (Len(strBenutzername) * Len(strKennwort) = 0) And Not bolTrustedConnection Then
         If LogindatenErmitteln(lngVerbindungszeichenfolgeID) = False Then
             Exit Function
         End If
     End If
     strVerbindungszeichenfolge = VerbindungszeichenfolgeNachID(lngVerbindungszeichenfolgeID)
     On Error Resume Next
     bolVerbindungHergestellt = VerbindungHerstellen(strVerbindungszeichenfolge)
     Do While Not bolVerbindungHergestellt
         MsgBox "Die Verbindung konnte nicht hergestellt werden."
         On Error GoTo 0
         If Not bolTrustedConnection Then
             If LogindatenErmitteln(lngVerbindungszeichenfolgeID) = True Then
                 strVerbindungszeichenfolge = VerbindungszeichenfolgeNachID(lngVerbindungszeichenfolgeID)
                 bolVerbindungHergestellt = VerbindungHerstellen(strVerbindungszeichenfolge)
             Else
                 Exit Function
             End If
         Else
             Exit Function
         End If
     Loop
     VerbindungTesten = True
End Function

Listing 4: Testen einer per VerbindungszeichenfolgeID angegebenen Verbindungszeichenfolge

  • Sie testet die Verbindung und liefert einen Boolean-Wert zurück, der über das Ergebnis Auskunft gibt.
  • Wenn die Verbindung SQL Server-Authentifizierung nutzt, soll zumindest das Kennwort nicht in der Verbindungszeichenfolge und auch nicht in der Tabelle tblVerbindungszeichenfol-gen enthalten sein. Die Funktion prüft die Verbindung für die verfügbaren Werte von Be-nut-zername und Kennwort und zeigt ein Formular zur erneuten Eingabe oder Korrektur der Werte an, wenn damit keine Verbindung hergestellt werden konnte.
  • Sie liefert, sofern dafür beim Aufruf ein entsprechender Parameter festgelegt wurde, gleich die Verbindungszeichenfolge für diese Verbindung zurück.

Die Funktion erwartet folgende Parameter:

Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...

Testzugang

eine Woche kostenlosen Zugriff auf diesen und mehr als 1.000 weitere Artikel

diesen und alle anderen Artikel mit dem Jahresabo

Schreibe einen Kommentar