Tipps & Tricks: Access-VBA
Auf dieser Seite finden Sie 17 Tipps & Tricks für Access-VBA. Soweit keine Einschränkungen dazu genannt werden, gelten diese Tipps & Tricks für alle Versionen von Access. Tipps zu VBA, die für alle Office-Programme gelten, finden Sie bei VBA allgemein.
Wenn der Datentyp DAO.Recordset oder ähnliches heißt, muss im VBA-Editor-Menü Extras Verweise die Bibliothek "Microsoft Data Access Objects" (nämlich "DAO", meistens in Version 3.6) angeklickt sein. Der Datentyp ADO.Recordset oder ähnliche erfordert dort "Microsoft ActiveX Data Objects" (nämlich "ADO" in Version 2.1). Auch wenn es technisch möglich ist, sollten Sie je Datenbank nur einen der beiden Verweise aktivieren, damit Sie nicht die oft gleichnamigen Objekte verwechseln. Seit Access 2013 heißt dieser Verweis "Microsoft Office 15.0 database engine Object", wird aber in der Programmierung wie früher als "DAO" angesprochen.
Wenn in den folgenden Erläuterungen Texte wie MsgBox formatiert sind, handelt es sich um konkret einzugebende Inhalte wie VBA-Code oder die Eingabe von Werten. Menüs wie Datei Speichern sind wie hier zu sehen formatiert. Schaltflächen oder Registerkarten auf Dialogen werden wie Menüs behandelt.
Alle Tipps sind nach bestem Wissen geprüft, aber selbstverständlich ohne Gewähr. Sollten Sie doch einen Fehler darin entdecken, würden wir uns freuen, wenn Sie uns per E-Mail Bescheid sagen.
Access-Version ermitteln ID 313
Die SysCmd-Funktion bietet auch die einfachste Möglichkeit, die Access-Version zu ermitteln:
Sub VersionsTest()
Dabei wird nicht die offizielle Version wie beispielsweise "Access 2002" angezeigt, sondern die interne Nummerierung (hier also "10.0").
Sobald eine Aktionsabfrage ausgeführt wird, warnt Access den/die Anwender/in erstens durch eine grundsätzliche Meldung, dass diese Abfrage Daten verändern werde und zweitens mit einer Meldung, wieviele Datensätze betroffen sein werden. Im Verlauf eines fertig programmierten Ablaufs soll ein/e Benutzer/in diese Hinweise natürlich nicht sehen.
Weit verbreitet ist daher die Technik, mit folgendem Code die Warnungen in Access generell auszuschalten:
DoCmd.RunSQL "DELETE * FROM Tabellenname"
DoCmd.SetWarnings True
Wenn die Ausführung allerdings durch einen Laufzeitfehler unterbrochen wird, bleiben die Warnungen aus - und zwar nicht nur für diese Datenbank, sondern dauerhaft für dieses Access auf dem Rechner! Das trifft ebenfalls Entwürfe von Tabellen, Abfragen, Formularen o.ä., deren ungespeicherte Änderungen dann ohne Rückfrage immer gespeichert werden. Das ist ein völlig unnötiges Risiko.
Die Warnungen werden dabei von der Access-Oberfläche erzeugt, DoCmd.RunSQL simuliert nämlich eine/n Anwender/in, der/die via Oberfläche eine Aktionsabfrage startet und also gewarnt werden muss. Probieren Sie stattdessen folgenden Code:
Dabei wird die Ausführung der Aktionsabfrage direkt an die JetEngine delegiert, welche die Arbeit auch im ersten Fall im Hintergrund gemacht hat. Diesmal aber ist die Access-Oberfläche nicht beteiligt, daher entfallen die Meldungen.
Die Warnmeldungen von Access sind genauso lästig, wenn Sie nicht einen SQL-Befehl, sondern eine bereits gespeicherte Abfrage ausführen wollen. Die oft übliche Version lautet:
DoCmd.OpenQuery "NameDerAktionsabfrage"
DoCmd.SetWarnings True
Die direkte Ausführung via JetEngine ohne Beteiligung der Access-Oberfläche gelingt mit:
Das ist nicht nur kürzer als die dreizeilige Version mit DoCmd.SetWarnings, sondern auch unproblematischer, weil die System-Meldungen dabei grundsätzlich eingeschaltet bleiben.
Alle Felder einer Tabelle ermitteln mit VBA ID 314 Top-Tipp!
Geben Sie den folgenden Beispiel-Code in einem beliebigen Access-Modul ein, um die Namen aller enthaltenen Felder zu sehen:
Dim fldDieses as DAO.Field
Set tblDiese = CurrentDB.OpenRecordset("MeineTabelle")
For Each fldDieses in tblDiese.Fields
(Siehe dazu auch den Tipp Alle Felder einer Tabelle ermitteln im Formular)
Sehr verführerisch in der Programmierung ist eine typische VBA-Auflistung wie die Forms-Auflistung. Mit dieser können Sie scheinbar alle Formulare anzeigen, wie der folgende Code zeigt:
Dim dbsDiese as Database
Set dbsDiese = CurrentDB()
For Each frmDiese in dbsDiese.Forms
Bei genauerem Hingucken werden Sie aber feststellen, dass es tatsächlich nicht alle, sondern nur die geöffneten Formulare sind, die darin aufgelistet sind. Das ist durchaus so gewollt und dokumentiert, kommt aber für viele oftmals überraschend.
Was machen Sie nun, wenn Sie wirklich eine Liste aller in der Datenbank enthaltenen, aber nicht unbedingt geöffneten Formulare benötigen? Ab Access 2000 gibt es nicht nur das CurrentDB-Objekt, welches nur die Datenobjekte enthält, sondern auch CurrentProject mit den übrigen Objekten der Bedienungsoberfläche.
Dort heißt die entsprechende Auflistung AllForms und kann wie folgt benutzt werden:
For Each frmDiese in CurrentProject.AllForms
Dann sehen Sie tatsächlich alle gespeicherten Formulare und nicht nur die geöffneten.
Geben Sie den folgenden Beispiel-Code in einem beliebigen Access-Modul ein, um die Namen aller enthaltenen Tabellen zu sehen:
Dim tblDiese as DAO.TableDef
Set dbsDiese = CurrentDB()
For Each tblDiese in dbsDiese.TableDefs
Wie Sie bemerken werden, sind wirklich alle Tabellen in der Auflistung enthalten, also auch die mit 'MSys...' anfangenden Systemtabellen, die normalerweise ausgeblendet sind.
Um diese auch noch auszublenden, können Sie mit dem 'Like'-Operator die Namen überprüfen. Dabei müssen Sie bedenken, dass es nicht nur die immer vorhandenen 'MSys...'-Tabellen, sondern möglicherweise auch 'USys...'-Tabellen (z.B. in AddIns) gibt. Der obige Code ändert sich dann wie folgt:
For Each tblDiese in dbsDiese.TableDefs
'...wie bisher
(Siehe dazu auch den Tipp Alle Felder einer Tabelle ermitteln im Formular)
Benutzer- und Computername ID 309
Um den Benutzer einer Datenbank eindeutig zu identifizieren, können Sie natürlich auf das (nur bis Version 2003!) eingebaute Sicherheitssystem der DB-Anmeldung zurückgreifen. Müssen sie aber nicht. Schließlich hat der Benutzer sich bereits identifiziert, als er den Rechner eingeschaltet hat. Diese beiden Informationen (Benutzer- und Computername) können Sie direkt abfragen:
Dateinamen per Dialog auswählen ID 305 Aktuell
Um eine Datei via VBA-Code auszuwählen, greifen Sie natürlich über den eingebauten Windows-DateiAuswahl-Dialog zu. Dazu müssen Sie zuerst mit Extras Verweise die Bibliothek 'Microsoft Office XX.0 Object Library' (XX ist die Versionsnummer) einbinden. Dann können Sie eine Datei so auswählen:
Set dlgFileOpen = Application.FileDialog(msoFileDialogFilePicker)
dlgFileOpen.AllowMultiSelect = False
If dlgFileOpen.Show() Then
Wollen Sie mehrere Dateien auswählen, setzen Sie dlgFileOpen.AllowMultiSelect = True und werten mit einer For/Next-Schleife die Inhalte des Arrays SelectedItems aus.
Access kennt keine Eigenschaft, um den angezeigten Cursor für ein Formularfeld zu ändern. Trotzdem können Sie den Mauszeiger mittels API-Funktionen ziemlich einfach nach Belieben ändern. Die beiden benötigten Funktionen sind LoadCursorFromFileA und SetCursor. Schreiben Sie in einem Modul folgenden Code:
Sub MausHand()
Dim lngCursor As Long
strPfadCursor = CurrentProject.Path & "Hand.cur"
'strPfadCursor = CurrentProject.Path & "Banana.ani"
lngCursor = LoadCursorFromFileA(strPfadCursor)
If lngCursor = 0 Then
Jetzt ist die Prozedur fertig und kann aufgerufen werden. Dazu können Sie in einem beliebigen Formular-Modul die MouseMove-Ereignisse von dessen Textboxen oder Schaltflächen nutzen:
MausHand
Private Sub edtDatum_MouseMove(Button As Integer, Shift As Integer, _
MausHand
Private Sub edtText_MouseMove(Button As Integer, Shift As Integer, _
MausHand
Bei der Kombination Bezeichnungsfeld/Textbox ist auch über dem Bezeichnungsfeld der geänderte Cursor zu sehen. Um das zu vermeiden, müssen Sie das Bezeichnungsfeld von der Textbox trennen: markieren Sie es im Entwurf, schneiden es in die Zwischenablage aus und fügen es direkt wieder ein.
Wenn eine Datenbank sehr groß wird, kann es hilfreich sein, zu sehen, in welchen Tabellen das Volumen versteckt ist. Sowohl die Menge der Daten als auch der evtl. zu groß gewählte Datentyp können das verursachen. Das zu ermitteln ist jedoch mehr als mühsam, weil es sowohl im Tabellenentwurf als auch in der angezeigten Tabelle enthalten ist.
Der folgende Code schreibt für alle Tabellen und alle Felder deren Feldgröße, mittlere und maximale Belegung (nur für Textfelder wichtig) und die Anzahl der Datensätze automatisch in eine Textdatei, die bei Bedarf direkt in Excel importiert werden kann:
Dim rcsTabelle As DAO.Recordset
Dim fldDieses As DAO.Field
Dim varMW As Variant
Dim varMax As Variant
Dim varAnzDS As Variant
Dim lngKanal As Long
lngKanal = FreeFile()
Open CurrentProject.Path & "TabellenGroessen.txt" For Output As lngKanal
Print #lngKanal, "Tabellenname" & vbTab & "Feldname" & vbTab & _
For Each tblDiese In CurrentDb.TableDefs
Print #lngKanal, tblDiese.Name
For Each fldDieses In tblDiese.Fields
tblDiese.Name & "];", dbOpenDynaset)
rcsTabelle.Close
Set rcsTabelle = CurrentDb.OpenRecordset( _
tblDiese.Name & "];", dbOpenDynaset)
rcsTabelle.Close
Print #lngKanal, tblDiese.Name & vbTab & fldDieses.Name & vbTab & _
Set rcsTabelle = CurrentDb.OpenRecordset(tblDiese.Name, dbOpenTable)
varAnzDS = rcsTabelle.RecordCount
rcsTabelle.Close
Print #lngKanal, varAnzDS & vbTab & "Datensätze"
Print #lngKanal, ""
Print #lngKanal, ""
Print #lngKanal, ""
Close #lngKanal
Wegen des CurrentProject-Objects läuft dieser Code nur ab Access 2000, für ältere Versionen müssen Sie stattdessen einen konkreten Pfad einsetzen oder beispielsweise die CurDir()-Funktion nutzen.
Läuft der Code in einer Runtime-Umgebung? ID 306 Aktuell
Oft ist es notwendig zu wissen, ob Access in der Vollversion läuft oder nur als Runtime ausgeführt wird. Die Runtime-Umgebung kann beispielsweise ein Formular nicht in den Filtermodus umschaltet und enthält kein Datenbank-Fenster. Sie können das mit
herausbekommen. Die Funktion liefert den Werte True zurück, wenn Access nur als Runtime installiert ist.
Mehr als 24 Stunden anzeigen ID 307
Access bietet nach wie vor keine Möglichkeit, Zeitangaben über einen Tag hinaus anzuzeigen, denn alle Zeitformate kürzen die Anzeige auf maximal 24 Stunden. In Excel gäbe es eine Format-Anweisung [hh]:mm:ss (Achtung, bei Excel sind m die Minuten, bei Access ist es n!), die wenigstens über 24 Stunden hinaus anzeigt, aber Access kann das ärgerlicherweise immer noch nicht.
Sie müssen sich also mit einer Funktion behelfen, die aus dem Datum/Zeit-Datentyp eine Zeichenkette macht. Das bedeutet, dass Sie mit dem Ergebnis nicht mehr weiterrechnen können und es in einem berechneten Feld (siehe auch Tipp "Berechnete Felder in Abfragen") stehen muß. Wenn Sie mit dieser Einschränkung leben können, können Sie die folgende Funktion einsetzen:
Case #12:00:00 AM# 'nur, falls Sie Mitternacht als 24:00:00 anzeigen wollen
Um das direkt in einem Modul zu testen, schreiben Sie die folgende Prozedur:
Debug.Print meinDatumsFormat(#12:00:00 AM#)
Debug.Print meinDatumsFormat(#12:00:01 AM#)
Debug.Print meinDatumsFormat(3.99)
Die Eingabe der verschiedenen Beispielwerte führt zur Anzeige dieser Daten im Direktfenster (welches Sie mit Strg+G anzeigen können):
Eingabe: 00:00:00 = 30.12.1899 00:00:00= 24:00:00
Eingabe: 00:00:01 = 30.12.1899 00:00:01= 00:00:01
Eingabe: 02.01.1900 23:45:36 = 02.01.1900 23:45:36= 71:45:36
Beachten Sie insbesondere die letzte Zeile, die zeigt, dass auch summierte Datum/Zeit-Werte über einen Tag hinaus funktionieren, selbst wenn sie als Dezimalzahl eingegeben werden.
Bevor Sie via VBA auf die Daten aus einem Formular zugreifen, sollten Sie sicherstellen, dass es überhaupt geöffnet ist. Dann lässt sich im laufenden Code so prüfen, ob das Formular geöffnet ist:
Für die Funktion IstFormularOffen gibt es verschiedene Methoden, von denen meistens die folgende gewählt wird. Dabei wird die Namensliste aller geöffneten Formulare mit dem gesuchten Formularnamen verglichen:
IstFormularOffen = False
For i = 0 To Forms.Count - 1
Exit For
Das lässt sich aber viel besser und vor allem kürzer programmieren, denn Access stellt mit der SysCmd-Funktion eine Möglichkeit zur Verfügung, die außerdem auch den Status des Formulars zurückgibt. Die ist allerdings in der Hilfe sehr gut versteckt, so dass sie kaum bekannt ist:
Mit der Konstanten acObjStateOpen lässt sich nicht nur feststellen, dass das Formular geöffnet ist, sondern auch, ob es ungespeicherte Daten enthält (acObjStateDirty) oder ob es neu ist (acObjStateNew). Zudem erlaubt der zweite Parameter (hier mit acForm) statt Formularen auch alle anderen Objekte der Datenbank (z.B. acReport für Berichte) ebenfalls auf ihren Status zu prüfen.
Sobald Sie in VBA einen SQL-Befehl mit Datumsanteil erstellen, haben Sie Ärger mit der automatisch deutschen Datumsformatierung. Typischerweise steht in Ihrem Code eine Zeile wie die folgende:
strSQL = "SELECT * FROM [Tabelle1] WHERE [Datum]<=" & Date
MsgBox strSQL 'nur zum Prüfen
Mit der MsgBox sehen Sie, dass als Ergebnis SELECT * FROM [Tabelle1] WHERE [Datum]<=24.12.2007 ermittelt wird. In einem SQL-Befehl ist aber amerikanische Syntax und damit ein anderes Datumsformat erforderlich. Während eines manuellen Abfrage-Entwurfs übernimmt der Access-Abfrageeditor die automatische Umwandlung, aber hier nicht.
Da dies Problem im Code sehr häufig vorkommt, sollten Sie sich direkt eine Funktion SQLDatum() schreiben, welche das Datum im gewünschten Format liefert:
Damit trotz deutscher Ländereinstellung in der Systemsteuerung die Schrägstriche im Datum erscheinen, müssen diese mit / als so genanntes "Literal" erzwungen werden. Auch die Reihenfolge ist ja verändert, daher steht zuerst der Monat und dann der Tag. Der Bequemlichkeit halber sind die für ein SQL-Datum vorgeschriebenen Doppelkreuze (#) auch schon angefügt. Damit kann der Code oben so verbessert werden:
Damit liefert die MsgBox nun das korrekte Ergebnis SELECT * FROM [Tabelle1] WHERE [Datum]<=#12/24/2007#. Karl Donaubauer zeigt auf seiner FAQ-Seite alternativ noch eine zweite Version für ein korrektes (ISO-)Datum.
Für einen einfachen Überblick über die Datenbank können Sie Access-VBA sehr einfach ermitteln lassen, wie viele Objekte in welchem Bereich enthalten sind:
Set cts = CurrentDb.Containers
MsgBox "Es gibt " & vbCrLf & _
cts("Forms").Documents.Count & " Formulare," & vbCrLf & _
cts("Reports").Documents.Count & " Berichte," & vbCrLf & _
cts("Scripts").Documents.Count & " Makros," & vbCrLf & _
cts("Modules").Documents.Count & " Module" & vbCrLf
Die Zuweisung von CurrentDb.Containers an die Variable cts dient nur der kürzeren Schreibweise. Allerdings werden mit dieser Technik Tabellen und Abfragen gemeinsam gezählt. Seit Access 2000 steht mit dem CurrentProject-Objekt eine Alternative zur Verfügung:
CurrentDb.QueryDefs.Count & " Abfragen," & vbCrLf & _
CurrentProject.AllForms.Count & " Formulare," & vbCrLf & _
CurrentProject.AllReports.Count & " Berichte," & vbCrLf & _
CurrentProject.AllMacros.Count & " Makros," & vbCrLf & _
CurrentProject.AllModules.Count & " Module" & vbCrLf
Beachten Sie dazu auch die übrigen Tipps zu den Themen "Alle ... der Datenbank ermitteln mit VBA" auf dieser Seite.
Zeilen fortlaufend nummerieren mit VBA-Funktion? ID 321 Top-Tipp!
Nachdem Sie eventuell im Tipp "Zeilen fortlaufend nummerieren mit SQL" schon gelesen haben, dass bei vielen Datensätzen ganz erhebliche Laufzeiten entstehen, liegt die Versuchung nahe, dieses Problem per VBA-Function zu lösen. Tatsächlich gibt es dazu eine Lösung:
Dim mlngNr As Long
Function StartNr() As Long
mlngNr = 0
Function LfdNr(varEgal As Variant) As Long
LfdNr = mlngNr
mlngNr = mlngNr + 1
Dabei machen sich die Funktionen jeweils zunutze, dass sie ohne Parameter nur einmal pro Abfrage, mit Parameter aber für jede Zeile neu aufgerufen werden. Über diesen Trick können Sie den Startwert der Modul-öffentlichen Long-Variablen mlngNr vorgeben. Tatsächlich wird jede Zeile mit einer laufenden Nummer versehen, wenn Sie die folgende SQL-Anweisung ausführen:
FROM tblAdressen
ORDER BY tblAdressen.KundenNr;
Die Anzeige der ersten Datenseite ist sogar schon nach 1 Seite fertig, es ist also um ein Vielfaches schneller als die SQL-Variante. Das sieht auch noch gut aus, solange Sie vorwärts durch die Datensätze blättern. Beim Hin- und Herblättern werden Sie aber feststellen, dass Access bei jedem Anzeigen eines Datensatzes weiterzählt, also sogar beim Wechsel zwischen Access und einem anderen Programm.
Die Zeilen sind daher zwar innerhalb einer Seite fortlaufend nummeriert, aber beim Wechsel zu anderen Datensätzen ist das Ergebnis eher unkalkulierbar. Als Lösungen bleiben daher für die meisten Anlässe die langsame und sichere SQL-Variante oder die schnelle und auf Berichte beschränkte "LaufendeSumme"-Variante aus den anderen Tipps.