Доступ до таблиць VBA - оновлення, підрахунок, видалення, створення, перейменування, експорт

Цей підручник навчить вас працювати з таблицями доступу за допомогою VBA.

Доступ до таблиць VBA

Для початку ми продемонструємо прості команди для роботи з таблицями в Access. Далі в цьому підручнику ми покажемо вам всі професійно розроблені функції для роботи з таблицями в Access.

Створити таблицю

Цей код буде використовувати SQL для створення таблиці з назвою «Таблиця1» з полями «Ідентифікатор» та «Ім'я»:

 Затемнити назву таблиці як рядок Затемнити поля як рядок таблиця -ім'я = "Таблиця1" поля = "([ID] varchar (150), [Name] varchar (150))" CurrentDb.Execute "CREATE TABLE" & table_name & fields

Закрити таблицю

Цей рядок коду VBA закриє таблицю (збереження змін):

DoCmd.Close acTable, "Таблиця1", acSaveТак

Щоб закрити таблицю без збереження:

DoCmd.Close acTable, "Таблиця1", acSaveNo

Видалити таблицю

Цей код видалить таблицю (примітка: спочатку таблицю слід закрити):

DoCmd.Close acTable, "Таблиця1", acSaveТак DoCmd.DeleteObject acTable = acDefault, "Таблиця1"

Перейменувати таблицю:

Цей рядок коду перейменує таблицю доступу:

DoCmd.Rename "Table1", acTable, "Table1_New"

Інший варіант - використовувати властивість TableDefs об’єкта бази даних.

Встановіть tdf = db.TableDefs (strOldTableName) tdf.Name = strNewTableName

Порожня / Очистити таблицю

Цей код VBA спорожнить таблицю:

DoCmd.RunSQL "DELETE * FROM" & "Table1"

Зрізати таблицю / Видалити записи

У цьому рядку коду VBA використовується SQL для видалення записів із таблиці, які відповідають певним критеріям:

DoCmd.RunSQL ("DELETE * FROM" & "Table1" & "WHERE" & "num = 2")

Експорт таблиці в Excel

Щоб експортувати таблицю в Excel, використовуйте DoCmd.OutputTo метод:

DoCmd.OutputTo acOutputTable, "Таблиця1", acFormatXLS, "c: \ temp \ ExportedTable.xls"

або скористайтеся DoCmd.TransferSpreadsheet метод:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "c: \ temp \ ExportedTable.xls", True

Оновлення таблиці

Наступний код оновить запис без відображення попереджувального повідомлення:

DoCmd.SetWarnings (False) DoCmd.RunSQL "Оновлення продуктів T SET ProductsT.ProductName = 'Продукт AAA' ДЕ (((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 As Integer Set r = CurrentDb.OpenRecordset ("Select count (*) as rcount from" & TableName) .OpenRecordset If (r .EOF) Тоді c = 0 В іншому c = Nz (r! RCount, 0) Закінчення Якщо Count_Table_Records = c Вихід з функції Err: Виклик MsgBox ("Сталася помилка:" & Err.Description, vbExclamation, "Помилка") Функція завершення ' Приклад використання Приватний підрахунок Count_Table_Records_Example () MsgBox (Count_Table_Records ("Table1")) End Sub

Перевірте, чи функціонує таблиця

Ця функція перевірить, чи існує таблиця, повертаючи TRUE або FALSE:

Загальнодоступна функція TableExists (ByVal strTableName As String) Як Boolean 'Функція: Визначити, чи існує таблиця в базі даних Access' Аргументи: strTablename: Назва таблиці для перевірки Dim tdf As DAO.TableDef On Error Resume Next Set tdf = CurrentDb.TableDefs (strTableName ) TableExists = (Err.Number = 0) End Function

Ось приклад використовуваної функції:

Private Sub TableExists_Example () Якщо VBA_Access_Checks.TableExists ("Таблиця") = Істина, тоді MsgBox ("Таблиця знайдена!") Інше MsgBox ("Таблиця НЕ знайдена!") Завершення, якщо кінець Sub

Створити функцію таблиці

Ця функція створить таблицю в Access VBA у поточній базі даних:

Загальнодоступна функція 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 As Integer Err GoTo 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 intCounter = 0 intData = 0 Якщо Err.Number = 0 Тоді CreateTable = True Else CreateTable = False End Якщо функція виходу Err: CreateTable = False MsgBox Err.Number & "" & Err.Description End Function

Ця функція поверне TRUE, якщо таблиця створена успішно, або FALSE, якщо таблиця не створена.

Викликати функцію можна так:

Приватний суб CreateTable_Example () Виклик CreateTable ("f1, f2, f3, f4", "ttest") Кінець Sub

Функція видалення / скидання таблиці

Ця функція видалить таблицю, якщо вона існує:

Загальнодоступна функція DeleteTableIfExists (TableName As String) If Not IsNull (DLookup ("Name", "MSysObjects", "Name = '" & TableName & "'")) Тоді DoCmd.SetWarnings False DoCmd.Close acTable, TableName, acSaveYes DoCmd DeleteObject acTable = acDefault, TabName Debug.Print "Table" & TableName & "deleted…" DoCmd.SetWarnings True End If End Функція

Викликати функцію можна так:

Приватна підказка DeleteTableIfExists_Example () Виклик DeleteTableIfExists ("Таблиця1") Закінчити суб

Функція порожньої таблиці

Ця функція очистить таблицю, якщо вона існує:

Загальнодоступна функція EmptyTable (TableName As String) If Not IsNull (DLookup ("Name", "MSysObjects", "Name = '" & TableName & "'")) Тоді DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM" & TableName Налагодження.Надрукуйте "Таблиця" & Назва таблиці & "Очищено …" DoCmd.SetWarnings True End If End Функція

Викликати функцію можна так:

Приватна підпустка EmptyTable_Example () Виклик EmptyTable ("Таблиця1") Закінчити підп

Перейменувати функцію таблиці

Ця функція VBA перейменує таблицю:

Загальнодоступна функція RenameTable (ByVal strOldTableName As String, ByVal strNewTableName As String, Optional strDBPath As String) As Boolean Dim db Як DAO.Database Dim tdf As TableDef 'Trap для будь -яких помилок. Увімкнено Помилка Відновити Далі 'Якщо ім'я бази даних порожнє … Якщо Обрізати $ (strDBPath) = "" Тоді "… то встановіть для Db поточний Db. Встановити db = CurrentDb () В іншому випадку 'В іншому випадку встановіть для Db вказану відкриту базу даних. Встановіть db = DBEngine.Workspaces (0) .OpenDatabase (strDBPath) 'Подивіться, чи сталася помилка. Якщо Err Тоді 'MsgBox "Не вдалося знайти базу даних для відкриття:" & strDBPath RenameTable = Помилковий вихід функції припинення Якщо закінчується If Якщо ObjectExists ("Таблиця", strOldTableName, strDBPath) Тоді встановіть tdf = db.TableDefs (strOldTableName) tdf.Name = strNewTableName db.Close RenameTable = True Else RenameTable = False End If End Function 'Приклад використання Private Sub RenameTable_Example () Виклик RenameTable ("table1", "table2") End Sub

Викликати функцію можна так:

Приватний підзаголовок RenameTable_Example () Виклик RenameTable ("table1", "table2") End Sub

Зрізати / видалити записи з таблиці

Ця функція видалить записи з таблиці з обробкою помилок:

Загальнодоступна функція Delete_From_Table (Таблиця Назва як рядок, Критерії як рядок) Помилка GoTo SubError DoCmd.SetWarnings False DoCmd.RunSQL ("DELETE * FROM" & TableName & "WHERE" & Criteria) DoCmd.SetWarnings True SubExit: SubExBit Exit: SubExBit Exit: SubExBit Exit: SubExBit Exit: SubExit Exit: SubExit Exit: SubExBit Exit: SubExit Exit: SubExit Exit: SubExit Exit: SubExit Exit Помилка Delete_From_Table: "& vbCrLf & Err.Number &": "& Err. Опис Відновлення підзавершення функції підвиходу" Приклад використання Публічний підсумок Delete_From_Table_Example () Виклик Delete_From_Table ("Таблиця1", "номер = 2") Кінцевий підрозділ

Експорт таблиці в Excel

Цей рядок коду експортує таблицю в Excel (нова електронна таблиця):

DoCmd.OutputTo acOutputTable, "Таблиця1", acFormatXLS, "c: \ temp \ ExportedTable.xls"

Або ви можете скористатися цією функцією:

Загальнодоступна функція Export_Table_Excel (TableName As String, FilePath As String) DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TableName, FilePath, True End Функція 'Приклад використання Sub Export_Table_Excel_Example ("\ Export_T End Sub

Наведений вище код експортуватиметься до нової електронної таблиці. Замість цього ви можете додати таблицю до наявної електронної таблиці. Наша стаття про імпорт / експорт у Access VBA охоплює це більш детально.

Додавання / додавання записів до таблиці

Ця функція додасть / додасть запис до таблиці:

Загальнодоступна функція Append_Record_To_Table (TableName As String, FieldName As String, FieldValue As String) On Error GoTo SubError Dim rs As DAO.Recordset Dim SQL As String Dim CurrentYear As Integer Set rs = CurrentDb.OpenRecordset (TableName) nrs.Ad .Value = FieldValue rs.Update rs.Close Set rs = Nothing SubExit: Вихід із функції SubError: MsgBox "Помилка RunSQL:" & vbCrLf & Err.Number & ":" & Err.Description Відновити SubExit End Function 'Приклад використання Private Sub AppeT_Texample_Record () Викличте Append_Record_To_Table ("Таблиця1", "число", 3) Закінчити підрозділ

Додати запис до таблиці з форми

Ця функція додасть запис до таблиці з форми:

Загальнодоступна функція Add_Record_To_Table_From_Form (Назва таблиці як рядок) У разі помилки GoTo SubError Dim rs Як DAO.Recordset Встановіть rs = CurrentDb.OpenRecordset (TableName) rs.AddNew 'rs! [Field1] = Value1' rs2! [2] Поле3] = Значення3 rs. Оновлення rs.Закрити Встановити rs = Нічого Підвихід: Вихід Функція Підпомилка: MsgBox "Помилка Refresh_Form:" & vbCrLf & Err.Number & ":" & Err.Опис Кінцева функція