Это руководство научит вас работать с таблицами доступа с помощью VBA.
Доступ к таблицам VBA
Для начала продемонстрируем простые команды для работы с таблицами в Access. Позже в этом руководстве мы покажем вам полный набор профессионально разработанных функций для работы с таблицами в Access.
Создать таблицу
Этот код будет использовать SQL для создания таблицы с именем «Таблица1» с полями «ID» и «Имя»:
Уменьшить имя_таблицы в виде строки Уменьшить поля в виде строки table_name = "Table1" fields = "([ID] varchar (150), [Name] varchar (150))» "CurrentDb.Execute" CREATE TABLE "& table_name & fields
Закрыть таблицу
Эта строка кода VBA закроет таблицу (с сохранением изменений):
DoCmd.Close acTable, «Table1», acSave Да
Чтобы закрыть таблицу без сохранения:
DoCmd.Close acTable, «Таблица1», acSaveNo
Удалить таблицу
Этот код удалит таблицу (примечание: сначала таблица должна быть закрыта):
DoCmd.Close acTable, «Table1», acSaveYes DoCmd.DeleteObject acTable = acDefault, «Table1»
Переименовать таблицу:
Эта строка кода переименует таблицу доступа:
DoCmd.Rename "Table1", acTable, "Table1_New"
Другой вариант - использовать свойство TableDefs объекта базы данных.
Установите tdf = db.TableDefs (strOldTableName) tdf.Name = strNewTableName
Пустая / Очистить таблицу
Этот код VBA очистит таблицу:
DoCmd.RunSQL "УДАЛИТЬ * ИЗ" & "Таблица1"
Обрезать таблицу / Удалить записи
Эта строка кода VBA использует SQL для удаления записей из таблицы, соответствующих определенным критериям:
DoCmd.RunSQL ("УДАЛИТЬ * ОТ" & "Таблица1" & "ГДЕ" & "число = 2")
Экспорт таблицы в Excel
Чтобы экспортировать таблицу в Excel, используйте DoCmd.OutputTo метод:
DoCmd.OutputTo acOutputTable, «Table1», acFormatXLS, «c: \ temp \ ExportedTable.xls»
или используйте DoCmd.TransferSpreadsheet метод:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, «Table1», «c: \ temp \ ExportedTable.xls», True
Обновить таблицу
Следующий код обновит запись без отображения предупреждающего сообщения:
DoCmd.SetWarnings (False) DoCmd.RunSQL «Обновить ProductsT SET ProductsT.ProductName = 'Product AAA' WHERE (((ProductsT.ProductID) = 1))»
Доступ к табличным функциям VBA
Приведенные выше примеры кода - это простые команды, которые вы можете использовать для взаимодействия с таблицами с помощью VBA. Однако вам часто потребуется добавить гораздо больше вспомогательного кода (включая обработку ошибок), чтобы правильно использовать эти команды. Ниже вы найдете профессионально разработанные функции для работы с таблицами в Access.
Счетчик записей в таблице
Эта функция подсчитает количество записей в таблице:
Открытая функция 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 ("Выбрать счетчик (*) как rcount из" & 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 ("Произошла ошибка:" & Err.Description, vbExclamation, "Error") End Function ' Пример использования Private Sub Count_Table_Records_Example () MsgBox (Count_Table_Records ("Table1")) End Sub
Проверить, существует ли функция таблицы
Эта функция проверяет, существует ли таблица, возвращая ИСТИНА или ЛОЖЬ:
Общедоступная функция TableExists (ByVal strTableName As String) As Boolean 'Функция: Определить, существует ли таблица в базе данных Access' Аргументы: strTablename: Имя таблицы для проверки Dim tdf As DAO.TableDef При ошибке Возобновить следующий Установить tdf = CurrentDb.TableDefs (strTableName ) TableExists = (Err.Number = 0) Завершить функцию
Вот пример используемой функции:
Private Sub TableExists_Example () Если VBA_Access_Checks.TableExists ("Table") = True Then MsgBox ("Таблица была найдена!") Else MsgBox ("Таблица не найдена!") End If End Sub
Создать табличную функцию
Эта функция создаст таблицу в Access VBA в текущей базе данных:
Открытая функция CreateTable (table_fields как 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 As Integer On Error GoTo Err strFields = Split (table_fields, ",") strCreateTable = "CREATE TABLE" & table_name & "(" For intCounter = 0 To UBound (strFields) - 1 strCreateTable = strCreateTable & "[" & strFields (intCounter) & "] varchar ( 150), «Далее, если справа (strCreateTable, 1) =», «Тогда strCreateTable = Left (strCreateTable, Len (strCreateTable) - 1) strCreateTable = strCreateTable &») «Конец, если CurrentDb.Execute strCreateTable If intCounter = 0 Err.Number = 0 Then CreateTable = True Else CreateTable = False End If Exit Function Err: CreateTable = False MsgBox Err.Number & "" & Err.Description End Function
Эта функция вернет ИСТИНА, если таблица создана успешно, или ЛОЖЬ, если таблица не создана.
Вы можете вызвать функцию так:
Частная подпрограмма CreateTable_Example () Вызов CreateTable ("f1, f2, f3, f4", "ttest") End Sub
Функция удаления / удаления таблицы
Эта функция удалит таблицу, если она существует:
Открытая функция DeleteTableIfExists (TableName As String) Если не IsNull (DLookup ("Name", "MSysObjects", "Name = '" & TableName & "'")), то DoCmd.SetWarnings False DoCmd.Close acTable, TableName, acSaveYes DoCmd. DeleteObject acTable = acDefault, TableName Debug.Print «Таблица» & TableName & «удалено…» DoCmd.SetWarnings True End If End Функция
Вы можете вызвать функцию так:
Частная подпрограмма DeleteTableIfExists_Example () Вызов DeleteTableIfExists ("Table1") End Sub
Функция пустой таблицы
Эта функция очистит таблицу, если она существует:
Открытая функция EmptyTable (TableName As String) Если не IsNull (DLookup ("Name", "MSysObjects", "Name = '" & TableName & "'")), то DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM" & TableName Debug.Print «Таблица» & Имя таблицы & «очищено…» DoCmd.SetWarnings True End If End Функция
Вы можете вызвать функцию так:
Частная подпрограмма EmptyTable_Example () Вызов EmptyTable ("Table1") End Sub
Переименовать функцию таблицы
Эта функция VBA переименует таблицу:
Открытая функция RenameTable (ByVal strOldTableName как строка, ByVal strNewTableName как строка, необязательный strDBPath как строка) As Boolean Dim db As DAO.Database Dim tdf As TableDef 'Ловушка для любых ошибок. При ошибке Resume Next 'Если имя базы данных пусто … If Trim $ (strDBPath) = "" Then' … тогда установите Db на текущую Db. Установите db = CurrentDb () Else 'В противном случае установите Db в указанную открытую базу данных. Установите db = DBEngine.Workspaces (0) .OpenDatabase (strDBPath) 'Проверьте, произошла ли ошибка. Если Err Then 'MsgBox "Не удалось найти базу данных для открытия:" & strDBPath RenameTable = False Завершение функции Завершение Если завершение Если ObjectExists ("Таблица", strOldTableName, strDBPath) Затем Установите tdf = db.TableDefs (strOldTableName) tdf.Name = strNewTableName db.Close RenameTable = True Else RenameTable = False End If End Пример использования функции Private Sub RenameTable_Example () Вызов RenameTable ("table1", "table2") End Sub
Вы можете вызвать функцию так:
Private Sub RenameTable_Example () Вызов RenameTable ("table1", "table2") End Sub
Обрезать / удалить записи из таблицы
Эта функция удалит записи из таблицы с обработкой ошибок:
Открытая функция Delete_From_Table (TableName as String, Criteria as String) On Error GoTo SubError DoCmd.SetWarnings False DoCmd.RunSQL («DELETE * FROM» & TableName & «WHERE» & Criteria) DoCmd.SetWarnings True SubExit: Exit Function Subgror: Ошибка Delete_From_Table: "& vbCrLf & Err.Number &": "& Err.Description Resume SubExit End Function 'Пример использования Public Sub Delete_From_Table_Example () Вызов Delete_From_Table (" Table1 "," num = 2 ") End Sub
Экспорт таблицы в Excel
Эта строка кода экспортирует таблицу в Excel (новую электронную таблицу):
DoCmd.OutputTo acOutputTable, «Table1», acFormatXLS, «c: \ temp \ ExportedTable.xls»
Или вы можете использовать эту функцию:
Общедоступная функция Export_Table_Excel (TableName As String, FilePath As String) DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TableName, FilePath, True End Function 'Пример использования Sub Export_Table_Excel_Example () Export_Table_Excel ("Table1", "corted: Конец подписки
Приведенный выше код будет экспортирован в новую электронную таблицу. Вместо этого вы можете добавить таблицу к существующей электронной таблице. В нашей статье об импорте / экспорте в Access VBA это рассматривается более подробно.
Добавить / добавить записи в таблицу
Эта функция добавит / добавит запись в таблицу:
Открытая функция Append_Record_To_Table (TableName As String, FieldName As String, FieldValue As String) При ошибке GoTo SubError Dim rs As DAO.Recordset Dim SQL As String Dim CurrentYear As Integer Set rs = CurrentDb.OpenRecordset (TableName) rs.AddNew rs (FieldName) rs (FieldName) .Value = FieldValue rs.Update rs.Close Set rs = Nothing SubExit: Exit Function SubError: MsgBox "Ошибка RunSQL:" & vbCrLf & Err.Number & ":" & Err.Description Resume SubExit End Function 'Пример использования Private Sub Append_Record_To_Table_Example () Вызвать Append_Record_To_Table ("Table1", "num", 3) End Sub
Добавить запись в таблицу из формы
Эта функция добавит запись в таблицу из формы:
Открытая функция Add_Record_To_Table_From_Form (TableName As String) При ошибке GoTo SubError Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset (TableName) rs.AddNew 'rs! [Field1] = Value1' rs! [Field2] = [Value2 'rs! [Field2] = [Value2' rs! [Field2] = [Value2 'rs! Поле3] = Значение3 rs.Update rs.Close Set rs = Nothing SubExit: Exit Function SubError: MsgBox "Refresh_Form error:" & vbCrLf & Err.Number & ":" & Err.Description End Function
