Der große ADO-Kurs - Seite 6

von Florian Reischl

Command

Das Command-Objekt ist das dritte wichtige ADO-Objekt. Mit diesem kann man alle Arten von „Aktions-SQL-Statements“ ausführen (z. B. UPDATE, CREATE TABLE, INSTERT INTO, ...). Einzelne SQL-Statements können jedoch auch über die Connection mit der Methode Execute abgeschickt werden. Die eigentlich Kraft dieses Objektes kommt erst dann zum Einsatz wenn man mit StoredProcedures arbeitet, was auch mit Access in einem kleinem Maß möglich ist.

Im Gegensatz zu dem Recordset-Objekt kann man das Command optional entweder an eine Connection anbinden oder die Verbindung selbst erstellen.

Die Programmierer die den Umgang mit professionellen Datenbankservern bereits gewöhnt sind werden den Umgang mit StoredProcedures bereits kennen. Bei Access stellt sich vielleicht die Frage „Was und wie soll ich hier mit StoredProcedures und was ist das überhaupt?“.

Was sind StoredProcedures? StoredProcedures sind auf der Datenbank platzierte, oft sehr große, SQL-Routinen. Der Sinn liegt in vier Punkten. Erstens sind DB-Server meist wesentlich Leistungsfähiger als die zugreifenden Clients, somit können die Aktionen wesentlich effizienter ausgeführt werden. Zweitens durch sinnvoll aufgebaute StoredProcedures wird sehr viel Traffic zwischen der Datenbank und dem Client (der Datenbankanwendung) vermieden, das ist für das Netzwerk gut und bringt starke Performanceverbesserungen. Als Drittes kann durch StoredProcedures der meiste SQL-Code aus dem FrontEnd gebracht werden sodass sich die Anwendungsentwicklung wirklich nur mit der Anwendung beschäftigen muß. Der letzte nicht unerhebliche Grund ist, wenn der, für einen Datenbankentwickler grauenhafte (!), Fall eintritt, daß die Datenbankstruktur geändert werden muß ist es bei StoredProcedures nicht unbedingt nötig auch gleich die gesamte Anwendung neu zu schreiben sondern nur die Lokalen Prozeduren umzuschreiben.

Was mache ich in Access damit? In Access sind leider nur sehr bedingt größere SQL-Scripts möglich, da leider keine Variablen deklariert werden können, keine Cursor und Schleifen verfügbar sind und nicht mehrere Aktionen auf einmal ausgeführt werden können. Prinzipiell sind aber alle Nichtauswahlabfragen (also Löschabfragen, Anfügeabfragen, Pivot-Tabellen, …) als StoredProcedures zu betrachten. Durch ein paar Übergabeparameter kann man sich hier Slim-Prozeduren schreiben. In den folgenden Beispielen werden übrigens Übergabeparameter immer mit einem vorgestellten @-Zeichen geschrieben, das ist bei Access-Parametern zwar nicht nötig, aber beim SQL-Server so vorgeschrieben und wir wollen auch bei Access diese Schreibweise beibehalten.

5.1 Verbindung mit einer Datenbank

Es gibt, wie schon beschrieben, zwei Möglichkeiten ein Command-Objekt mit einer Datenbank zu verbinden.

5.1.1 Erstellen mit bestehender Connection

Dies ist die meistens sinnvolle Methode um ein Command mit einer DB zu verbinden. Es wird einfach mit einer bestehenden Connection verbunden:

Dim Cmd As New ADODB.Command
Cmd.ActiveConnection = Cn

5.1.2 Erstellen ohne Connection

Statt einer Connection kann auch ein String übergeben werden welcher dem ConnectionString gleich zusetzten ist:

Dim Cmd As New ADODB.Command

Cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\Test_DBs\Access\db3.mdb;" & _
"Jet OLEDB:Database Password=test;" & _
"Jet OLEDB:System database=F:\Test_DBs\Access\db3.mdw;" & _
"User ID=florianr;" & _
"Password=flo"

5.2 Execute

Die Execute-Methode ist ähnlich der des Connection-Objektes in seiner Grundfunktionalität. Es führt ein SQL-Statement aus. Bei dem Command-Objekt ist Execute jedoch wesentlich stärker. Der SQL-String wird nicht angehängt, sondern in den meisten Fällen über viele Parameter und Eigenschaften zusammengestellt. Ein wichtiger Parameter ist RecordsAffected, dieser ist kein Übergabeparameter sondern ein Rückgabeparameter. Bei Aktionen auf der Datenbank (löschen, ändern, hinzufügen) wird hier die Anzahl der betroffenen Datensätzen in eine übergebene Long-Variable eingetragen. Als Objekt liefert Execute, wie die Connection, einen Recordset zurück.

5.3 CommandType

Der CommandType gibt an was ausgeführt werden soll. Dafür liegt das CommandTypeEnum zu Grunde.

CommandTypeEnum

Konstante	Beschreibung
adCmdText	Es wird ein Text übergeben welcher z.B. ein SQL-Statement oder eine StoredProcedure (mit Parametern!) übergibt.
adCmdTable	Es wird als Text ein Tabellenname übergeben, somit wird ein Recordset zurückgeliefert welches die Tabelle 1:1 wiederspiegelt. (Intern wird ein SQL-Statement erstellt)
adCmdTableDirect	Es wird als Text ein Tabellenname übergeben, somit wird ein Recordset zurückgeliefert welches die Tabelle 1:1 wiederspiegelt. (Es wird kein SQL-Statement erstellt)
adCmdStoredProc	Es wird der Name einer StoredProcedure übergeben.
adCmdUnknown	Default. Es wird nicht festgesetzt was als CommandText gesetzt wird.
adCmdFile	Liefert ein Recordset zurück welches lokal auf dem Client als Datei gespeichert ist.
adExecuteNoRecords	Es werden explizit keine Daten an den Client zurückgeliefert. Vor allem beim Gebrauch mit StoredProcedures kann hier eine Datenrückgabe unterdrückt werden. Kombination mit adCmdText oder adCmdStoredProc.

5.4 CommandText

Der CommandText gibt einen String an welcher z.B. einen Tabellennamen, ein SQL-Statement oder den Namen einer StoredProcedure darstellt.

Ein paar Beispiele:

Öffnen einer Tabelle

Dim Cmd As New ADODB.Command

Cmd.ActiveConnection = Cn
Cmd.CommandType = adCmdTable
Cmd.CommandText = "tblName"
Set Rs = Cmd.Execute()

Ausführen einer StoredProcedure (ohne Parameter mit Recordset als Rücklieferung):

Dim Cmd As New ADODB.Command
Dim Rs As ADODB.Recordset

Cmd.ActiveConnection = Cn
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "sp_ProcName"
Cmd.Execute

Ausführen eines normalen SQL-Statements (Anzahl der gelöschten Datensätze wird zurückgeliefert):

Dim Cmd As New ADODB.Command
Dim i As Long

Cmd.ActiveConnection = Cn
Cmd.CommandType = adCmdText
Cmd.CommandText = "DELETE FROM tblName WHERE Alter > 80"
Cmd.Execute i
MsgBox i

5.5 CreateParameter

Diese Methode ist vor allem für Access-Entwickler eine geniale Erfindung! Wenn man die Parameter einer StoredProcedure nicht von der DB abfragen will, oder bei Access leider nicht kann, bietet diese Funktion eine sensationelle Selbsthilfe. Mit dieser Funktion ist es möglich für das Command selbst Parameter lokal zu erstellen und diese anzuhängen.

Die Syntax erwartet bis zu fünf Übergabeparameter:

5.5.1 Name

Name des zu erstellenden Parameters

5.5.2 Type

Art des zu übergebenden Parameters. Hierfür liegt das DataTypeEnum zu Grunde, eine genauere Beschreibung ist im Kapitel Recordset unter Fields-Type zu finden.

5.5.3 Direction

Gibt an in welche Richtung der Parameter agiert. Die Konstanten stammen aus dem ParameterDirectoinEnum. Bei Access sind mir leider nur Inputparameter bekannt.

ParameterDirectionEnum

Konstante	Beschreibung
adParamUnknown	Die Richtung des Parameters ist nicht bekannt.
adParamInput	Es handelt sich um einen Input-Parameter
adParamOutput	Es handelt sich um einen Output-Parameter
adParamInputOutput	Der Parameter ist sowohl Übergabeparameter, liefert aber auch einen Wert zurück.
adParamReturnValue	Beschreibt einen Return-Wert

5.5.4 Size

Diese Eigenschaft ist wie die gleichnamige Eigenschaft des Field-Objektes des Recordsets zu betrachten. Es ist eigentlich nur bei Text-Parametern nötig und gibt die maximale Länge des zu übergebenden Wertes an.

5.5.5 Value

Hier wird der eigentlich zu übergebende Wert eingetragen.

Ein Beispiel für den Einsatz dieser Funktion wird in der Beschreibung der Parameter geliefert.

5.6 Parameters

Diese Collection macht das Command erst zu dem was es ist, ein mächtiges Instrument im Umgang mit professionellen Datenbankanwendungen. Hier werden die Parameter für StoredProcedures (Aktionsabfragen in Access) angehängt.

Der sinn von StoredProcedures ist es ja möglichst dynamisch auf gewünschte Aktionen des Users einzugehen. Dafür werden Übergabeparameter benötigt und diese werden hier angehängt.

5.6.1 Refresh

Reine Access-Programmierer müssen die Beschreibung dieser sehr angenehmen Methode leider überspringen, Access bietet diese Funktionalität leider nicht.

Die Refesh-Methode verbindet sich kurz mit dem Server und fragt für die vorher angegebene Prozedur die Parameter ab und erstellt die Collection der Parameters neu. Dies verursacht zwar einen minimalen kurzen Verkehr im Netzwerk, welcher aber in keiner Relation steht zum lokalen erstellen der Parameter.

5.6.2 Append

Wer auf Refresh verzichten will, oder muß kann auch lokal die zu übergebenden Parameter erstellen und an das Command anhängen. Hierbei wird ein ADO-Parameter-Objekt angehängt.

Beispiele für Parameter
Ein Command mit Refresh und Rücklieferung eines OUTPUT-Parameters ausführen:

Private Sub Form_Load()
Dim Cmd As New ADODB.Command

With Cmd
    .ActiveConnection = CnT
    .CommandType = adCmdStoredProc
    .CommandText = "sp_New_Adress"
    .Parameters.Refresh
    .Parameters("@Nachname") = "Mustermann"
    .Parameters("@Vorname") = "Manfred"
    .Parameters("@Telefon") = "012-456789"
    .Execute
    MsgBox .Parameters("@AdressId")
End With

End Sub

Listing 23

Ein Command mit CreateParameter und Recordset als Rückgabe (für Access sehr wichtig!):

Private Sub Form_Load()
Dim Cmd As New ADODB.Command
Dim RsBestell As ADODB.Recordset

With Cmd
    .ActiveConnection = CnT
    .CommandType = adCmdStoredProc
    .CommandText = "sp_Search_Bestellungen"
    .Parameters.Append ..CreateParameter("@BestellDatum", _
    adDate, adParamInput, , Date)
    Set RsBestell = .Execute
End With

End Sub

Listing 24

Bei dem letzten Beispiel könnte bei Access beispielsweise ein großes SELECT-Statement hinterlegt sein welches über verschiedene JOINs eine Tabelle Bestell_Main mit einer Tabelle Bestell_Detail, einer Tabelle Artikel und einer Tabelle Einkaeufer verbindet.