 | Unser Angebot für Sie! Lesen Sie diesen Beitrag und 500 andere sofort im Onlinearchiv, und erhalten Sie alle zwei Monate brandheißes Access-Know-how auf 72 gedruckten Seiten! Plus attraktive Präsente, zum Beispiel das bald erscheinende Buch 'Access 2010 - Das Grundlagenbuch für Entwickler'! |
| | | | | |
Zusammenfassung
Füllen Sie Formulare mit ADO-Recordsets aus Internet-Datenbanken und steigern Sie so die Performance.
Techniken
Formulare, Kombinationsfelder, VBA, ADO
Voraussetzungen
Access 2000 und höher
Beispieldateien
ADODB_MySQL.mdb
Shortlink
www.access-im-unternehmen.de/702
Performanter Webzugriff auf MySQL-Datenbanken
André Minhorst, Duisburg
Wer einen Webserver sein Eigen nennt oder anderweitig Gelegenheit zum Ablegen von Daten in einer MySQL-Datenbank hat, kann dies für verschiedene Zwecke nutzen: Primär sollen Datenbanken auf Webservern natürlich Inhalte für Internetseiten liefern, aber Sie können diese natürlich auch einfach als zentrale Datenbasis verwenden und von überall auf diese zugreifen. Wenn dies auch noch in akzeptabler Zeit gelingen soll, müssen Sie jedoch einige Regeln beachten.
Wenn Sie direkt auf eine Datenbank auf einem Webserver zugreifen können, ergeben sich eine ganze Menge Möglichkeiten.
Sie können nicht nur, wie oben beschrieben, Daten zentral speichern und dann von überall darauf zugreifen, sondern natürlich auch mehreren Personen gleichzeitig Zugriff auf die Daten verschaffen.
Außerdem sparen Sie sich damit unter Umständen die aufwendige Programmierung von Web-Backends für die Datenbanken Ihrer Webseiten: Sie können die Daten nämlich, eine geeignete Verbindung und Access-Anwendung vorausgesetzt, ganz einfach per Formular ändern oder per Bericht anzeigen lassen.
Dies ändert natürlich nichts an der Tatsache, dass Sie für die Besucher der Webseite ein HTML-Frontend bauen müssen, aber in vielen Fällen stellt die Administration der auf der Webseite dargestellten Daten eine wesentlich höhere Herausforderung als die eigentliche Webseite dar.
Was brauchen Sie, um vom vorliegenden Beitrag profitieren zu können? Da wäre als Erstes eine MySQL-Datenbank irgendwo auf einem Webserver und zweitens die Möglichkeit, per ODBC darauf zuzugreifen - sei es direkt oder über den Umweg eines Tunnels, wie im Beitrag Access und MySQL (Shortlink 240) beschrieben.
Dies zieht nach sich, dass Sie auf Ihrem lokalen Rechner einen ODBC-Connector für die MySQL-Datenbank installieren müssen. Die aktuellste Version finden Sie unter dem Link http://dev.mysql.com/downloads/connector/odbc/5.1.html. Als Nächstes müssen Sie die Verbindungsparameter für den Aufbau einer Verbindung zum MySQL-Server auf dem Webserver ermitteln. Eine gute Anlaufstelle finden Sie unter http://www.connectionstrings.com/.
Der Standardweg
Normalerweise würden Sie die Tabellen der MySQL-Datenbank mit dem Access-Frontend per ODBC verknüpfen. Dies funktioniert insbesondere deshalb sehr gut, weil Sie die Tabellen fast genau wie in der lokalen Access-Datenbank enthaltene Tabellen behandeln können - sie lassen sich in Abfragen zusammenführen, an Formulare, Berichte und die enthaltenen Steuerelemente binden und auch der Zugriff per ADO oder DAO geschieht problemlos.
Dummerweise gibt es ein Problem: Wenn die Datenmenge wächst oder Abfragen über mehrere Tabellen abgefragt werden sollen, gerät etwa die Anzeige von Daten in einem Formular mitunter zum Geduldsspiel.
Welche Alternativen gibt es hierzu? Wenn es um das reine Anzeigen von Daten geht, diese also nicht bearbeitet werden sollen, können Sie PassThrough-Abfragen verwenden.
Diese unterscheiden sich dadurch von herkömmlichen Abfragen, dass sie erstens im nativen SQL-Dialekt des DBMS formuliert sein müssen, die Angabe einer entsprechenden ODBC-Verbindung erfordern und außerdem einen entscheidenden Nachteil haben: sie lassen keine Veränderungen an den gelieferten Daten zu.
Und damit kommen wir zur Methode dieses Beitrags: Der Zugriff erfolgt ausschließlich über ADO-Recordsets und unterscheidet sich somit wesentlich von den üblicherweise verwendeten Möglichkeiten zum Binden von Formularen, Berichten und Steuerelementen an Datenherkünfte wie Tabellen oder Abfragen.
Wie aber können wir ein Formular, einen Bericht oder auch ein Kombinationsfeld mit Daten füllen, auf die wir nur per ADO zugreifen? ADO wird doch normalerweise ausschließlich in VBA-Prozeduren verwendet?
Per VBA, ADODB und ODBC auf eine Tabelle zugreifen
Beginnen wir gleich dort, nämlich beim Einlesen von Daten via VBA, DAO und ODBC. Kern dieser Vorgehensweise sind wie üblich ein Connection- und ein Recordset-Objekt, die allerdings etwas anders als sonst üblich gefüllt werden. Als Beispieltabellen verwenden wir vier einfache Tabellen, deren Beziehungen wie in Abb. 1 aufgebaut sind. Um die wichtigsten Fälle abzudecken, haben wir eine 1:n- und eine m:n-Beziehung eingebaut.
Abb. 1: Datenmodell der Beispieldatenbank
Diese haben wir in Access erstellt und mit den im Beitrag Von Access nach MySQL (Shortlink ****) beschriebenen Techniken in eine auf einem Webserver liegende MySQL-Datenbank migriert. Wenn wir mit ADO arbeiten, was in diesem Fall die schnellste Variante ist, benötigen wir zunächst einmal ein Connection-Objekt. Da eine Anwendung meist über mehrere Formulare verfügt und diese alle schnell mit Daten gefüllt werden sollen, machen wir das Connection-Objekt über eine globale Funktion verfügbar. Die folgenden Codezeilen liegen alle in einem Standardmodul namens mdlMySQL. Den Start macht die Deklaration einer Connection-Variablen zum Speichern eines Verweises auf die aktuelle Verbindung:
Private m_Connection As ADODB.Connection
Damit wir den Connectionstring nicht an verschiedenen Stellen ändern müssen, wenn dies einmal notwendig ist, schreiben wir ihn in eine Konstante:
Public Const strConnection As String = "Driver={MySQL ODBC 5.1 Driver};Server=<IP des Webservers>;Database=<Datenbankname>;User=<Benutzername>;Password=<Kennwort>;Option=3;"
Die folgende Funktion liefert schließlich ein Connection-Objekt. Dabei prüft sie, ob schon ein Connection-Objekt vorliegt, und gibt dieses zurück - falls nicht, erzeugt sie eine neue Verbindung:
Public Function GetConnection() As _
ADODB.Connection
If m_Connection Is Nothing Then
Set m_Connection = New ADODB.Connection
m_Connection.Open strConnection
End If
Set GetConnection = m_Connection
End Function
Beachten Sie bitte, dass es einen serverseitigen Timeout gibt. Gegebenenfalls ist die Connection nach einer halben Tasse Kaffee nicht mehr verfügbar. Hier müssen Sie entweder serverseitig den Timeout erhöhen oder anderweitige Maßnahmen treffen.
Recordset erzeugen
Wie oben erwähnt, möchten wir ausschließlich auf der Basis von Recordsets arbeiten. Da bietet es sich an, genau wie bei den Connection-Objekten eine Funktion zu bauen, welche die gewünschten Recordsets zurückliefert. Die Funktion GetRecordset erwartet zwei Parameter:
- objConnection: Verweis auf eine geöffnete Verbindung, idealerweise über die Funktion GetConnection zu beziehen
- strSQL: SQL-String der Datenherkunft des Recordsets, gegebenenfalls in der Syntax des SQL-Servers; auch Stored Procedures oder View möglich
Die Funktion stellt einige Parameter für das zu erzeugende Recordset-Objekt ein, öffnet dieses und gibt einen Verweis darauf als Funktionswert zurück:
Public Function GetRecordset( _
objConnection As ADODB.Connection, _
strSQL As String) As ADODB.Recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = objConnection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.Source = strSQL
.Open , , , , adCmdText
Set GetRecordset = rst
End With
End Function
Formular mit Daten füllen
Kommen wir nun zum eigentlichen Thema: dem Füllen eines Formulars mit Daten einer MySQL-Datenbank auf einem Webserver. Den größten Teil der Arbeit haben wir bereits erledigt, fehlt nur noch das passende Formular. Dieses soll im ersten Beispiel die Daten der Tabelle tblKunden als einzelne Datensätze anzeigen. Die Beispieldatenbank enthält noch die für das Erstellen der MySQL-Datenbank verwendeten Tabellen inklusive Daten, sodass Sie diese zunächst zum Füllen des Formulars mit den benötigten Feldern verwenden können. Erstellen Sie dazu ein neues, leeres Formular, stellen Sie die Eigenschaft Datenherkunft auf die Tabelle tblKunden ein und ziehen Sie alle Felder der Tabelle aus der Feldliste in den Entwurf des Formulars (s. Abb. 2). Leeren Sie nun unbedingt die Eigenschaft Datenherkunft wieder - schließlich möchten wir nicht die Daten der lokalen Tabelle anzeigen, sondern die vom Webserver. Damit dies gelingt, legen Sie nun eine Ereignisprozedur für die Ereigniseigenschaft Beim Laden des Formulars an. Füllen Sie diese wie folgt:
Abb. 2: Entwurf des ersten Beispielformulars
Private Sub Form_Load()
Set Me.Recordset = GetRecordset( _
GetConnection, _
"SELECT * FROM tblKunden")
End Sub
Wenn Sie nun in die Formularansicht wechseln, erscheinen die gewünschten Datensätze. Sie können diese ändern und löschen und auch neue Datensätze anlegen.
1:n-Beziehung abbilden
1:n-Beziehungen lassen sich auf zwei Arten abbilden, und zwar aus der Sicht jeder einzelnen der beiden beteiligten Tabellen.
Im ersten Beispiel wollen wir einen Kunden zu einer Bestellung auswählen, was mit einem Kombinationsfeld geschieht. Im zweiten Beispiel sollen alle Bestellungen zu einem Kunden aufgelistet werden.
Kombinationsfelder füllen
Der interessante Teil beim Abbilden einer 1:n-Beziehung aus Sicht des Bestellungsdatensatzes ist das Kombinationsfeld zum Auswählen des Kunden.
Das Formular zu diesem Beispiel heißt frmBestellung und zeigt alle Felder der Tabelle tblBestellungen an. Die Felder fügen Sie am einfachsten wie oben beschrieben hinzu, indem Sie zunächst die lokale Tabelle tblBestellungen als Datenherkunft einstellen, die Felder ins Formular ziehen und die Datenherkunft wieder leeren. Danach legen Sie die folgende Prozedur für das Ereignis Beim Laden an:
Private Sub Form_Load()
Set Me.Recordset = GetRecordset( _
GetConnection, _
"SELECT * FROM tblBestellungen")
End Sub
Danach funktioniert das Formular gleich wie gewünscht - es zeigt sogar die Daten aus der Tabelle tblKunden im Kombinationsfeld zur Auswahl des Kunden an (s. Abb. 3).
Abb. 3: Das Kombinationsfeld ist bereits gefüllt, aber aus der falschen Quelle.
Allerdings stammen diese Daten natürlich nicht vom Webserver, sondern aus der lokalen Tabelle. Die notwendige Datensatzherkunft des Formulars wurde beim Hinzufügen der Felder auf Basis der lokalen Tabelle tblBestellungen eingetragen.
Wir wollen aber alle Daten von der Datenbank auf dem Webserver beziehen. Wechseln Sie also in die Entwurfsansicht des Formulars, markieren Sie das Kombinationsfeld zur Auswahl des Kunden und leeren Sie dessen Eigenschaft Datensatzherkunft.
Beim nächsten Wechseln in die Formularansicht ist das Kombinationsfeld leer, was auch logisch ist: Es enthält zwar den Wert des Feldes KundeID der dem Formular als Recordset zugewiesenen Tabelle tblBestellungen, diese wird jedoch nicht angezeigt, weil die Einstellungen der Eigenschaften Spaltenanzahl und Spaltenbreiten dafür ausgelegt sind, den Inhalt des zweiten Felds der Datensatzherkunft des Kombinationsfelds anzuzeigen - und da diese leer ist, zeigt das Formular eine leere Zeichenkette an. Da es nicht leer ist, können Sie durch das Einstellen der Eigenschaften Spaltenanzahl und Spaltenbreiten auf 1 und 3cm nachweisen.
Damit das Kombinationsfeld die auf dem Webserver gespeicherten Kundennamen zur Auswahl anbietet, müssen wir auch dessen Recordset-Eigenschaft mit einem Verweis auf das entsprechend Recordset füllen. Dazu erweitern Sie die Beim Laden-Ereignisprozedur von oben um die folgende Zeile:
Set Me!KundeID.Recordset = _
GetRecordset(GetConnection, _
"SELECT KundeID, Nachname " _
& "FROM tblKunden ORDER BY Nachname")
Dies zeigt nun die gewünschten Kunden an. Statt des Nachnamens sollen die Kundendaten in einem weiteren Schritt in der Form Nachname, Vorname angezeigt werden. Normalerweise würden wir die SQL-Abfrage dazu wie folgt ändern:
SELECT KundeID, Nachname & ', ' & Vorname AS Kunde FROM tblKunden ORDER BY Nachname
Dies funktioniert jedoch nicht: Das Kombinationsfeld zeigt zwar die richtige Anzahl Datensätze an, allerdings ohne Angabe des Kundennamens.
Der Grund ist einfach: Wenn wir Abfragen wie hier per ADO direkt an den SQL-Server absetzen, müssen diese im SQL-Dialekt des jeweiligen SQL-Servers, hier MySQL, formuliert werden. Und MySQL verwendet zum Konkatenieren von Feldern die CONCAT-Funktion. Die SQL-Abfrage muss also so aussehen:
SELECT KundeID, CONCAT(Nachname, ', ', Vorname) AS Kunde FROM tblKunden ORDER BY Nachname
Nun zeigt das Kombinationfeld die gewünschten Ausdrücke an und erlaubt auch deren Änderung. Alternativ können Sie auch eine PassThrough-Abfrage als Datensatzherkunft verwenden.
1:n-Beziehung mit Unterformular
Die umgekehrte Ansicht soll alle Bestellungen zu einem Kunden anzeigen. Dazu benötigen wir ein Listen-Steuerelement wie ein Listenfeld oder ein Unterformular in der Datenblatt- oder Endlosansicht. In diesem Fall verwenden wir ein Unterformular in der Datenblattansicht. Das Hauptformular soll alle Kunden-Daten anzeigen, weshalb Sie das Formular frmKunden aus dem ersten Beispiel weiterverwenden können.
Das Unterformular zum Anzeigen der Bestellungen soll sfmKunden heißen und erhält zunächst die Tabelle tblBestellungen als Datenherkunft, damit Sie die gebundenen Felder schnell zum Entwurf hinzufügen können. Danach leeren Sie die Datenherkunft, schließen das Formular und fügen es dem Formular frmKunden als Unterformular hinzu - am einfachsten gelingt dies, indem Sie frmKunden im Entwurf öffnen und das Formular sfmKunden aus dem Datenbankfenster in den Detailbereich ziehen. Das Ergebnis sieht dann wie in Abb. 4 aus. Ändern Sie den Steuerelementtyp des Felds KundeID in Textfeld - es dient später nur der Prüfung, ob das Feld den richtigen Standardwert aufweist, und kann anschließend entfernt werden.
Abb. 4: Kunden und ihre Bestellungen in Haupt- und Unterformular
Wenn Sie nun nur das Unterformularsteuerelement markieren und im Eigenschaftsfenster zur Registerseite Daten wechseln, finden Sie die beiden Eigenschaften Verknüpfen von und Verknüpfen nach leer vor. Normalerweise sollte Access automatisch die Beziehung zwischen den beiden Tabellen tblKunden und tblBestellungen erkennen und das Feld KundeID für beide Eigenschaften eintragen. Dies führt normalerweise dazu, dass das Unterformular automatisch nur die Bestellungen zu dem im Hauptformular angezeigten Kunden darstellt.
Hier geschieht das nicht, was leicht zu erklären ist: Beide Formulare besitzen schlicht und einfach keine Datenherkunft, da diese ja erst zur Laufzeit hinzugefügt wird. Somit kann Access auch keine Beziehung zwischen den im Haupt- und im Unterformular angezeigten Daten erkennen.
Damit wir überhaupt eine Chance haben, diese Synchronisierung zu erhalten, müssen wir zunächst das Unterformular mit Daten füllen. Dazu gibt es nun theoretisch zwei Möglichkeiten:
- Sie füllen das Unterformular mit allen Daten der Tabelle tblBestellungen und fügen anschließend die Werte für die Eigenschaften Verknüpfen von und Verknüpfen nach manuell hinzu. Access sollte dann eigentlich nur die betroffenen Bestellungen im Unterformular anzeigen.
- Sie füllen das Unterformular nur mit den Daten, die zum aktuell im Hauptformular angezeigten Kunden gehören.
Der Versuch der ersten Variante zeigt, dass sich Theorie und Praxis unterscheiden: Bei per Recordset-Eigenschaft zugewiesenen Datenherkünften wirken sich die Eigenschaften Verknüpfen von und Verknüpfen nach nicht aus. Widmen wir uns also gleich der zweiten Variante, die neben dem Füllen des Hauptformulars im Ereignis Beim Laden des Formulars noch eine weitere Ereignisprozedur einsetzt: Damit das Unterformular jeweils die passenden Bestelldatensätze zum Kunden des Hauptformulars liefert, sollte dieses bei jedem Wechsel des Datensatzes im Hauptformular aktualisiert werden. Die passende Ereigniseigenschaft heißt Beim Anzeigen und wird mit folgender Ereignisprozedur zum Leben erweckt:
Private Sub Form_Current()
Dim strSQL As String
Dim lngKundeID As Long
lngKundeID = Nz(Me!KundeID)
strSQL = "SELECT * FROM tblBestellungen " _
"WHERE KundeID = " & lngKundeID
Set Me!sfmKunden.Form.Recordset = _
GetRecordset(GetConnection, strSQL)
End Sub
Wir prüfen hier, ob das Feld KundeID einen Nullwert enthält, und ersetzen diesen für die Formulierung der Abfrage durch 0. Dies hat den Hintergrund, dass der Benutzer mit dem Formular auch neue Datensätze anlegen können soll.
Bei begrenzter Anzahl Datensätze im Unterformular kann man diese auch gleich komplett laden und beim Wechsel des Datensatzes im Hauptformular einen entsprechenden Filter setzen.
Ein neuer Datensatz besitzt allerdings zunächst keinen Wert für das Feld KundeID. Wenn nun einfach der stattdessen vorhandene Nullwert an die WHERE-Bedingung angehängt wird, erzeugt dies einen Fehler, weil MySQL (genauso wenig wie Access-SQL) die WHERE-Klausel der Abfrage SELECT * FROM tblBestellungen WHERE KundeID = nicht interpretieren kann und einen Fehler auslöst.
Es gibt noch ein weiteres kleines Problem, das durch die fehlende Synchronisierung über die Eigenschaften Verknüpfen von und Verknüpfen nach hervorgerufen wird: Das Fremdschlüsselfeld des Datensatzes im Unterformular (Verknüpfen von) wird nicht automatisch mit dem Primärschlüsselfeld des Datensatzes im Hauptformular (Verknüpfen nach) gefüllt. Sie müssen also auch noch dafür Sorge tragen, dass der Standardwert dieses Felds entsprechend vorbelegt wird. Da dieser Wert wiederum vom Datensatz im Hauptformular abhängt, muss er bei jedem Datensatzwechsel im Hauptformular aktualisiert werden. Dies erledigen wir durch Anhängen der folgenden Zeilen an die oben bereits beschriebene Prozedur Form_Current des Hauptformulars:
Me!sfmKunden.Form!KundeID.DefaultValue = lngKundeID
Dies reicht aus, um den Standardwert von KundeID für Bestellungen aller bereits vorhandenen Kunden einzustellen (s. Abb. 5). Leider geschieht dies scheinbar nicht, wenn Sie einen Kundendatensatz anlegen: Beim Form_Current-Ereignis, das beim Wechseln zum neuen Datensatz ausgelöst wird, ist KundeID im Hauptformular noch Null. Das Fremdschlüsselfeld im Unterformular wird zwar richtigerweise auch auf Null eingestellt, aber nach dem Erzeugen des Autowerts für KundeID nach Eingabe des ersten Zeichens in einem Feld des neuen Datensatzes bleibt der Standardwert für KundeID im Unterformular auf dem Wert Null stehen. Wenn Sie nun im Hauptformular zum vorherigen Datensatz und wieder zurück wechseln, zeigt das Unterformular zwar den richtigen Standardwert an, aber das ist natürlich nicht besonders ergonomisch. Der Benutzer möchte möglicherweise gleich nach dem Anlegen eines Datensatzes im Hauptformular Daten im Unterformular anlegen (okay, vielleicht nicht in diesem Beispiel, aber es ist ja auch nur ein Beispiel).
Abb. 5: Der Standardwert der Kunden-ID im Unterformular wird per Code eingestellt.
Also sorgen wir dafür, dass Access den Standardwert spätestens beim Eintritt in das Unterformular setzt, und zwar mit folgender Ereignisprozedur:
Private Sub sfmKunden_Enter()
Dim lngKundeID As Long
lngKundeID = Nz(Me!KundeID)
Me!sfmKunden.Form!KundeID.DefaultValue = lngKundeID
End Sub
Beachten Sie an dieser Stelle, dass die DefaultValue-Eigenschaft nur für Steuerelemente zur Verfügung steht und nicht für Feldverweise. In den meisten Fällen ändern Access-Entwickler die Steuerelementnamen von Feldern nach dem Hinzufügen über die Feldliste nicht explizit, was dazu führt, dass Steuerelement und Feld beziehungsweise Steuerelementinhalt die gleiche Bezeichnung tragen.
Wenn nun das Fremdschlüsselfeld, dessen Wert Sie per DefaultValue vorbelegen möchten, nur in der Datenherkunft des Formulars enthalten ist, aber nicht als Steuerelementinhalt eines Steuerelements vorliegt, erzeugt dies einen Fehler. Sie müssen das Fremdschlüsselfeld also auf jeden Fall zum Formular hinzufügen, was in Abb. 5 ja auch geschehen ist. Der Nachteil dabei ist, dass Sie dieses Feld eigentlich gar nicht benötigen - genau genommen soll es überhaupt nicht angezeigt werden, weil ja der Datensatz im Hauptformular bereits zeigt, wozu die Datensätze im Unterformular gehören.
Macht ja nichts, dann blenden wir es halt durch Einstellen der Eigenschaft Sichtbar auf den Wert Nein einfach aus. Nur: Dies wirkt sich zwar auf die Formular- und die Endlosansicht, nicht aber auf die Datenblattansicht im Unterformular aus. In der Datenblattansicht gelten andere Gesetze - es gibt nämlich einige weitere Eigenschaften für die dort angezeigten Steuerelemente. In diesem Fall erweitern wir die Beim Anzeigen-Ereignisprozedur um folgende Zeile, um die Spalte mit dem Feld KundeID auszublenden:
Me!sfmKunden.Form!KundeID.ColumnHidden = True
Dies können Sie übrigens nur wieder rückgängig machen, indem Sie ColumnHidden für das entsprechende Steuerelement wieder auf False einstellen. Mehr zu diesem Thema erfahren Sie im Beitrag Datenblattansicht aufgebohrt (Shortlink 464).
m:n-Beziehung mit Unterformular
Ein typischer Fall für die Anzeige einer m:n-Beziehung im Unterformular sind die Bestellpositionen einer Bestellung. Das Hauptformular zeigt dabei die Bestelldetails wie Kunde, Bestelldatum et cetera an, das Unterformular steuert die bestellten Artikel sowie Informationen wie Anzahl und Preis bei. Im Unterformular muss dabei zwangsläufig die Verknüpfungstabelle (in unserem Datenmodell tblPositionen) auftauchen.
Brauchen wir auch Daten aus der Tabelle tblArtikel? Nein, in diesem Fall nicht: Denn die zum Zeitpunkt der Bestellung wichtigen Artikelinformationen sollten in die Tabelle tblPositionen übertragen und nicht einfach nur verknüpft werden. Dies betrifft vor allem den Einzelpreis, der sich ja durchaus mit der Zeit ändern kann, was sich nicht auf bereits durchgeführte Bestellungen auswirken darf. Außerdem kann es auch passieren, dass der Einzelpreis eines Artikels bei einer Bestellung geändert werden soll. Wir brauchen also nur die Tabelle tblPositionen im Unterformular.
Fangen wir jedoch beim Hauptformular an: Hier können wir einfach mit dem bereits weiter oben erstellten Formular frmBestellungen fortfahren.
Wir brauchen nun ein Unterformular, das wie in Abb. 6 aussieht. Die Datenherkunft tblPositionen werfen wir nach dem Hinzufügen der Steuerelemente aus der Feldliste wieder heraus. Gleiches gilt für die Datensatzherkünfte der beiden Kombinationsfelder BestellungID und ArtikelID.
Abb. 6: Das Unterformular zur Anzeige der Positionen einer Bestellung
Außerdem können wir das Kombinationsfeld BestellungID in ein Textfeld ändern, damit wir gleich den noch zu erstellenden DefaultValue kontrollieren können - später wird dieses Feld natürlich ausgeblendet. Stellen Sie die Eigenschaft Standardansicht des angehenden Unterformulars auf Datenblatt ein und speichern Sie es unter dem Namen sfmBestellungen.
Öffnen Sie dann frmBestellungen in der Entwurfsansicht und fügen Sie sfmBestellungen als Unterformular hinzu. Da weder Haupt- noch Unterformular eine Datenherkunft besitzen, werden auch die beiden Eigenschaften Verknüpfen von und Verknüpfen nach des Unterformularsteuerelements automatisch gefüllt.
Schauen wir uns nun die notwendigen Prozeduren an, um Haupt- und Unterformular zu füllen. Den Start macht das Hauptformular mit mit der Ereignisprozedur aus Listing 1, die beim Laden des Formulars ausgelöst wird. Sie weist dem Recordset-Objekt zuerst eine Datensatzgruppe mit allen Datensätzen der Tabelle tblBestellungen zu und füllt das Kombinationsfeld KundeID mit allen vorhandenen Kunden. Gleiches geschieht mit dem Kombinationsfeld ArtikelID aus dem Unterformular: Dieses soll dem Benutzer alle vorhandenen Artikel zur Auswahl anbieten. Schließlich blendet die Prozedur die beiden Felder PositionID und BestellungID im Unterformular aus.
Private Sub Form_Load()
Set Me.Recordset = GetRecordset(GetConnection, "SELECT * FROM tblBestellungen")
Set Me!KundeID.Recordset = GetRecordset(GetConnection, _
"SELECT KundeID, CONCAT(Nachname, ‘, ‘, Vorname) AS Kunde FROM tblKunden ORDER BY Nachname")
With Me!sfmBestellungen.Form
Set !ArtikelID.Recordset = GetRecordset(GetConnection, _
"SELECT ArtikelID, Artikelname, Artikelpreis FROM tblArtikel")
!PositionID.ColumnHidden = True
!BestellungID.ColumnHidden = True
End With
End Sub
Die Prozedur aus Listing 2 wird jeweils beim Wechseln des Datensatzes im Hauptformular ausgelöst und sorgt dafür, dass die zur Bestellung passenden Positionen im Unterformular angezeigt werden. Dazu prüft die Prozedur zunächst die BestellungID auf den Wert Null, ersetzt diesen gegebenenfalls durch die Zahl 0 und schreibt das Ergebnis in die Long-Variable lngBestellungID.
Private Sub Form_Current()
Dim lngBestellungID As Long
lngBestellungID = Nz(Me!BestellungID)
With Me!sfmBestellungen.Form
Set .Recordset = GetRecordset(GetConnection, _
"SELECT * FROM tblPositionen WHERE BestellungID = " & lngBestellungID)
!BestellungID.DefaultValue = lngBestellungID
End With
End Sub
Diese dient als Vergleichswert für die WHERE-Klausel der Abfrage, welche die Positionen zur aktuellen Bestellung ermittelt und das resultierende Recordset der gleichnamigen Eigenschaft des Unterformulars zuweist. Damit das Unterformular neue Datensätze gleich der richtigen Bestellung zuordnen kann, stellt die Prozedur noch den Standardwert des Felds BestellungID auf den entsprechenden Wert der im Hauptformular angegebenen Bestellung ein.
Die Prozedur aus Listing 3 beugt dem Fall vor, dass der Benutzer eine neue Bestellung anlegt und dann die gewünschten Positionen hinzufügen möchte. Dafür gibt es zwei Varianten:
- Wenn der Benutzer bereits Daten im Hauptformular eingetragen hat, gibt es auch schon eine BestellungID. Diese wird dann beim Eintreten in das Unterformular als Standardwert für das Steuerelement BestellungID des Unterformulars angegeben.
- Falls der Benutzer im Hauptformular noch keine Daten eintragen hat, besitzt das Autowert-Feld BestellungID auch noch keinen Wert. In diesem Fall wird der Benutzer zunächst zum Hauptformular zurückgeschickt, um einen Bestellungs-Datensatz zu erzeugen.
Private Sub sfmBestellungen_Enter()
Dim lngBestellungID As Long
lngBestellungID = Nz(Me!BestellungID)
If Not lngBestellungID = 0 Then
Me!sfmBestellungen.Form!BestellungID.DefaultValue = lngBestellungID
Else
MsgBox !Bitte geben Sie zunächst die Bestellinformationen wie Kunde und Bestelldatum an.", _
vbOKOnly + vbExclamation, "Bestelldaten fehlen"
Me!Bestelldatum.SetFocus
End If
End Sub
Schließlich müssen wir uns bei Verwendung von ADO-Recordsets in Haupt- und Unterformular noch darum kümmern, dass beim Löschen des Datensatzes im Hauptformular auch die damit verknüpften Daten im Unterformular gelöscht werden - es sei denn, Sie haben auf dem MySQL-Server Löschweitergabe aktiviert (was nur bei MySQL 5-Tabellen im InnoDB-Format funktioniert). Falls nicht, erledigt die Prozedur aus Listing 4 dies für Sie.
Private Sub Form_Delete(Cancel As Integer)
GetConnection.Execute "DELETE FROM tblPositionen WHERE BestellungID = " & Me!BestellungID
End Sub
Behandlung von Abfragen über mehr als eine Tabelle
Bislang konnten wir alle gewünschten Beziehungen in entsprechenden Formularen abbilden. Es gibt allerdings auch Fälle, denen nur mit größerem Aufwand beizukommen ist. Dabei handelt es sich grundsätzlich um Formulare, die Daten aus mehr als einer Tabelle anzeigen sollen und deren Datenherkunft entsprechend aus einer Abfrage mit mehreren per JOIN verknüpften Tabellen besteht.
Das Problem ist, dass Access im Gegensatz zu anderen Datenbanksystemen sehr großzügig bezüglich der Aktualisierung von Daten aus verknüpften Tabellen ist. Beim Füllen des Recordsets eines Formulars mit zwei oder mehr verknüpften Tabellen aus einer MySQL-Datenbank via ADODB sieht das zum Beispiel anders aus: Die Daten werden dann zwar angezeigt, können allerdings nicht bearbeitet werden. Wenn man dies von vornherein weiß, kann man das Design seiner Benutzeroberfläche auch mit dieser Einschränkung in den meisten Fällen ausreichend gut umsetzen.
Beim Umstellen bestehender Datenbanken auf ADODB-/Recordset-Technik kann es hier und da schon einmal zu Problemen kommen, beispielsweise wenn Felder aus verknüpften Tabellen als WHERE-Bedingung oder als Sortierung per ORDER BY-Klausel verwendet werden; noch schwieriger wird es, wenn Daten aus mehr als einer Tabelle in einem Formular bearbeitet werden sollen.
Sortier- und sonstige Kriterien aus verknüpften Tabellen
Wenn Sie Daten einer Tabelle anzeigen möchten und nach Feldern aus verknüpften Tabellen sortieren oder filtern möchten, dürfen diese nicht in der Ergebnismenge der Abfrage enthalten sein. Die folgende Abfrage würde beispielsweise ein Aktualisieren der angezeigten Daten verhindern:
SELECT tblBestellungen.BestellungID,
tblBestellungen.Bestelldatum, tblKunden.Vorname, tblKunden.Nachname
FROM tblKunden
INNER JOIN tblBestellungen
ON tblKunden.KundeID = tblBestellungen.KundeID
WHERE tblKunden.Vorname LIKE 'Andre';
Wenn Sie die Ergebnismenge wie folgt auf die Felder einer einzigen Tabelle beschränken, können Sie die Daten bearbeiten und löschen und auch neue Datensätze anlegen:
SELECT tblBestellungen.BestellungID,
tblBestellungen.Bestelldatum
FROM tblKunden ...
Sollten Daten ohnehin nur der Anzeige dienen wie etwa die in Recordsets von Kombinations- oder Listenfeldern, können Sie sich beim Zusammenstellen der zugrunde liegenden Abfragen austoben.
Wir beschränken uns an dieser Stelle darauf, auf potenzielle Probleme hinzuweisen. Theoretisch ist es immer möglich, Tabellen, Abfragen und Formulare so zu gestalten, dass Inhalte aus nicht mehr als einer Tabelle dargestellt beziehungsweise bearbeitet werden müssen.
ADODB-Recordsets in Berichten
Wer Formulare und Steuerelemente mit ADO-Recordsets befüllt, kommt früher oder später natürlich auf die Idee, dies mit einem Bericht durchzuführen. Leider können Berichte auch mit Access 2007 noch immer nicht mit benutzerdefinierten Recordsets gefüllt werden. Als Alternative empfiehlt es sich, eine PassThrough-Abfrage zu verwenden, die bezüglich der Geschwindigkeit wohl eine ähnliche Leistung erwarten lässt.
Zusammenfassung und Ausblick
Wenn Sie mit einem Access-Frontend auf eine Datenbank zugreifen möchten, die auf einem Webserver liegt (was aktuell meist ein MySQL-Server sein dürfte) und sich über die mäßige Geschwindigkeit bei der Verwendung verknüpfter ODBC-Tabellen ärgern, finden Sie in diesem Beitrag möglicherweise die Lösung: Am Beispiel einer im Einsatz befindlichen Anwendung konnte der Autor deutliche Performance-Steigerungen feststellen, welche die Umstellung der kompletten Anwendung auf die hier vorgestellte Technik nach sich zog. Gleichwohl muss klar sein, dass der Programmieraufwand etwas höher ist als bei der Verwendung eingebundener ODBC-Tabellen, was ja im Prinzip mit dem für den Einsatz lokaler Tabellen nötigen Aufwand vergleichbar ist. Außerdem sind hier und da Umstellungen erforderlich, da Datenherkünfte mit Daten aus mehreren verknüpften Tabellen unter Umständen nicht aktualisiert werden können. |
