Access Blog

Im zweiten Artikel in dieser Serien ging es darum wie man Gespeicherte Prozeduren in Access aufrufen kann, welche Parameter wie übergeben werden, und wie man gleichartige Gespeicherte Prozeduren mit einem universellen VBA-Prozedur aufruft. In diesem dritten und vorerst letzten Teil geht es nur noch um Feinheiten und Vereinfachungen.

Beispielsweise kann man für Gespeicherte Prozeduren die genau einen Rückgabewert liefern, eine universelle Prozedur schreiben.
Eine solche Prozedur in Access benötigt auf jedenfall ID-Feld und ID-Wert, welche beide zusammen das Abfragekriterium bilden.
Ist die Gespeicherte Prozedur universell gehalten (siehe 4. Codelisting im zweiten Teil der Serie), wird zusätzlich der Tabellennamen benötigt.
Bzw. bei einer speziell gehaltenen Gespeicherten Prozedur wird der Name der Gespeicherten Prozedur benötigt.

Ich verwende für letztere Zwecke die nachfolgende VBA-Prozedur:

Public Function singleProc(idWert As Double, idFeld As String, outputFeld As String, strProcname As String) As Variant
    ConnectionString = "Driver={SQL Server};Server=?;Database=?;Uid=?;Pwd=?;"
    Set cnn = OpenConnection(ConnectionString)
    Set cmdObj = New ADODB.Command
    With cmdObj
        .ActiveConnection = cnn
        .CommandText = strProcname
        .CommandType = adCmdStoredProc
        .CommandTimeout = 60
        .Parameters.Refresh
        .Parameters("@" & idFeld) = idWert
        .Execute
        singleProc = .Parameters("@" & outputFeld).Value
    End With
    Set cmdObj = Nothing
    Set cnn = Nothing
End Function

Damit kann jede gespeicherte Prozedur aufgerufen werden die genau 1 Inputvariable und genau 1 Outputvariable verlangt.
Der Namen beider Variablen können Variieren, sie werden ja als idFeld und outputFeld übergeben.

Ein anderes Beispiel befasst sich mit der mehrfachen Rückgabe aus einer Gespeicherten Prozedur. Bekanntlich kann eine Funktion nur 1 Rückgabewert haben. um aber trotzdem mehrere (viele) Werte als Rückgabewert zu erhalten braucht man ein Array. Nachfolgendes Beispiel demonstiert wie man das machen kann.

Function getAdressDaten(ProduktID As Long) As Variant
    Dim tmpArr(4) As Variant   'temporäres Array für die Rückgabewerte
    ConnectionString = "Driver={SQL Server};Server=?;Database=?;Uid=?;Pwd=?;"
    Set cnn = OpenConnection(ConnectionString)
    Set cmdObj = New ADODB.Command
    With cmdObj
        .ActiveConnection = cnn
        .CommandText = "prgAdressDaten"
        .CommandType = adCmdStoredProc
        .CommandTimeout = 60

        .Parameters.Refresh
        .Parameters("@kName") = Null
        .Parameters("@kVorname") = Null
        .Parameters("@kAdresse") = Null
        .Parameters("@kPLZ") = Null
        .Parameters("@kOrt") = Null
        .Parameters("@ProduktID") = ProduktID
        .Execute

        tmpArr(0) = .Parameters("@kName").Value
        tmpArr(1) = .Parameters("@kVorname").Value
        tmpArr(2) = .Parameters("@kAdresse").Value
        tmpArr(3) = .Parameters("@kPLZ").Value
        tmpArr(4) = .Parameters("@kOrt").Value
    End With
    getGehaeuseDaten = tmpArr()
    Set cmdObj = Nothing
    Set cnn = Nothing
Exit Function

Nach der Parameterübergabe und anschließender Ausführung der Abfrage mit Execute, erfolgt die Zuweisung der Output-Variablen an das temporäre Array, welches zum Schluss durch Zuweisung an den Funktionsnamen zurückgegeben wird.
Wenn man mit einer Gespeicherten Prozedur Daten wegschreibt, so muss man sich nicht darauf verlassen dass dabei alles glatt läuft. Interesant ist es doch zu erfahren, wieviel Datensätze bei einem Insert betroffen sind. Demnach kann man eine Prozedur in Access auch mit einem Rückgabewert versehen, und z.B. eine 1 zurückgeben, wenn OK, und eine 0 wenn nicht OK. wie das aussehen könnte zeigt das nachfolgende Codelisting:

Function saveArtikelPreis(ArtikelID As Long, Preis As Double) As Long
    Dim AnzahlDS As Long
    ConnectionString = "Driver={SQL Server};Server=?;Database=?;Uid=?;Pwd=?;"
    Set cnn = OpenConnection(ConnectionString)
    Set cmdObj = New ADODB.Command
    With cmdObj
        .ActiveConnection = cnn
        .CommandText = prgSaveArtikelPreis
        .CommandType = adCmdStoredProc
        .CommandTimeout = 60
        .Parameters.Refresh

        .Parameters("@ArtikelID") = ArtikelID
        .Parameters("@Preis") = Preis
        .Parameters("@Erfassungsdatum") = CStr(Format(Now(), "yyyymmdd hh:nn:ss"))
        .Execute RecordsAffected:=AnzahlDS
    End With

    If AnzahlDS > 0 Then 
        saveArtikelPreis = 1
    else
        saveArtikelPreis  = 0
    End if
End Function

Man rufe sich in Erinnerung, Execute ist eine Methode von ADODB und besitzt auch (Optionale) Eigenschaften und Parameter.
Und die Eigenschaft die Execute zurückgibt, ist RecordsAffected, also die Anzahl betroffener Datensätze. Diese wird im gleichen Aufwasch in die Variable AnzahlDS gespeichert.
Im nächsten Schritt wird durch einfachste If-Konstrukte ein Rückgabewert gebildet.

So das wars mit dieser Serie, du solltest nun das Rüstzeug haben um in eigenen Accessanwendungen Gespeicherte Prozeduren zu verwenden.
Zur Erinnerung, für ADODB benötigen wir den Verweis auf Microsoft ActiveX Data Object 2.1 Library.
Außerdem sei erwähnt dass dies auch mit der kostenlosen Express-Version von Microsoft möglich ist, alle Beispiele die ich gebracht habe sind auf einer 2008 Express Edition erstellt und getestet worden.

Hilfreich ist noch der folgende Link: www.connectionstrings.com, wo die Connectionstrings aller gebräuchlichen Datenbanksystemen mit ihren Parametern aufgelistet sind.

Bis dahin
Andreas Vogt ©2011