Query-Analyzer für Access

Christoph Spielmann, Düsseldorf

Wenn Sie schon einmal mit dem Microsoft SQL-Server gearbeitet haben, werden Sie sicherlich den Query-Analyzer schätzen gelernt haben. Mit diesem Tool können Sie schnell und einfach eine SQL-Abfrage ausführen und das Ergebnis begutachten. Dieser Artikel stellt Ihnen einen Query-Analyzer zum Einsatz unter Access vor.

Man mag es kaum glauben, aber auch der Microsoft SQL Server hat einmal in der Version 1.0 das Licht der Welt erblickt. 1989 hatte Microsoft die erste Version für das Betriebssystem OS/2 herausgebracht, das damals noch unter der Federführung von Microsoft in Zusammenarbeit mit IBM entwickelt wurde. Die Basis des SQL-Servers stammte hierbei vom Sybase SQL-Server 4.0, einem Datenbank-Server für Unix und VMS. Das Typische für diese Datenbank-Server war zu jener Zeit, dass jegliche Kommunikation zwischen Benutzer und Server nur via SQL erfolgen konnte. Dies betraf zum Beispiel das Anlegen von Tabellen, das Hinzufügen von Benutzern, die Vergabe von Zugriffsrechten und natürlich auch das Abfragen von Daten. Die Eingabe von SQL-Anweisungen erfolgt hierbei über den Query-Analyzer (s. Abb. 1) beziehungsweise das DOS-Gegenstück „iSQL“. Erst später stellte Microsoft mit dem Enterprise-Manager eine Benutzeroberfläche zur Verfügung, die dem Benutzer Aufgaben wie beispielsweise die Anlage von Tabellen auch ohne komplizierte SQL-Anweisungen ermöglichte.

Abb. 1: Der Query-Analyzer des Microsoft SQL-Servers

Trotz Enterprise-Managers wird der Query-Analyzer noch von vielen Benutzern eingesetzt, da zum Beispiel die Eingabe von DELETE FROM Kunden zum Löschen von Datensätzen für viele der schnellste Weg ist.

Auch zum Test und zur Fehlerbehebung von SQL-Anweisungen ist der Query-Analyzer gut geeignet.

Die hier vorgestellte Lösung des Query-Analyzers bietet die wichtigsten Funktionen nun auch unter Access. Hierzu zählen die Eingabe und Ausführung von SQL-Statements sowie das Speichern und Laden von Abfragen.

Abb. 2: Erforderliche Verweise zum Einsatz des Query-Analyzers

Abb. 3: Der Access-Query-Analyzer in der Ergebnistabellenansicht …

Abb. 4: … und in der Ergebnisansicht

Der Query-Analyzer für Access besteht aus einem einzelnen Formular, das Sie problemlos in Ihre Datenbank importieren können. Von dort aus können Sie den Query-Analyzer dann aus dem Datenbankfenster heraus öffnen und einsetzen.

Voraussetzung ist lediglich, dass Sie Ihrem Projekt Verweise auf die Objekte ADO und ADOX hinzufügen (siehe Abb. 2).

Ein Einsatz des Query-Analyzers als Add-In ist leider nicht möglich, wie später noch erläutert wird.

Eingabe- und Ergebnisbereich

Das Formular des Query-Analyzers ist zweigeteilt. Im oberen Bereich befindet sich der Eingabebereich für SQL-Anweisungen.

Der untere Ergebnisbereich zeigt das Ergebnis an. Sollte es sich bei der Anweisung um eine SELECT-Anweisung handeln, wird das Ergebnis automatisch als Tabelle angezeigt.

Bei allen anderen Anweisungen – die keine Datensätze zurückliefern – erscheint eine entsprechende Meldung.

Daten und Meldungen sind im unteren Bereich durch ein Register-Steuerelement umschaltbar (s. Abb. 3 und Abb. 4).

Bei SELECT-Abfragen wird automatisch die Ergebnistabelle gefüllt. Als Meldung wird die Anzahl der zurückgelieferten Datensätze ausgegeben.

Bei allen anderen Abfrage-Typen (zum Beispiel DELETE, UPDATE oder INSERT INTO) wird das Register Daten automatisch ausgeblendet und lediglich das Meldungsfenster angezeigt.

Hier wird dann die Anzahl der von der Anweisung betroffenen Datensätze ausgegeben.

Bei DELETE-Anweisungen ist dies zum Beispiel die Anzahl der gelöschten Datensätze.

Sollte die angegebene Anweisung einen Fehler enthalten, wird dieser ebenfalls als Meldung ausgegeben (s. Abb. 5).

Abb. 5: Die Anzeige einer Fehlermeldung

Abb. 6: Auswahl einer gespeicherten Abfrage

Ausführen undprüfen von Anweisungen

Im Kopf des Query-Analyzers befindet sich eine Symbolleiste. Mit Hilfe des Pfeil-Symbols führen Sie die aktuelle Abfrage aus. Alternativ können Sie die SQL-Anweisung auch nach dem Vorbild des SQL-Servers mit der Taste F5 starten.

Eine besondere Bedeutung hat das Prüf-Symbol direkt links daneben. Hiermit können Sie eine Abfrage prüfen, ohne dass hierbei die Daten verändert werden. Dies ist insbesondere bei komplexen Löschabfragen hilfreich.

öffnen undspeichern vonAbfragen

Eine weitere wichtige Funktion ist das Speichern und öffnen einer Abfrage.

Zu diesem Zweck stehen im Kopfbereich zwei entsprechende Symbole zur Verfügung.

Im Gegensatz zum Query-Analyzer des SQL-Servers werden die Abfragen jedoch nicht in Text-Dateien, sondern innerhalb der Datenbank als normale Access-Abfragen gespeichert.

Auch beim Laden einer Abfrage greifen Sie auf alle Access-Abfragen zu.

Beim Laden einer Abfrage ändert sich der Eingabebereich des Query-Analyzers in eine Auswahlliste (s. Abb. 6).

Hier können Sie eine Abfrage durch einen Doppelklick auswählen, deren SQL-Anweisung dann im Eingabebereich angezeigt wird.

Private Sub ExecuteSQL(CheckOnly As Boolean)
    Dim SQLError As Boolean
    If SQL = "" Then
        MsgBox "Bitte geben Sie eine SQL-Anweisung ein!", vbInformation
        Exit Sub
    End If
    Me.txtMessage.Value = ""
    If ReturnsData() Then
        If CheckOnly = False Then
            Me.lstData.RowSource = SQL
            Me.lstData.Requery
            On Error Resume Next
            Me.lstData.ColumnCount = Me.lstData.Recordset.Fields.Count
            If Err = 0 Then
                SQLError = False
            Else
                SQLError = True
            End If
            On Error GoTo 0
        End If
        If SQLError = True Or CheckOnly = True Then
            Dim tempData As New Recordset
            With tempData
                .ActiveConnection = CurrentProject.Connection
                .Source = SQL
                .CursorType = adOpenStatic
                .LockType = adLockReadOnly
                .CursorLocation = adUseClient
                On Error Resume Next
                .Open
                If Err <> 0 Then
                    Me.txtMessage.Value = Err.Description
                    Me.tabMessage.SetFocus
                    Me.tabData.Visible = False
                Else
                    If CheckOnly Then
                        Me.txtMessage.Value = "Die SQL-Anweisung wurde erfolgreich " _                            & "geprüft"
                        Me.tabMessage.SetFocus
                        Me.tabData.Visible = False

Quellcode 1 (Start)

                    Else
                        Me.tabData.Visible = True
                        Me.tabData.SetFocus
                    End If
                End If
            End With
        Else
            Me.tabData.Visible = True
            Me.tabData.SetFocus
            Me.txtMessage.Value = CStr(Me.lstData.ListCount - 1) _                & " Datensätze abgefragt"
        End If
    Else
        Me.tabMessage.SetFocus
        Me.tabData.Visible = False
        Dim RecordsAffected As Long
        If CheckOnly Then CurrentProject.Connection.BeginTrans
        On Error Resume Next
        CurrentProject.Connection.Execute SQL, RecordsAffected
        If Err <> 0 Then
            Me.txtMessage.Value = Err.Description
        Else
            If CheckOnly Then
                Me.txtMessage.Value = "Die SQL-Anweisung wurde erfolgreich geprüft"
            Else
                Me.txtMessage.Value = CStr(RecordsAffected) & " Datensätze betroffen"
            End If
        End If
        If CheckOnly Then CurrentProject.Connection.RollbackTrans
    End If
    Me.txtSQL.SetFocus
    Me.txtSQL.SelLength = 0
End Sub

Quellcode 1 (Fortsetzung)

Hinweis

Auf der CD zu dieser Ausgabe finden Sie zwei Versionen des Query-Analyzers, eine für Access XP und eine für Access 2000. Der Unterschied besteht darin, dass in der Access-XP-Version die Anzahl der Spalten mit Hilfe der Recordset-Eigenschaft des Listenfeldes ermittelt werden kann. Dies ist bei Access 2000 leider nicht möglich, weswegen die Spaltenanzahl immer auf 255 gesetzt wird. Weiterhin ist die Erkennung von Fehlern in der SQL-Anweisung bei der XP-Variante zuverlässiger. Die 2000er-Variante geht dagegen schon dann von einem Fehler in der Anweisung aus, wenn keine Datensätze zurückgeliefert werden (was ja nicht unbedingt einem Fehler in der Anweisung gleichkommt). In der Praxis hat dies jedoch kaum negative Auswirkungen. Aufgrund des besseren Funktionsumfangs wird im Folgenden die Version für Access XP beschrieben.

Damit der Query-Analyzer problemlos in andere Datenbanken importiert werden kann, befindet sich die gesamte Logik innerhalb des Formulars frmQueryAnalyzer beziehungsweise dem dazugehörigen Formularmodul.

Dies ist auch der Grund dafür, warum beim öffnen einer gespeicherten Abfrage nicht ein Auswahlfenster erscheint, sondern der obere Eingabebereich des Formulars durch eine Auswahlliste ersetzt wird. Abbildung 7 zeigt den Aufbau des Formulars in der Entwurfsansicht.

Abb. 7: Der Query-Analyzer in der Entwurfsansicht

Ausführen einer Abfrage

Das Ausführen einer Abfrage erledigt die Prozedur ExecuteSQL (s. Quellcode 1).

Die Prozedur prüft zunächst, ob überhaupt eine SQL-Anweisung eingegeben wurde. Wenn dies nicht der Fall ist, gibt sie eine Fehlermeldung aus.

Anschließend wird ein eventuell vorhandener Meldungstext gelöscht und mittels ReturnsData-Funktion geprüft, ob es sich bei der SQL-Anweisung um eine Auswahl-Abfrage (SELECT) oder eine Aktionsabfrage (DELETE, INSERT INTO, UPDATE …) handelt.

Den Aufbau dieser Funktion lernen Sie später noch kennen.

Im Falle einer Auswahlabfrage muss das Ergebnis abgefragt und angezeigt werden. Zu diesem Zweck befindet sich im Ergebnisbereich ein Listenfeld namens lstData. Diesem wird mittels RowSource-Eigenschaft die SQL-Anweisung zugewiesen und das Ergebnis mittels Requery-Methode abgefragt und angezeigt.

Schließlich wird noch die Anzahl der Felder ermittelt und diese der ColumnCount-Eigenschaft zugewiesen. Diese Anweisung ist in eine Fehlerbehandlung mittels On Error Resume Next gekapselt. Das hat den folgenden Grund: Wenn die SQL-Anweisung fehlerhaft ist, tritt beim Aufruf der Requery-Methode kein Fehler auf. Dies ist jedoch beim Zugriff auf die Recordset-Eigenschaft der Fall. Hier muss der Fehler also abgefangen werden. Zusätzlich wird die Variable SQLError auf True gesetzt, um den nachfolgenden Anweisungen mitzuteilen, dass die SQL-Anweisung einen Fehler enthält.

Zur Vervollständigung sei noch erwähnt, dass die Aktualisierung der Liste nur dann erfolgt, wenn die SQL-Anweisung nicht geprüft, also wirklich ausgeführt werden soll.

Hinweis

Der Einsatz des Listenfeldes ist der Grund dafür, warum die Datenbank nicht als Add-In eingesetzt werden kann. Die RowSource-Eigenschaft des Listenfeldes kann nicht aus einem Add-In heraus auf die Daten der Benutzerdatenbank zurückgreifen. Die einzige Lösung wäre, das Abfrageergebnis in einer dynamisch erzeugten Access-Tabelle zwischenzuspeichern, was jedoch insbesondere bei großen Datenmengen zu deutlichen Performanceeinbußen führen würde.

Private Property Get SQL() As String
    Dim SQLText As String
    Me.txtSQL.SetFocus
    SQL = FullTrim(Me.txtSQL.Text)
End Property

Quellcode 2

Private Function FullTrim(ByVal Text As String) _    As String
    Dim i As Integer
    For i = 1 To Len(Text)
        If Asc(Mid(Text, i, 1)) < 32 Then
            Mid(Text, i, 1) = " "
        End If
    Next i
    FullTrim = Trim(Text)
End Function

Quellcode 3

Private Function ReturnsData() As Boolean
    If Left(SQL, 6) = "SELECT" Then
        ReturnsData = True
    Else
        ReturnsData = False
    End If
End Function

Quellcode 4

Private Sub btnExecuteSQL_Click()
    ExecuteSQL False
End Sub

Quellcode 5

Private Sub txtSQL_KeyDown(KeyCode As _    Integer, Shift As Integer)
    If KeyCode = 116 Then
        ExecuteSQL False
    End If
End Sub

Quellcode 6

Wenn in der SQL-Anweisung ein Fehler vorhanden ist, wird die Abfrage nochmals ausgeführt, diesmal jedoch unter Zuhilfenahme eines ADO-Recordset-Objekts.

Bei der Ausführung der Open-Methode tritt hier der erwartete Laufzeitfehler auf, dessen Meldung schließlich als Text ausgegeben wird.

Das ADO-Recordset kommt auch dann zur Ausführung, wenn die SQL-Anweisung lediglich geprüft werden sollte. Wenn die Prüfung erfolgreich verläuft, erscheint eine Erfolgsmeldung, andernfalls eine Fehlermeldung.

Je nachdem wird auch das Register Daten oder Meldung eingeblendet, damit der Benutzer das Ergebnis der SQL-Anweisung direkt begutachten kann.

Im unteren Teil der ExecuteSQL-Prozedur befindet sich der Block zur Ausführung von Aktionsabfragen. Wesentlich ist hierbei die Ausführung der Execute-Methode des Objekts CurrentProject.Connection.

Als Parameter werden der SQL-Befehl sowie die leere Variable RecordsAffected übergeben.

Letztere füllt Access nach erfolgreicher Ausführung mit der Anzahl der betroffenen Datensätze.

Diese Information ist zum Beispiel bei Lösch- oder Aktualisierungsabfragen interessant. Sollte ein Fehler auftreten, erfolgt wieder die Ausgabe des Fehlertextes.

Besondere Wichtigkeit liegt bei der Handhabung der Prüf-Funktion: Da Aktionsabfragen die Daten verändern, muss mit Hilfe einer Transaktion gearbeitet werden.

Diese wird mit CurrentProject.Connection.BeginTrans gestartet und am Ende der Prüfung mit CurrentProject.Connection.Rollback wieder rückgängig gemacht.

Hinweis

Transaktionen sind unter Access leider nicht sehr sicher. Anders als beim SQL-Server kann Access Transaktionen, die nicht durch Rollback oder CommitTrans beendet wurden, nicht mehr zurückfahren. Dies wäre zum Beispiel bei einem Absturz von Access oder einem Stromausfall der Fall. Die Prüf-Funktion ist also mit gewisser Vorsicht zu verwenden.

Private Sub btnOpen_Click()
    Dim QueryList As String
    Dim MyCatalog As New ADOX.Catalog
    Dim i As Integer
    MyCatalog.ActiveConnection = CurrentProject.Connection
    For i = 0 To MyCatalog.Views.Count - 1
         QueryList = QueryList + MyCatalog.Views(i).Name + ";"
    Next i
    For i = 0 To MyCatalog.Procedures.Count - 1
        If Left(MyCatalog.Procedures(i).Name, 1) <> "~" Then
            QueryList = QueryList + MyCatalog.Procedures(i).Name + ";"
        End If
    Next i
    If Len(QueryList) > 0 Then
        QueryList = Left(QueryList, Len(QueryList) - 1)
    End If
    With Me.lstQueryList
        .RowSource = QueryList
    End With
    SetQuerySelectMode True
End Sub

Quellcode 7

Einsatz von Hilfsfunktionen

Wie bereits zu Beginn erwähnt, werden von der Prozedur ExecuteSQL einige Hilfsprozeduren verwendet.

Eine hiervon ist als Eigenschaft ausgelegt. Sie trägt den Namen SQL und ermittelt den vom Benutzer eingegebenen SQL-Text (Quellcode 2).

Die Besonderheit ist hierbei, dass mit Hilfe der FullTrim-Eigenschaft alle führenden Leerzeichen und Leerzeilen abgeschnitten werden. Die Funktion hat den in Quellcode 3 beschriebenen Aufbau.

Mit einer Schleife werden hier alle Sonderzeichen mit einem ASCII-Code kleiner als 32 durch Leerzeichen ersetzt.

Abschließend wird noch die Trim-Funktion von Access aufgerufen, um führende und nachfolgende Leerzeichen abzuschneiden.

Die Hilfsfunktion ReturnsData haben Sie bereits kennen gelernt. Sie prüft, ob es sich bei der Abfrage um eine Auswahl oder eine Aktionsabfrage handelt. Zu diesem Zweck prüft die Funktion, ob die SQL-Anweisung mit dem Text SELECT beginnt. Hierbei greift sie auf die SQL-Eigenschaft zu, die bereits führende Leerzeichen und -zeilen entfernt hat (s. Quellcode 4).

Public Sub SetQuerySelectMode(Activate As Boolean)
    If Activate Then
        Me.lstQueryList.Visible = True
        Me.lstQueryList.SetFocus
        Me.txtSQL.Visible = False
        Me.btnSelectQueryCancel.Visible = True
        Me.btnSelectQueryOK.Visible = True
    Else
        Me.txtSQL.Visible = True
        Me.txtSQL.SetFocus
        Me.lstQueryList.Visible = False
        Me.btnSelectQueryCancel.Visible = False
        Me.btnSelectQueryOK.Visible = False
    End If
End Sub

Quellcode 8

Private Sub btnSelectQueryOK_Click()
    If Me.lstQueryList.ItemsSelected.Count = 0 Then
        MsgBox "Bitte Abfrage markieren.", vbInformation
    Else
        Dim SQLName As String
        Dim MyCatalog As New ADOX.Catalog
        Dim MyView As ADOX.View
        Dim MyProc As ADOX.Procedure
        MyCatalog.ActiveConnection = _            CurrentProject.Connection
        SQLName = Me.lstQueryList.Value
        On Error Resume Next
        Set MyView = MyCatalog.Views(SQLName)
        Me.txtSQL.Value = MyView.Command.CommandText
        If Err <> 0 Then
            Set MyProc = MyCatalog.Procedures(SQLName)
            Me.txtSQL.Value = MyProc.Command.CommandText
        End If
        SetQuerySelectMode False
    End If
End Sub

Quellcode 9

Der Start der ExecuteSQL-Funktion erfolgt sowohl von der Schaltfläche der Symbolleiste aus, als auch durch Drücken der Taste F5.

Hierfür sind die folgenden beiden Ereignisprozeduren aus Quellcode 5 und 6 verantwortlich. Letztere ist mit dem KeyDown-Ereignis des Textfeldes verbunden. Sie prüft, ob es sich bei dem KeyCode um die Nummer 116 handelt, die der F5-Taste entspricht.

Laden von SQL-Anweisungen

Das Laden von SQL-Anweisungen erledigt die Ereignisprozedur btnOpen_Click (s. Quellcode 7).

Die Prozedur erzeugt eine Liste aller Abfragen, indem sie mittels ADOX die Namen aller Auswahl-Abfragen (View) und Aktionsabfragen (Procedures) zu einer mit Semikolons getrennten Zeichenkette zusammen stellt und diese der Auswahlliste lstQueryList als Werteliste zuweist. Bei Aktionsabfragen werden hierbei die von Access angelegten temporären Abfragen ignoriert. Diese sind an einer führenden Tilde im Namen zu erkennen.

Nach Füllen der Liste wird das Formular in den Abfrageauswahlmodus umgeschaltet. Dies bedeutet, dass der Eingabebereich ausgeblendet und die Auswahlliste zusammen mit den beiden Schaltflächen OK und Abbrechen eingeblendet wird. Diese Aufgabe erledigt die Prozedur SetQuerySelectMode (s. Quellcode 8).

Sofern der Benutzer eine Abfrage ausgewählt und auf OK geklickt hat, wird die Prozedur aus Quellcode 9 ausgeführt. Diese greift mittels ADOX auf die Abfrage zu und liest die SQL-Anweisung aus. Da die Prozedur anhand des Namens nicht unterscheiden kann, ob es sich um eine Auswahlabfrage (View) oder eine Aktionsabfrage (Procedure) handelt, versucht sie zunächst den Zugriff per View und im Fehlerfall einen Zugriff per Procedure. Die Anweisung wird schließlich mit der Command.CommandText-Eigenschaft ausgelesen und in das Eingabefeld eingetragen. Abschließend erfolgt durch Aufruf der SetQuerySelectMode-Prozedur wieder eine Umschaltung vom Abfrageauswahlmodus in den Eingabemodus.

Private Sub btnSave_Click()
    If SQL = "" Then
        MsgBox "SQL-Anweisung eingeben!", vbInformation
        Exit Sub
    End If
    Dim QueryName  As String
    Dim MyCommand As New ADODB.Command
    QueryName = InputBox("Abfragename")
    If Len(QueryName) > 0 Then
    On Error GoTo ErrHandler
        Dim MyCatalog As New ADOX.Catalog
        MyCatalog.ActiveConnection = _            CurrentProject.Connection
        If ReturnsData Then
            MyCommand.CommandText = SQL
            MyCatalog.Views.Append QueryName, MyCommand
        Else
            MyCommand.CommandText = SQL
            MyCatalog.Procedures.Append QueryName, _                MyCommand
        End If
    End If
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbCritical
    Exit Sub
End Sub

Quellcode 10

Speichern von SQL-Anweisungen

Das Speichern von SQL-Anweisungen funktioniert ebenfalls mit ADOX:

Nach der Prüfung, ob eine SQL-Anweisung vorhanden ist, wird der Benutzer zur Eingabe eines Abfragenamens aufgefordert. Danach erfolgt die Anlage der View beziehungsweise Procedure (s. Quellcode 10).

Unter Umständen ist die neue Abfrage nicht direkt im Datenbankfenster sichtbar.

Schalten Sie in diesem Fall einfach in ein anderes Register um und danach in das Abfragen-Register zurück.

Hinweis

Aus Sicherheitsgründen überschreibt der Query-Analyzer generell keine Abfragen. Sollten Sie also eine Abfrage öffnen, modifizieren und dann wieder unter dem gleichen Namen speichern, tritt ein Fehler auf. Löschen Sie in diesem Fall die Abfrage vorher aus dem Datenbankfenster von Access.

Der Query-Analyzer dürfte bereits im jetzigen Zustand für Access-Anwender mit SQL-Know-how eine deutliche Arbeitserleichterung bedeuten.

Trotzdem besteht natürlich Potenzial für weitere Entwicklungen. Denkbar wären zum Beispiel die folgenden Funktionen:

  • übersichtliche Formatierung von SQL-Anweisungen
  • Implementierung als Add-In unter Inkaufnahme der Performance-Nachteile
  • Speichern von Abfragen außerhalb von Access als Textdateien
  • Unterstützung von SQL-Scripten nach dem Vorbild des SQL-Servers
  • Downloads zu diesem Beitrag

    Enthaltene Beispieldateien:

    acc0503ml01_XP.mdb

    Download

    Schreibe einen Kommentar