SQL Server-Tabellenverknüpfungsassistent

Bild 1: Herstellen einer Tabellenverknüpfung mit Bordmitteln

Die Bordmittel von Access zum Herstellen oder Aktualisieren von Tabellenverknüpfungen zum SQL Server sind teilweise etwas umständlich zu bedienen und erfordern zwangsläufig den Einsatz von Data Source Names (DSN). Diese möchte man unter Umständen aber gar nicht nutzen, sondern einfach alle Informationen in die Verbindungszeichenfolge für die Tabelle schreiben. Natürlich kann man sich ein Set von Tabellen und Formularen zusammenstellen, mit denen man die Verbindungszeichenfolgen und Tabellenverknüpfungen verwaltet. Und die man bei Bedarf zu der Datenbank hinzufügt, deren Tabellenverknüpfungen man pflegen möchte. Aber manchmal möchte man einfach nur schnell mal etwas ausprobieren und dazu ist der Aufwand, die Datenbank um diese Tools zu erweitern, zu aufwendig. Wie wäre es also, wenn wir diese Tools einfach in ein Access-Add-In auslagern, mit dem wir unsere Verbindungszeichenfolgen und Verknüpfungen verwalten können? In diesem Beitrag schauen wir uns an, wie dies aussieht.

Anlass zur Programmierung dieses Access-Add-Ins war der Umstand, dass ich immer wieder mal Testdatenbanken vom Kunden bekomme oder selbst Beispiele programmiere, deren Daten im SQL Server liegen und für die ich schnell eine Tabellenverknüpfung erstellen möchte. Das ist mir allerdings über den Assistenten zum Importieren oder Verknüpfen von Tabellen aus ODBC-Datenbanken etwas zu aufwendig (siehe Bild 1).

Bild 1: Herstellen einer Tabellenverknüpfung mit Bordmitteln

Ich möchte einmal die Verbindungszeichenfolge festlegen und dann so schnell wie möglich Tabellenverknüpfungen erstellen oder aktualisieren.

Tabellen per Add-In verknüpfen

Das gelingt mit dem Access-Add-In, das wir in diesem Beitrag vorstellen, ganz einfach.

Dazu brauchen wir nur über den Ribbon-Befehl Datenbanktools|Add-Ins|Add-Ins|amvTableLinker das Hauptformular unseres Add-Ins zu starten, das direkt nach dem Aufruf wie in Bild 2 aussieht. Hier finden wir nur wenige Elemente vor:

Bild 2: Hauptformular unseres Access-Add-Ins

• Das Kombinationsfeld oben zeigt alle Datenbankverbindungen an, die wir bereits im Add-In hinterlegt haben und dient zur Auswahl der zu verwendenden Datenbankverbindung.
• Das Listenfeld zeigt alle Tabellen und Views an, die wir in der referenzierten Datenbank vorfinden. Das Listenfeld erlaubt die Auswahl mehrerer Tabellen gleichzeitig. Dies geschieht bei gedrückter Alt-Taste, um einzelne Einträge aus- oder abzuwählen und bei gedrückter Umschalttaste lassen sich mehrere zusammenhängende Tabellen markieren.
• Die Schaltfläche Tabellen verknüpfen verknüpft schließlich alle aktuell markierten Tabellen.

Eine weitere Funktion ist, dass das Listenfeld alle Tabellenverknüpfungen, die es in der Access-Datenbank vorfindet, bereits markiert.

Um alle aktuell vorhandenen Tabellenverknüpfungen zu aktualisieren, braucht man also nur die richtige Verbindungszeichenfolge auszuwählen und die Schaltfläche Tabellen verknüpfen zu betätigen.

Verbindungszeichenfolgen verwalten

Schließlich finden wir oben rechts noch eine Schaltfläche mit drei Punkten, mit der wir den Dialog zum Verwalten der Verbindungszeichenfolgen öffnen können.

Dieser sieht wie in Bild 3 aus und erlaubt das Eintragen der wesentlichen Merkmale einer SQL Server-Verbindung. Hier finden wir folgende Steuerelemente:

Bild 3: Verwalten von Verbindungszeichenfolgen

• Bezeichnung: Selbst vergebene Bezeichnung für die Verbindungszeichenfolge
• SQL Server: Adresse des SQL Servers, zum Beispiel Servername oder IP, gegebenenfalls unter Angabe des Instanznamens
• Port: Port des SQL Servers, wenn abweichend vom Standardport 1433
• Datenbank: Name der Datenbank. Dieser kann nach Festlegung des SQL Servers auch aus den vorhandenen Datenbanken ausgewählt werden.
• Authentifizierung: Hier wählt man zwischen Windows-Authentifizierung und SQL Server-Authentifizierung.
• Benutzername und Kennwort: Angabe der Zugangsdaten im Falle der SQL Server-Authentifizierung
• Treiber: Auswahl des Treibers für die Verbindungszeichenfolge. Diese können wiederum über eine Tabelle im Add-In verwaltet werden.
• Verbindungszeichenfolge: Stellt aus den gegebenen Informationen die Vorschau der Verbindungszeichenfolge zusammen.

Die Schaltflächen erlauben die folgenden Funktionen:

• OK: Schließen des Formulars
• Testen: Testaufruf der Verbindungszeichenfolge
• Als Standard: Setzt die aktuelle Verbindungszeichenfolge als Standard
• Neu: Legt eine neue, leere Verbindungszeichenfolge an
• Kopieren als…: Kopiert die aktuelle Verbindungszeichenfolge als neue Zeichenfolge.
• Löschen: Löscht die aktuelle Zeichenfolge.

Oben finden wir noch das Kombinationsfeld Schnellauswahl. Hier werden alle verfügbaren Verbindungszeichenfolgen zur Auswahl angeboten.

Das Formular ist an die Tabelle tblVerbindungszeichenfolgen gebunden (siehe Bild 4).

Bild 4: Die Tabelle tblVerbindungszeichenfolgen

Die Treiber, die über das Feld TreiberID ausgewählt werden können, stammen aus der Tabelle tblTreiber (siehe Bild 5).

Bild 5: Die Tabelle tblTreiber

Automatische Übernahme der Verbindungszeichenfolge

Wenn wir den Dialog zum Verwalten der Verbindungszeichenfolge vom Formular zum Verknüpfen der Tabellen aus öffnen, wird dort direkt die Verbindungszeichenfolge angezeigt, die wir im aufrufenden Formular ausgewählt haben. Legen wir hier eine neue Verbindungszeichenfolge an oder wählen eine andere aus, wird diese nach dem Schließen des Dialogs automatisch im aufrufenden Formular selektiert.

Datenbank merkt sich die Verbindungszeichenfolge

Außerdem wollten wir noch eine Funktion hinzufügen, die dafür sorgt, dass beim Öffnen von amvTableLinker auf irgendeine Weise gespeichert wird, welche Verbindungszeichenfolge zuletzt zum Verknüpfen von Tabellen verwendet wurde. Damit wollen wir die Voraussetzung schaffen, dass beim nächsten Öffnen des amvTableLinkers in der gleichen Datenbank automatisch die richtige Verbindungszeichenfolge ausgewählt wird.

Dies haben wir realisiert, indem wir die Bezeichnung der Verbindungszeichenfolge in einer benutzerdefinierten Eigenschaft der Datenbank speichern.

Beim Öffnen von amvTableLinker prüfen wir, ob es diese Eigenschaft gibt und falls ja, wählen wir die entsprechende Verbindungszeichenfolge aus.

Programmieren der Formulare für amvTableLinker

Nun schauen wir uns an, wie wir die Formulare des Access-Add-Ins programmiert haben.

Wir beginnen mit dem Formular frmTabellenVerknuepfen, das in der Entwurfsansicht wie in Listing 1 aussieht.

Private Sub Form_Load()
     Dim db As DAO.Database
     Dim dbc As DAO.Database
     Dim prp As DAO.Property
     Dim rst As DAO.Recordset
     Dim strVerbindung As String
     Set db = CurrentDb
     Set dbc = CodeDb
     On Error Resume Next
     Set prp = db.Properties("Verbindung")
     On Error GoTo 0
     If Not prp Is Nothing Then
         strVerbindung = prp.Value
         Set rst = dbc.OpenRecordset("SELECT * FROM tblVerbindungszeichenfolgen WHERE Bezeichnung = ''" _
             & strVerbindung & "''", dbOpenDynaset)
         If Not rst.EOF Then
             Me.cboVerbindung = rst!VerbindungszeichenfolgeID
             Call cboVerbindung_AfterUpdate
         End If
     End If
End Sub

Listing 1: Diese Prozedur wird beim Laden des Formulars frmTabellenVerknuepfen ausgelöst.

Beim Laden des Formulars wird die Prozedur aus Bild 6 ausgelöst. Diese deklariert gleich zwei Variablen des Typs DAO.Database. Die erste namens db soll das Database-Objekt der Datenbank referenzieren, die das Add-In aufruft. Die zweite heißt dbc und soll das Database-Objekt der Add-In-Datenbank aufnehmen. Außerdem deklarieren wir Variablen für ein Property– und ein Recordset-Objekt.

Bild 6: Entwurf des Formulars frmTabellenVerknuepfen

Die beiden verschiedenen Database-Objekte füllen wir mit den Funktionen CurrentDb (für die aufrufende Datenbank) und CodeDb (für die Add-In-Datenbank).

Danach versuchen wir, die Eigenschaft Verbindung der aufrufenden Datenbank zu referenzieren.

In dieser haben wir gegebenenfalls zuvor die Bezeichnung der Verbindungszeichenfolge gespeichert, mit der wir die Tabellenverknüpfungen hergestellt haben. Dies erledigen wir bei deaktivierter Fehlerbehandlung, weil es auch sein kann, dass diese Eigenschaft noch gar nicht vorliegt – und das würde wiederum einen Fehler auslösen, den wir somit umgehen.

Ob das Zuweisen an die prp-Variable erfolgreich war, prüfen wir anschließend, indem es mit Is Nothing vergleichen. In diesem Fall ist prp vorhanden und wir lesen den Wert über die Eigenschaft Value in die Variable strVerbindung ein.

Damit können wir nun das Recordset rst füllen, und zwar mit einen Recordset auf Basis der Tabelle tblVerbindungszeichenfolgen. Für dieses legen wir fest, dass das Feld Bezeichnung mit dem Wert aus strVerbindung übereinstimmen soll. Das Recordset öffnen wir dabei mit der OpenRecordset-Methode der CodeDb-Datenbank aus dbc.

Ist das Recordset anschließend nicht leer, stellen wir das Kombinationsfeld cboVerbindung auf den Primärschlüsselwert des Recordsets ein und rufen die Prozedur cboVerbindung_AfterUpdate auf, die auch nach der manuellen Auswahl einer Verbindungszeichenfolge ausgelöst wird.

Anzeigen der Tabellen einer Verbindungszeichenfolge

Die oben erwähnte Prozedur cboVerbindung_AfterUpdate soll nach dem Selektieren eines Eintrags dieses Kombinationsfeldes alle Tabellen der Datenbank anzeigen, die in dieser Verbindungszeichenfolge referenziert wird.

Die Prozedur sehen wir in Listing 2. Sie verwendet ebenfalls zwei Database-Objektvariablen. Außerdem deklariert sie eine QueryDef-Variable und eine Recordset-Variable. Daneben benötigen wir eine String-Variable für die Verbindungszeichenfolge sowie eine Variant-Variable zum Durchlaufen der Listenfeld-Einträge.

Private Sub cboVerbindung_AfterUpdate()
     Dim db As DAO.Database
     Dim dbc As DAO.Database
     Dim qdf As DAO.QueryDef
     Dim rstTables As DAO.Recordset
     Dim strVerbindungszeichenfolge As String
     Dim lngErrorNumber As Long
     Dim strErrorDescription As String
     Dim var As Variant
     Set dbc = CodeDb
     Set db = CurrentDb
     If Len(strBenutzername) = 0 Then
         strBenutzername = Nz(dbc.OpenRecordset("SELECT Benutzername FROM tblVerbindungszeichenfolgen " _
         & "WHERE VerbindungszeichenfolgeID = " & Me!cboVerbindung).Fields(0), "")
     End If
     If Len(strKennwort) = 0 Then
         strKennwort = Nz(dbc.OpenRecordset("SELECT Kennwort FROM tblVerbindungszeichenfolgen " _
            & "WHERE VerbindungszeichenfolgeID = " & Me!cboVerbindung).Fields(0), "")
     End If
     If VerbindungTesten(Me!cboVerbindung, strVerbindungszeichenfolge, lngErrorNumber, strErrorDescription) = True Then
         Set qdf = db.CreateQueryDef("")
         With qdf
             .SQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE IN (''BASE TABLE'', ''VIEW'') " _
                 & "ORDER BY TABLE_TYPE, TABLE_NAME"
             .Connect = strVerbindungszeichenfolge
             .ReturnsRecords = True
         End With
         Set Me!lstTabellen.Recordset = qdf.OpenRecordset
         Set rstTables = db.OpenRecordset("SELECT Name FROM MSysObjects WHERE NOT Connect IS NULL", dbOpenDynaset)
         Do While Not rstTables.EOF
             For var = 0 To Me.lstTabellen.ListCount - 1
                 If Me!lstTabellen.ItemData(var) = rstTables!Name Then
                     Me.lstTabellen.Selected(var) = True
                 End If
             Next var
             rstTables.MoveNext
         Loop
     Else
         MsgBox "Keine gültige Verbindung." & vbCrLf & vbCrLf & strErrorDescription
     End If
End Sub

Listing 2: Anzeigen aller Tabellen der gewählten Verbindungszeichenfolge

Nach dem Füllen der Database-Variablen ermitteln wir die eventuell festgelegten Daten für die Anmeldung an die Datenbank und speichern diese in den Variablen strBenutzername und strKennwort, die als öffentliche Variablen im Modul mdlSQLServer deklariert sind. Hier würden wir normalerweise der Einfachheit halber DLookup-Funktionen nutzen. Aber damit würden wir immer auf die Datenbank zugreifen, die das Add-In aufgerufen hat. Daher müssen wir hier die OpenRecordset-Methode für das mit der Variablen dbc referenzierte Database-Objekt der Add-In-Datenbank verwenden.

Anschließend ruft die Prozedur die Funktion VerbindungTesten auf und übergibt den Primärschlüsselwert der zu untersuchenden Verbindungszeichenfolge sowie die beiden Variablen lngErrorNumber und strErrorDescription für eventuelle Rückmeldungen über Fehler und erwartet für den Parameter strVerbindungszeichenfolge die entsprechende Zeichenfolge zurück. Diese Funktion beschreiben wir weiter unten.

War der Aufruf erfolgreich, erstellt die Prozedur mit der CreateQueryDef-Methode ein neues, temporäres QueryDef-Objekt. Das erreichen wir, indem wir für den Parameter Name eine leere Zeichenkette übergeben.

Dieser weisen wir nun über die Eigenschaft SQL eine SQL-Anweisung zu, die alle Tabellennamen aus der Systemtabelle Information_Schema.Table holt, deren Typ BASE TABLE oder VIEW lautet. Als Verbindungszeichenfolge geben wir für die Eigenschaft Connect den Wert aus strVerbindungszeichenfolge an. Schließlich stellen wir die Eigenschaft ReturnsRecords auf True ein, damit die Abfrage Datensätze zurückliefert (im Gegensatz zu einer Aktionsabfrage).

Das mit der OpenRecordset-Methode auf Basis des QueryDef-Objekts aus der Variablen qdf geöffnete Recordset weisen wir direkt der gleichnamigen Eigenschaft des Listenfeldes lstTabellen zu.

Außerdem erstellen wir ein Recordset namens rstTables, das wir mit Datensätzen der Systemtabelle MSysObjects der aufrufenden Access-Datenbank füllen. Diese beschränken wir auf alle Datensätze, deren Eigenschaft Connect nicht leer ist – was in der Regel die per ODBC eingebundenen Tabellen eindeutig identifiziert.

Dieses Recordset durchlaufen wir in einer Do While-Schleife. Innerhalb der Schleife durchlaufen wir in einer For…Each-Schleife alle Elemente des Listenfeldes lstTabellen. Dabei vergleichen wir jeweils den Eintrag des Listenfeldes mit dem Namen der aktuellen Tabelle aus dem Recordset.

Sind beide gleich, bedeutet dies, dass die Tabelle aus dem SQL Server bereits in Form einer Tabellenverknüpfung in der aufrufenden Datenbank hinterlegt ist. In diesem Fall soll dieser Eintrag im Listenfeld selektiert werden, was wir durch Einstellen der Eigenschaft Selected für das Element var auf den Wert True erledigen.

Auf diese Weise füllen wir das Listenfeld lstTabellen und markieren gleichzeitig alle bereits verknüpften Tabellen. Dies gelingt übrigens nur, wenn der Name der Tabelle im SQL Server mit dem Namen der Verknüpfung übereinstimmt.

Testen der Verbindung

Damit wir Tabellenverknüpfungen herstellen können, benötigen wir eine funktionsfähige Verbindungszeichenfolge. Diese prüfen wir zuvor mit der Funktion VerbindungTesten, die wir in Listing 3 finden. Sie nimmt den Primärschlüsselwert des Eintrags der Tabelle tblVerbindungszeichenfolgen für die zu untersuchende Verbindungszeichenfolge entgegen. Außerdem enthält sie einen zweiten Parameter, mit dem die ermittelte Verbindungszeichenfolge zurückgegeben werden kann. Daneben hat die Funktion den Datentyp Boolean und liefert mit dem Rückgabewert die Information, ob die Verbindungszeichenfolge erfolgreich getestet werden konnte.

Public Function VerbindungTesten(lngVerbindungszeichenfolgeID As Long, _
         Optional strVerbindungszeichenfolge As String, Optional lngErrorNumber As Long, _
         Optional strErrorDescription As String) As Boolean
     Dim dbc As dao.Database
     Dim bolTrustedConnection As Boolean
     Dim bolVerbindungHergestellt As Boolean
     Set dbc = CodeDb
     bolTrustedConnection = dbc.OpenRecordset("SELECT TrustedConnection FROM tblVerbindungszeichenfolgen " _
         & "WHERE VerbindungszeichenfolgeID = " & lngVerbindungszeichenfolgeID).Fields(0)
     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, lngErrorNumber, strErrorDescription)
     If bolVerbindungHergestellt = False Then
         VerbindungTesten = False
         Exit Function
     End If
     VerbindungTesten = True
End Function

Listing 3: Testen der Verbindung für die Verbindungszeichenfolge

Die Prozedur ermittelt auf Basis des Feldes TrustedConnection, ob es sich um eine Verbindungszeichenfolge für den Zugriff mit Windows-Authentifizierung oder mit SQL Server-Authentifizierung handelt.

Wenn Benutzername und/oder Kennwort leer sind und die SQL Server-Authentifizierung verwendet werden soll, benötigen wir vermutlich noch die Anmeldedaten, wozu die Funktion LogindatenErmitteln mit der Verbindungszeichenfolge als Parameter aufgerufen wird (mehr dazu weiter unten). Liefert diese Funktion wiederum den Wert False zurück, wird der Test erfolglos abgebrochen.

Anderenfalls geht es mit den Daten, die durch diese Funktion in die Variablen strBenutzername und strKennwort geschrieben wurden, weiter.

Die Hilfsfunktion VerbindungszeichenfolgeNachID (siehe unten) erhält den Primärschlüsselwert der Verbindungszeichenfolge und soll den entsprechenden String ermitteln. Dieser landet anschließend in der Variablen strVerbindungszeichenfolge.

Damit rufen wir bei deaktivierter Fehlerbehandlung eine weitere Funktion namens VerbindungHerstellen auf und übergeben dieser die Verbindungszeichenfolge.

Das Ergebnis landet in der Variablen bolVerbindungHergestellt.

Enthält diese nun den Wert False, wird auch False als Ergebnis der Funktion VerbindungTesten zurückgegeben und die Funktion mit Exit Function verlassen. Anderenfalls wird der Wert True als Ergebnis von VerbindungTesten zurückgeliefert.

Ermitteln der Login-Daten per Formular

Die Funktion LogindatenErmitteln öffnet ein Formular, das in der Entwurfsansicht wie in Bild 7 aussieht. Sie öffnet das Formular als modalen Dialog und übergibt dieser die ID der zu verwendenden Verbindungszeichenfolgt als Öffnungsargument.

Bild 7: Entwurf des Formulars frmLogin

Nachdem das Formular mit der OK-Schaltfläche ausgeblendet wurde, läuft die aufrufende Prozedur weiter und liest den Benutzernamen und das Kennwort aus den dafür vorgesehenen Textfeldern des Formulars in die Variablen strBenutzername und strKennwort ein. Dann schließt sie das Formular und stellt den Rückgabewert der Funktion auf True ein:

Public Function LogindatenErmitteln( _
         lngVerbindungszeichenfolgeID) As Boolean
     DoCmd.OpenForm "frmLogin", WindowMode:=acDialog, _
         OpenArgs:=lngVerbindungszeichenfolgeID
     If IstFormularGeoeffnet("frmLogin") Then
         strBenutzername = Forms!frmLogin!txtBenutzername
         strKennwort = Forms!frmLogin!txtKennwort
         DoCmd.Close acForm, "frmLogin"
         LogindatenErmitteln = True
     End If
End Function

Das Formular löst beim Öffnen die Prozedur aus Listing 4 aus. Diese liest aus dem Öffnungsargument die zu verwendende Verbindungszeichenfolge ein und speichert sie in lngVerbindungszeichenfolgeID. Hat diese den Wert 0, verwendet die Prozedur die Standardverbindungszeichenfolge, die mit der Funktion StandardverbindungszeichenfolgeErmitteln eingelesen wird.

Private Sub Form_Open(Cancel As Integer)
     Dim dbc As DAO.Database
     Dim strVerbindungszeichenfolge As String
     Set dbc = CodeDb
     lngVerbindungszeichenfolgeID = Nz(Me.OpenArgs)
     If lngVerbindungszeichenfolgeID = 0 Then
         lngVerbindungszeichenfolgeID = StandardverbindungszeichenfolgeID
     End If
     Me!txtBenutzername = Nz(dbc.OpenRecordset("SELECT Benutzername FROM tblVerbindungszeichenfolgen " _
         & "WHERE VerbindungszeichenfolgeID = " & lngVerbindungszeichenfolgeID).Fields(0), strBenutzername)
     Me!txtKennwort = Nz(dbc.OpenRecordset("SELECT Kennwort FROM tblVerbindungszeichenfolgen " _
         & "WHERE VerbindungszeichenfolgeID = " & lngVerbindungszeichenfolgeID).Fields(0), strKennwort)
End Sub

Listing 4: Einstellen des Loginformulars beim Öffnen

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

den kompletten Artikel im PDF-Format mit Beispieldatenbank

diesen und alle anderen Artikel mit dem Jahresabo