Access Listenfeld mit Stored Procedure aus MySQL füllen

Seit der Version 5.0 beherrscht auch MySQL endlich den Umgang mit Stored Procedures.
Bisher gab es nur die Möglichkeit über die Erstellung einer View eine komplexe Abfrage vom Access Frontend in das MySQL Server Backend zu verlagern. Der Nachteil der View: Es können keine Parameter übergeben werden.
In vielen Fällen müssen also immer noch große und eigentlich nicht benötigte Daten zum Access Frontend übertragen werden, da bisher eine Selektion dieser Daten auf Basis eines Parameters nur innerhalb von Access erfolgen konnte.
Diese Möglichkeit besteht allerdings jetzt mit Stored Procedures, da diesen beim Start Parameter übergeben werden können.
Der folgende Code wurde getestet für Access 2007 und MySQL 5.0.

Hier ist am Beispiel eines Listenfeldes die Vorgehensweise erklärt:

In einem Formular wird in einer Combobox ein Projekt ausgewählt,
anschließend sollen aus einer tabelle t_zeit die Summen der Arbeitszeiten,
gruppiert nach Kategorien für dieses Projekt angezeigt werden.
Die bisherige Vorgehensweise bestand daraus, nach der Auswahl des Projektes innerhalb von Access eine SQL Abfrage zu generieren und diese anschließend als RowSource an das Listenfeld zu hängen. Mit einem Listbox.Requery wird diese Abfrage dann gestartet und die entsprechenden Werte in der Listbox angezeigt.
Wird die Ausgangstabelle t_zeit jedoch größer, in diesem Fall ca. 70000 Datensätze, dauerte die Ausführung der SQL-Abfrage in der Kundenumgebung jedoch ca. 15 Sekunden, da zunächst alle Datensätze von t_zeit nach Access übertragen wurden und die Selektion, Gruppierung und Summierung innerhalb des Access Frontends stattfand.

Combobox zur Projektauswahl: me!Projekte
Listenfeld zur Anzeige: me!liste1

Code (ohne stored Procedure):

sql = "Select round(sum(datediff(ende-anfang,s)/3600,2) as zeit, kategorie from t_zeit where projekt_id = " & me!projekte & " GROUP BY t_Zeit.kategorie ORDER BY t_zeit.kategorie;
me!liste1.rowsource = sql
me!liste1.requery


Neues Vorgehen mit Stored Procedure

Zunächst ist am SQL-Server eine neue Stored Procedure mit einem Parameter anzulegen.
Dabei ist zu beachten, daß der Code nicht 1:1 aus Access übernommen werden kann, da jetzt nicht Access die SQL-Anweisung interpretiert, sondern der MySQL-Server.
In diesem Fall musste die Datediff Anweisung angepasst werden.

USE `DB_NAME`;
DROP procedure IF EXISTS `sp_kategoriestunden`;
DELIMITER $$
USE `DB_NAME`$$
CREATE PROCEDURE `sp_kategoriestunden` (IN projekt_id int)
BEGIN
SELECT T_Zeit.Kategorie as Kat, Round(Sum(TIME_TO_SEC(TIMEDIFF(ende,anfang)))/3600,2) AS Zeit,
FROM t_zeit
WHERE (t_zeit.projekt_ID = projekt_id)
GROUP BY T_Kategorie.Kategorie
ORDER BY T_Kategorie.Kategorie;
END $$
DELIMITER ;


Anschließend ist in Access eine sogenannte „PassThrough-Abfrage“ zu erstellen.
Eine PassThrough-Abfrage enthält alle Verbindungsinformationen die der ODBC-Treiber benötigt, um sich mit dem MySQL-Server und der gewünschten Datenbank zu verbinden.
Diese Informationen müssen beim erstellen der Abfrage eingetragen werden.

Anschließend kann die SQL-Anweisung dieser PassThrough Abfrage mit dem Aufruf der Stored Procedure und dem darin enthaltenen Parameter im VBA Code gesetzt werden.


'Stored Procedure aufrufen
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("PassThroughQuery01")
qdf.sql = "call sp_kategoriestunden (" & Nz(Me!projekte, 0) & ");"

Me!liste1.RowSourceType = "Table/Query"
Me!liste1.RowSource = "PassThroughQuery01"
Me!liste1.Requery


Da jetzt der MySQL-Server die Daten selektiert und aggregiert, werden nur noch die fertigen Zeilen für die Listbox übertragen.
In diesem Fall ergab sich ein Geschwindigkeitszuwachs um ca. Faktor 100 !!!
Besonders effektiv ist dieses Vorgehen wenn als Quelle nicht nur die Daten einer Tabelle,
sondern die Daten mehrerer umfangreicher mittels joins verknüpfter Tabellen abgefragt werden sollen
und die Ergebnismenge relativ klein ist.