Zum Inhaltsverzeichnis

Zugriff auf eine Access Datenbank

Allgemein

Um auf eine Access Datenbank mit VBA zuzugreifen, müssen Sie eine zusätzliche Bibliothek in Ihr Projekt einbinden, die Ihnen die entsprechenden Datenbankobjekte zur Verfügung stellt.

Je nach Office-Version gibt es hier historisch unterschiedliche Bibliotheken. Seit der Office-Version 2007 wird die Bibliothek "Microsoft Office [Versionsnummer] Access database engine Object Library" verwendet. Um die Bibliothek einzubinden, öffnen Sie im VBA-Editor mit Extras - Verweise folgenden Dialog:

Scrollen Sie nach unten und aktivieren Sie das Häkchen bei dem entsprechenden Eintrag und schließen Sie den Dialog mit Klick auf OK.

Die angeführten Codebeispiele beziehen sich auf folgende Access Datenbank. Laden Sie die zip-Datei herunter und extrahieren Sie die Datenbank in ein beliebiges Verzeichnis.

Database-Objekt

Um auf die Daten Ihrer Access Datenbank zugreifen zu können, müssen wir ein gültiges Datenbankobjekt erstellen. Um Eindeutigkeit zu erlangen, werden wir die Datenbankobjekte mit Ihrem Namensraum (DAO) verwenden.

Im folgenden Beispiel wird ein Datenbankobjekt erzeugt und der Name und die Versionsnummer im Direktfenster ausgegeben.

'Erzeugen eines Datenbankobjekts  
Dim db As DAO.Database
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Debug.Print db.Name
Debug.Print db.Version
db.Close

Im Ausgabefenster sollte der volle Pfad der Datenbank und die Versionsnummer ausgegeben werden. Da die Datenbank als Datenbank der Office-Version 2007 erstellt wurde, ist die Versionsnummer 12.0.

Beachten Sie, dass nach dem Schließen des Datenbankobjektes ein erneuter Zugriff nur nach neuerlichem Aufruf von OpenDatabase(....) möglich ist.

Verketten von SQL-Anweisungen

Beim Arbeiten mit Daten werden Sie als Teil Ihres Codes auch SQL-Anweisungen schreiben und der Datenbank übermitteln. Enthält Ihr SQL-Statement konstante und dynamische Teile, so müssen diese verkettet (konkateniert) werden, um ein gültiges SQL-Statement zu erhalten.

Im folgenden erzeugen wir ein SQL-Statment, das alle Personen mit einem Gewicht kleiner der Benutzereingabe liefert.

'SQL  
Dim eingabe As Integer
Dim sql As String
eingabe = InputBox("Gewicht:")
sql = "Select * From Personen Where Gewicht < " & eingabe & ";"
Debug.Print sql

Nachfolgend ein SQL-Statement, das alle Personen mit einem Nachnamen gleich der Benutzereingabe liefert.

'SQL  
Dim eingabe As String
Dim sql As String
eingabe = InputBox("Nachname:")
sql = "Select * From Personen Where Nachname = '" & eingabe & "';"
Debug.Print sql

Beachten Sie, dass der gesuchte Nachname im SQL-Statement unter Anführungszeichen stehen muss. Deshalb sind als Teil des konstanten Textes auch vor und nach dem gesuchten Nachnamen einfache Hochkommas eingefügt. Sie können auch doppelte Anführungszeichen verwenden, diese müssen allerdings doppelt angeführt werden, damit VBA diese nicht als Ende der Zeichenkette ansieht.

'SQL: doppelte Anführungszeichen  
Dim eingabe As String
Dim sql As String
eingabe = InputBox("Nachname:")
sql = "Select * From Personen Where Nachname =""" & eingabe & """;"
Debug.Print sql

Es ist offensichtlich, dass aus Gründen der Lesbarkeit die Variante mit den einfachen Hochkommas vorzuziehen ist.

Access-SQL benötigt die Angabe von Datumswerten in der amerikanischen Schreibweise (Monat/Tag/Jahr) und in Rauten (#). Da Access-SQL die Funktion CDate zur Konvertierung von Text in ein Datum kennt, können Sie das Datum als Text angeben.

'SQL: Datumsangaben  
Dim sql As String
sql = "Select * From Personen Where Geburtsdatum < CDate('28.12.2003');"

Jetzt ein Beispiel, in dem das Datum als Text vom Benutzer eingelesen wird:

'SQL: Datumsangaben  
Dim eingabe As Integer
Dim sql As String
eingabe = InputBox("Geburtsdatum:")
sql = "Select * From Personen Where Geburtsdatum < CDate('" & eingabe & "');"
Debug.Print sql

Recordset-Objekt

Daten lesen

Um lesend oder schreibend auf die Daten einer Datenbank zuzugreifen, verwenden Sie das Recordset-Objekt. Als Datenquelle für ein Recordset können Sie eine Tabellenbezeichnung, eine Abfragebezeichnung oder eine SQL-Anweisung angeben. Beachten Sie, dass eine Abfragebezeichnung nur dann funktioniert, wenn die Abfrage keine Parameter (Kriterien in eckigen Klammern) enthält.

Enthält das Recordset Daten, so steht der interne Zeiger beim ersten Datensatz. Um die Inhalte einer Spalte auszugeben verwenden Sie entweder ein Ausrufungszeichen als Operator gefolgt von der Spaltenbezeichnung oder Sie rufen mit dem Punktoperator die Auflistung Fields auf und geben die Spaltenbezeichnung oder den 0-basierten Spaltenindex an.

Die folgenden drei Beispiele zeigen die unterschiedlichen Varianten. In allen drei Beispielen wird der Inhalt der Spalte PersonNr im Direktfenster ausgegeben und das Recordset geschlossen.

'Tabelle liefert die Daten  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Personen")
Debug.Print rs!PersonNr
Debug.Print rs.Fields("PersonNr")
Debug.Print rs.Fields(0)
rs.Close
db.Close

'Gespeicherte Abfrage liefert die Daten  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("PersonenAufsteigend")
Debug.Print rs!PersonNr
Debug.Print rs.Fields("PersonNr")
Debug.Print rs.Fields(0)
rs.Close
db.Close

'SQL-Anweisung liefert die Daten  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Select * From Personen Where Nachname = 'Doe';")
Debug.Print rs!PersonNr
Debug.Print rs.Fields("PersonNr")
Debug.Print rs.Fields(0)
rs.Close
db.Close

Beachten Sie im SQL-Statement, dass der Vergleichsausdruck Doe unter einfachen Anführungszeichen steht, da es sich um einen Zeichenkettenvergleich handelt.

Enthält das Recordset keine Daten, steht der interne Zeiger auf der Position EOF. Dies ist die Abkürzung für End Of File.

Im folgenden Beispiel prüfen wir, ob das Recordset Daten enthält.

'Prüfen, ob Datensätze vorhanden  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Select * From Personen Where Nachname = 'Huber';")
If rs.EOF Then
  Debug.Print "Keine Daten"
Else
  Debug.Print rs!Nachname
End If
rs.Close
db.Close

Um den internen Zeiger vom aktuellen Datensatz zum nächsten zu setzen, rufen Sie die Methode MoveNext des Recordset auf. Ist der interne Zeiger auf dem letzten Datensatz, erreichen Sie durch neuerlichen Aufruf von MoveNext die Position EOF.

Im folgenden Beispiel gehen wir in einer Schleife über alle Datensätze und geben den Nachnamen aus.

'Ausgabe aller Nachnamen  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Personen")
Do Until rs.EOF
  Debug.Print rs!Nachname
  rs.MoveNext
Loop
rs.Close
db.Close

Das Recordset stellt eine Fields-Auflistung zur Verfügung. Sie können mit einer For-Each-Schleife bei jedem Datensatz über alle Spalten gehen und beispielsweise den Namen und den Wert der entsprechenden Spalte ausgeben.

'Ausgabe der Namen und Werte aller Spalten jedes Datensatzes  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fd As DAO.Field
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Personen")
Do Until rs.EOF
  For Each fd In rs.Fields
    Debug.Print fd.Name & ": " & fd.Value
  Next
  rs.MoveNext
Loop
rs.Close
db.Close

Um die Inhalte eines Recordsets in einem Excel Tabellenblatt auszugeben, empfiehlt es sich, die Cells-Anweisung zu verwenden, um mit Hilfe einer Zeilenindex-Variable die Daten der Reihe nach auszugeben.

'Ausgabe aller Datensätze in Excel
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim zeilenIndex As Integer
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Personen")
zeilenIndex = 1
Do Until rs.EOF
  Cells(zeilenIndex, 1) = rs!PersonNr
  Cells(zeilenIndex, 2) = rs!Nachname
  Cells(zeilenIndex, 3) = rs!Vorname
  Cells(zeilenIndex, 4).NumberFormat = "dd.mm.yyyy"
  Cells(zeilenIndex, 4) = rs!Geburtsdatum
  Cells(zeilenIndex, 5).NumberFormat = "0.00"
  Cells(zeilenIndex, 5) = rs!Gewicht
  zeilenIndex = zeilenIndex + 1
  rs.MoveNext
Loop
rs.Close
db.Close

Beachten Sie, dass auch für die Ausgabe des Geburtsdatums und des Gewichts ein Zellformat definiert wurde.

Obiges Beispiel bringt einiges an Änderungsaufwand mit sich, wenn Sie nachträglich die Reihenfolge der Spalten umstellen wollen. Deshalb empfiehlt es sich, auch die Spaltenangabe über eine Spaltenindex-Variable zu steuern, die vor der Ausgabe eines Datensatzes auf 1 zurückgesetzt wird und nach jeder Spalte um 1 erhöht wird.

'Ausgabe aller Datensätze in Excel mit Spaltenindex  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim zeilenIndex As Integer
Dim spaltenIndex As Integer
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Personen")
zeilenIndex = 1
Do Until rs.EOF
  spaltenIndex = 1
  Cells(zeilenIndex, spaltenIndex) = rs!PersonNr
  spaltenIndex = spaltenIndex + 1
  Cells(zeilenIndex, spaltenIndex) = rs!Nachname
  spaltenIndex = spaltenIndex + 1
  Cells(zeilenIndex, spaltenIndex) = rs!Vorname
  spaltenIndex = spaltenIndex + 1
  Cells(zeilenIndex, spaltenIndex).NumberFormat = "dd.mm.yyyy"
  Cells(zeilenIndex, spaltenIndex) = rs!Geburtsdatum
  spaltenIndex = spaltenIndex + 1
  Cells(zeilenIndex, spaltenIndex).NumberFormat = "0.00"
  Cells(zeilenIndex, spaltenIndex) = rs!Gewicht
  zeilenIndex = zeilenIndex + 1
  rs.MoveNext
Loop
rs.Close
db.Close

Daten ändern

Um Daten zu ändern können Sie entweder über ein QueryDef-Objekt ein SQL-Update-Statement an die Datenbank senden oder die Daten direkt im Recordset ändern. Das Vorgehen lässt sich wie folgt beschreiben:

• Öffnen des Recordsets mit dem zu ändernden Datensatz.
• Prüfen, ob tatsächlich ein Datensatz vorhanden ist.
• Aufruf der Recordset-Methode Edit.
• Zuweisen der neuen Werte zu den jeweiligen Spalten.
• Aufruf der Recordset-Methode Update.

Im folgenden Beispiel wird das Gewicht der Person mit der PersonNr gleich 1 auf 95 Kilogramm geändert.

'Ändern von Daten im Recordset  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Select * From Personen Where PersonNr = 1;")
With rs
  If Not .EOF Then
    .Edit
    !Gewicht = 95
    .Update
  End If
End With
rs.Close
db.Close

Daten hinzufügen

Um Daten hinzuzufügen können Sie entweder über ein QueryDef-Objekt ein SQL-Insert-Statement an die Datenbank senden oder die Daten direkt im Recordset hinzufügen. Das Vorgehen lässt sich wie folgt beschreiben:

• Öffnen des Recordsets.
• Aufruf der Recordset-Methode AddNew.
• Zuweisen der neuen Werte zu den jeweiligen Spalten.
• Aufruf der Recordset-Methode Update.

Um ein leeres Recordset zu öffnen, verwenden Sie ein SQL-Statement, das garantiert keine Daten zurückliefert. In unserem Beispiel verwende ich das Kriterium Where PersonNr Is Null.

Da die Spalte PersonNr in unserer Datenbank vom Typ Autowert ist, können Sie dieser Spalte keinen Wert zuweisen. Nach der Anweisung Update können Sie das Recordset anweisen, den internen Zeiger auf den zuletzt geänderten Datensatz zu setzen. Somit haben Sie dann lesenden Zugriff auf die neu erstellte PersonNr.

Im folgenden Beispiel wird eine neue Person angelegt und der Wert der neu vergebenen PersonNr im Direktfenster ausgegeben.

'Hinzufügen von Daten zum Recordset  
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim neuePersonNr As Long
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rs = db.OpenRecordset("Select * From Personen Where PersonNr Is Null;")
With rs
  .AddNew
  !Nachname = "Musterfranz"
  !Vorname = "Franz"
  !Geburtsdatum = "08.03.2003"
  !Gewicht = 84
  .Update
  'Zeiger auf neuen Datensatz
  .Move 0, rs.LastModified
  'Auslesen der Spalte PersonNr
  neuePersonNr = !PersonNr
End With
rs.Close
db.Close
Debug.Print neuePersonNr

Beachten Sie, dass die Angabe des Datums als Zeichenkette erfolgen kann. Sie könnten das Datum auch explizit mit der Funktion CDate in ein Datum konvertieren: CDate("08.03.2003").

QueryDef-Objekt

Allgemein

Mit dem QueryDef-Objekt können Sie auf gespeicherte Abfragen zugreifen oder Abfragen erstellen, die nur im Code generiert und nicht in der Datenbank gespeichert werden.

Im Folgenden gehe ich nur auf das Ändern großer Datenmengen und das Löschen von Daten ein.

Ändern großer Datenmengen

Sie wollen das Gewicht aller Personen um 10 Kilogramm erhöhen. Wenn Sie eine Aktualisierungsabfrage zur Gewichtserhöhung in Ihrer Datenbank erstellt haben, so können Sie durch Ausführen der gespeicherten Abfrage die Änderung durchführen.

Das folgende Beispiel führt die gespeicherte Abfrage MehrGewicht aus, in dem diese über die Auflistung Quer>Defs angesprochen wird und über die QueryDef-Methode Execute ausgeführt wird. Anschließend wird mit der QueryDef-Eigenschaft RecordsAffected die Anzahl der betroffenen Datensätze im Direktfenster ausgegeben.

'Aufruf gespeicherter Abfrage  
Dim db As DAO.Database
Dim rsq As DAO.QueryDef
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rsq = db.QueryDefs("MehrGewicht")
rsq.Execute
Debug.Print rsq.RecordsAffected

Um ein QueryDef-Objekt zu erzeugen, das nicht in der Datenbank gespeichert ist, rufen Sie auf das Datenbankobjekt die Methode CreateQueryDef auf und übergeben als Argument ein SQL-Statement. Da wir für das erste Argument Name eine leere Zeichenkette angeben, wird das QueryDef-Objekt nicht in der Datenbank gespeichert.

'Aufruf nicht gespeicherter Abfrage  
Dim db As DAO.Database
Dim rsq As DAO.QueryDef
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rsq = db.CreateQueryDef("", "Update Personen Set Gewicht = Gewicht + 10;")
rsq.Execute
Debug.Print rsq.RecordsAffected

Löschen von Datensätzen

Sie wollen alle Datensätze aus der Tabelle Personen entfernen. Wenn Sie dazu eine Löschabfrage in Ihrer Datenbank erstellt haben, so können Sie durch Ausführen der gespeicherten Abfrage die Löschung durchführen.

Das folgende Beispiel führt die gespeicherte Abfrage PersonenLöschen aus, in dem diese über die Auflistung Quer>Defs angesprochen wird und über die QueryDef-Methode Execute ausgeführt wird. Anschließend wird mit der QueryDef-Eigenschaft RecordsAffected die Anzahl der gelöschten Datensätze im Direktfenster ausgegeben.

'Aufruf gespeicherter Abfrage  
Dim db As DAO.Database
Dim rsq As DAO.QueryDef
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rsq = db.QueryDefs("PersonenLöschen")
rsq.Execute
Debug.Print rsq.RecordsAffected

Um ein QueryDef-Objekt zu erzeugen, das nicht in der Datenbank gespeichert ist, rufen Sie auf das Datenbankobjekt die Methode CreateQueryDef auf und übergeben als Argument ein SQL-Statement. Da wir für das erste Argument Name eine leere Zeichenkette angeben, wird das QueryDef-Objekt nicht in der Datenbank gespeichert.

'Aufruf nicht gespeicherter Abfrage  
Dim db As DAO.Database
Dim rsq As DAO.QueryDef
Set db = OpenDatabase("C:\temp\Personenverwaltung.accdb")
Set rsq = db.CreateQueryDef("", "Delete From Personen;")
rsq.Execute
Debug.Print rsq.RecordsAffected

Download Codebeispiele

Die gezeigten Codebeispiele können Sie als xlsm-Datei herunterladen.

Seitenanfang   Inhaltsverzeichnis