In diesem Tutorial erfahren Sie, wie Sie mithilfe von VBA mit Access Tables arbeiten.
Auf VBA-Tabellen zugreifen
Zu Beginn werden wir die einfachen Befehle zum Arbeiten mit Tabellen in Access demonstrieren. Später in diesem Tutorial zeigen wir Ihnen vollständige, professionell entwickelte Funktionen für die Arbeit mit Tabellen in Access.
Tabelle erstellen
Dieser Code verwendet SQL, um eine Tabelle mit dem Namen „Table1“ mit den Feldern „ID“ und „Name“ zu erstellen:
Dim table_name As String Dim fields As String table_name = "Table1" fields = "([ID] varchar(150), [Name] varchar(150))" CurrentDb.Execute "CREATE TABLE" & Tabellenname & Felder
Tabelle schließen
Diese Zeile mit VBA-Code schließt eine Tabelle (Änderungen werden gespeichert):
DoCmd.Close acTable, "Table1", acSaveYes
So schließen Sie eine Tabelle ohne zu speichern:
DoCmd.Close acTable, "Table1", acSaveNo
Tabelle löschen
Dieser Code löscht eine Tabelle (Hinweis: zuerst sollte die Tabelle geschlossen werden):
DoCmd.Close acTable, "Table1", acSaveYes DoCmd.DeleteObject acTable = acDefault, "Table1"
Tabelle umbenennen:
Diese Codezeile benennt eine Zugriffstabelle um:
DoCmd.Rename "Table1", acTable, "Table1_New"
Eine andere Möglichkeit besteht darin, die TableDefs-Eigenschaft eines Datenbankobjekts zu verwenden.
Setze tdf = db.TableDefs(strOldTableName) tdf.Name = strNewTableName
Tabelle leeren / leeren
Dieser VBA-Code leert eine Tabelle:
DoCmd.RunSQL "DELETE * FROM" & "Table1"
Tabelle abschneiden / Datensätze löschen
Diese VBA-Codezeile verwendet SQL, um Datensätze aus einer Tabelle zu löschen, die bestimmte Kriterien erfüllen:
DoCmd.RunSQL ("DELETE * FROM" & "Table1" & "WHERE" & "num=2")
Tabelle nach Excel exportieren
Um eine Tabelle nach Excel zu exportieren, verwenden Sie die DoCmd.OutputTo Methode:
DoCmd.OutputTo acOutputTable, "Table1", acFormatXLS, "c:\temp\ExportedTable.xls"
oder benutze die DoCmd.TransferSpreadsheet Methode:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "c:\temp\ExportedTable.xls", True
Tabelle aktualisieren
Der folgende Code aktualisiert einen Datensatz, ohne die Warnmeldung anzuzeigen:
DoCmd.SetWarnings (False) DoCmd.RunSQL "Update ProductsT SET ProductsT.ProductName = 'Product AAA' WHERE (((ProductsT.ProductID)=1))"
Auf VBA-Tabellenfunktionen zugreifen
Die obigen Codebeispiele sind die einfachen Befehle, die Sie verwenden können, um mithilfe von VBA mit Tabellen zu interagieren. Sie müssen jedoch oft viel mehr unterstützenden Code (einschließlich Fehlerbehandlung) hinzufügen, um diese Befehle richtig zu verwenden. Nachfolgend finden Sie professionell entwickelte Funktionen für die Arbeit mit Tabellen in Access.
Tabellendatensätze zählen
Diese Funktion zählt die Anzahl der Datensätze in einer Tabelle:
Öffentliche Funktion Count_Table_Records(TableName As String) As Integer On Error GoTo Err: Dim r As DAO.Recordset Dim c As Integer Set r = CurrentDb.OpenRecordset("Select count(*) as rcount from " & TableName).OpenRecordset If (r .EOF) Then c = 0 Else c = Nz(r!rCount, 0) End If Count_Table_Records = c Exit Function Err: Call MsgBox("Ein Fehler ist aufgetreten: " & Err.Description, vbExclamation, "Error") End Function ' Verwendungsbeispiel Private Sub Count_Table_Records_Example() MsgBox (Count_Table_Records("Table1")) End Sub
Überprüfen Sie, ob Tabelle existiert Funktion
Diese Funktion testet, ob eine Tabelle existiert und gibt TRUE oder FALSE zurück:
Public Function TableExists(ByVal strTableName As String) As Boolean 'Funktion: Ermitteln, ob Tabelle in einer Access-Datenbank vorhanden ist 'Arguments:strTablename: Name der zu überprüfenden Tabelle Dim tdf As DAO.TableDef On Error Resume Next Set tdf = CurrentDb.TableDefs(strTableName ) TableExists = (Err.Number = 0) End Function
Hier ein Beispiel für die verwendete Funktion:
Private Sub TableExists_Example() If VBA_Access_Checks.TableExists("Table") = True Then MsgBox ("Tabelle wurde gefunden!") Else MsgBox ("Tabelle wurde NICHT gefunden!") End If End Sub
Tabellenfunktion erstellen
Diese Funktion erstellt eine Tabelle in Access VBA in der aktuellen Datenbank:
Öffentliche Funktion CreateTable(table_fields As String, table_name As String) As Boolean Dim strCreateTable As String Dim intCount As Integer Dim strFields() As String Dim strValues() As String Dim strInsertSQL As String Dim intCounter As Integer Dim intData Als Integer bei Fehler GoTo Err strFields = Split(table_fields, ",") strCreateTable = "CREATE TABLE" & table_name & "(" For intCounter = 0 To UBound(strFields) - 1 strCreateTable = strCreateTable & "[" & strFields(intCounter) & "] varchar( 150)," Next If Right(strCreateTable, 1) = "," Then strCreateTable = Left(strCreateTable, Len(strCreateTable) - 1) strCreateTable = strCreateTable & ")" End If CurrentDb.Execute strCreateTable intCounter = 0 intData = 0 If Err.Number = 0 Then CreateTable = True Else CreateTable = False End If Exit Function Err: CreateTable = False MsgBox Err.Number & " " & Err.Description End Function
Diese Funktion gibt TRUE zurück, wenn die Tabelle erfolgreich erstellt wurde, oder FALSE, wenn die Tabelle nicht erstellt wurde.
Sie können die Funktion wie folgt aufrufen:
Private Sub CreateTable_Example() Call CreateTable("f1,f2,f3,f4", "ttest") End Sub
Tabellenfunktion löschen / löschen
Diese Funktion löscht eine Tabelle, wenn sie existiert:
Öffentliche Funktion DeleteTableIfExists(TableName As String) If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & TableName & "'")) Then DoCmd.SetWarnings False DoCmd.Close acTable, TableName, acSaveYes DoCmd. DeleteObject acTable = acDefault, TableName Debug.Print "Table " & TableName & " gelöscht… " DoCmd.SetWarnings True End If End Function
Sie können die Funktion wie folgt aufrufen:
Private Sub DeleteTableIfExists_Example() Aufruf DeleteTableIfExists("Table1") End Sub
Leere Tabellenfunktion
Diese Funktion leert eine Tabelle, wenn sie existiert:
Öffentliche Funktion EmptyTable(TableName As String) If Not IsNull(DLookup("Name", "MSysObjects", "Name='" & TableName & "'")) Then DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM " & TableName Debug.Print "Table " & TableName & " empty… " DoCmd.SetWarnings True End If End Function
Sie können die Funktion wie folgt aufrufen:
Private Sub EmptyTable_Example() Aufruf EmptyTable("Table1") End Sub
Tabellenfunktion umbenennen
Diese VBA-Funktion benennt eine Tabelle um:
Öffentliche Funktion RenameTable(ByVal strOldTableName As String, ByVal strNewTableName As String, Optional strDBPath As String) As Boolean Dim db As DAO.Database Dim tdf As TableDef ' Trap für alle Fehler. On Error Resume Next ' Wenn der Datenbankname leer ist… If Trim$(strDBPath) = "" Then '… dann setze Db auf den aktuellen Db. Set db = CurrentDb() Else ' Andernfalls setze Db auf die angegebene offene Datenbank. Set db = DBEngine.Workspaces(0).OpenDatabase(strDBPath) ' Prüfen, ob ein Fehler aufgetreten ist. If Err Then 'MsgBox "Datenbank zum Öffnen nicht gefunden: " & strDBPath RenameTable = False Exit Function End If End If If ObjectExists("Table", strOldTableName, strDBPath) Then Set tdf = db.TableDefs(strOldTableName) tdf.Name = strNewTableName db.Close RenameTable = True Else RenameTable = False End If End Function 'Verwendungsbeispiel Private Sub RenameTable_Example() Call RenameTable("table1", "table2") End Sub
Sie können die Funktion wie folgt aufrufen:
Private Sub RenameTable_Example() Aufruf RenameTable("table1", "table2") End Sub
Abschneiden / Löschen von Datensätzen aus der Tabelle
Diese Funktion löscht Datensätze aus einer Tabelle mit Fehlerbehandlung:
Öffentliche Funktion Delete_From_Table(TableName As String, Criteria As String) Bei Fehler GoTo SubError DoCmd.SetWarnings False DoCmd.RunSQL ("DELETE * FROM " & TableName & " WHERE " & Criteria) DoCmd.SetWarnings True SubExit: Exit Function SubError: MsgBox " Delete_From_Table error: " & vbCrLf & Err.Number & ": " & Err.Description Resume SubExit End Function 'Verwendungsbeispiel Public Sub Delete_From_Table_Example() Aufruf Delete_From_Table("Table1", "num=2") End Sub
Tabelle nach Excel exportieren
Diese Codezeile exportiert eine Tabelle nach Excel (eine neue Tabelle):
DoCmd.OutputTo acOutputTable, "Table1", acFormatXLS, "c:\temp\ExportedTable.xls"
Oder Sie verwenden diese Funktion:
Öffentliche Funktion Export_Table_Excel(TableName As String, FilePath As String) DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TableName, FilePath, True End Function 'Verwendungsbeispiel Sub Export_Table_Excel_Example () Export_Table_Excel("Table1", "c:\temp\ExportedTable.xls") End Sub
Der obige Code wird in eine neue Tabelle exportiert. Stattdessen können Sie einer vorhandenen Tabelle eine Tabelle hinzufügen. Unser Artikel zum Importieren / Exportieren in Access VBA behandelt dies ausführlicher.
Datensätze zu einer Tabelle hinzufügen/anhängen
Diese Funktion fügt einen Datensatz zu einer Tabelle hinzu / hängt ihn an:
Öffentliche Funktion Append_Record_To_Table(TableName As String, FieldName As String, FieldValue As String) Bei Fehler GoTo SubError Dim rs As DAO.Recordset Dim SQL As String Dim CurrentYear As Integer Set rs = CurrentDb.OpenRecordset(TableName) rs.AddNew rs(FieldName) .Value = FieldValue rs.Update rs.Close Set rs = Nothing SubExit: Exit Function SubError: MsgBox "RunSQL error: " & vbCrLf & Err.Number & ": " & Err.Description Resume SubExit End Function 'Verwendungsbeispiel Private Sub Append_Record_To_Table_Example () Call Append_Record_To_Table("Table1", "num", 3) End Sub
Datensatz aus Formular zur Tabelle hinzufügen
Diese Funktion fügt einer Tabelle aus einem Formular einen Datensatz hinzu:
Öffentliche Funktion Add_Record_To_Table_From_Form(TableName As String) Bei Fehler GoTo SubError Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset(TableName) rs.AddNew 'rs![Field1] = Value1 'rs![Field2] = Value2 'rs![ Field3] = Value3 rs.Update rs.Close Set rs = Nothing SubExit: Exit Function SubError: MsgBox "Refresh_Form error: " & vbCrLf & Err.Number & ": " & Err.Description End Function
