Джентльменский набор разработчика Access

За время работы разработчиком Access набралась куча полезностей, которую считаю своим долгом выложить на Хабр. Многие из этих сниппетов находятся в сети, а некоторые находятся с трудом или безнадежно затеряны.




1. При работе с Access во время выполнения запроса возникают предупреждающие сообщения. Они довольно полезны во время отладки приложения, но для пользователей, как правило, не нужны. Отключить/включить их можно с помощью небольшого кода VBA:

Application.SetOption "Confirm Action Queries", 0
Application.SetOption "Confirm Document Deletions", 0
Application.SetOption "Confirm Record Changes", 0

Указав в виде параметра 0 для отключения и 1 для включения.

2. Для того, чтобы защитить базу данных от посторонних глаз и разграничить права доступа используется файл рабочей группы ( формата .MDW ). Путь к файлу рабочей группы можно указать вручную, но если в сети много пользователей, то гораздо удобнее иметь файлик Access с кнопкой, по нажатию которой отрабатывает одна строка кода, задающая путь к MDW:

Application.SetDefaultWorkgroupFile Path:="D:\путь к файлу\file.MDW"

3. Не знаю, как бы я работал (наверное, работал бы так же, но гораздо медленнее), если бы не создал себе форму с поиском текста в содержимом запросов или форм. Очень часто необходимо производить какой-то рефакторинг или определять область применения таблицы или поля.

Для поиска по запросам поможет форма с кодом, который выполняет поиск в тексте запросов:

For i = 0 To CurrentDb.QueryDefs.Count - 1
If InStr(CurrentDb.QueryDefs(i).sql, strSearchWord) > 0 Then
' здесь можно организовать вывод на консоль или в текст CurrentDb.QueryDefs(i).Name
End If
Next
Или можно пройтись только по всем полям запросов:
For i = 0 To CurrentDb.QueryDefs.Count - 1
For j = 0 To CurrentDb.QueryDefs(I).Fields.Count
' и здесь можно организовать вывод на консоль или в текст CurrentDb.QueryDefs(i).Name
Next
Next

Для поиска по формам код немного объемнее:

Dim strSearchWord As String ' текст, который мы будем искать в формах
strSearchWord=”цена”

Dim oAO As object
Dim frm As Form
Dim ctrl As object
For Each oAO In CurrentProject.AllForms
DoCmd.OpenForm oAO.Name, acDesign
Set frm = Forms(oAO.Name)
For Each ctrl In frm.Controls

Select Case ctrl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox ‘ поиск только по определенным контролам
                If InStr(1, ctrl.ControlSource & "", strSearchWord) Then
                  ' здесь вывод на консоль или куда хотите  frm.Name и ctrl.Name
                End If
 
End Select

    Next
    DoCmd.Close acForm, oAO.Name, acSaveNo
    Next
    Set oAO = Nothing
    Set frm = Nothing
    Set ctrl = Nothing

4. Для того, чтобы сделать нашу работу чуть более солидной с точки зрения программирования и для возможности поиска ошибок при работе в режиме production на рабочей базе данных очень желательно добавить модуль VBA (tracing модуль) для записи происходящих событий в текстовый файл лога. Простая функция записи в текстовый файл будет очень полезна при отладке.

Sub Trace(ByVal txtinfo As String)
On Error Resume Next
MyFile = "D:\" & "logfile.txt"
fnum = FreeFile()
Open MyFile For Append As fnum
txtinfo = CStr(Now()) + " " + txtinfo
Print #fnum, txtinfo
Close #fnum
End Sub

5. Этот код (из пункта 4) вполне можно вынести в отдельный файл базы данных Access и добавить во все существующие базы данных через References/Ссылки редактора VBA.

Если у вас имеется несколько файлов баз данных Access, то любой повторяющийся код можно вынести в отдельный файл. Единственное изменение, которое может быть необходимо сделать — в случае если в коде используется объект CurrentDb, то заменить его на CodeDb, дабы обращение шло к объектам той базы, которая используется в качестве хранителя общего кода.



6. Зачастую в запросах указывают в качестве параметра значение какого-либо поля открытой формы. Например, таким образом:

WHERE demotable.infonumber>Forms!Form1!Field25

Но иногда необходимо указать параметр непосредственно в самом запросе. Сделать это можно так:

PARAMETERS val Text ( 255 ), fldID Long;
UPDATE demotable SET demofield = val
WHERE [fieldID]=fldID;

И далее из кода Access задать эти параметры и выполнить запрос:

With CurrentDb.QueryDefs("demoquery")
   .Parameters("fldID") = 2
   .Parameters("val") = "newvalue"
   .Execute
End With

Альтернативой может быть создание модуля VBA и добавление в него глобальной переменной, а также функции, возвращающей эту переменную.

Global start_ID As Long

Public Function get_global() As Long
get_global = start_ID
End Function

Перед запуском запроса необходимо задать значение глобальной переменной (можно задать при открытии основной формы):

start_ID=3 ' для примера задал значение глобальной переменной равное трем

И в самом запросе указать параметром название возвращающей значение функции:

SELECT * FROM demotable WHERE (demotable.infonumber>get_global());

6.1 Этот способ получения параметра запроса можно использовать для частичного ограничения доступа к информации таблицы (в случае использования рабочей группы). При загрузке формы в зависимости от текущего пользователя установим значение глобальной переменной:

Private Sub Form_Load()
If (CurrentUser = "Buh") Then
start_ID = 1
Else
start_ID = 1000
End If
End Sub

Далее установим запрет на просмотр и изменение таблицы demotable, а на запрос установим разрешение. Но так как запрос у нас использует таблицу, на которую нет прав, то данных он нам не вернет. Для того, чтобы он вернул данные необходимо к sql запроса добавить в конце

WITH OWNERACCESS OPTION

В результате пользователь Buh сможет иметь доступ ко всем строкам таблицы, а все остальные пользователи ко всем строкам за исключением первой 1000.

7. Для того, чтобы открыть форму из кода используется код:

DoCmd.OpenForm "FormName", View, "FilterName", "WhereCondition", DataMode, WindowMode, "OpenArgs"

В качестве «WhereCondition» можно указать условие, на какой записи формы необходимо её открыть (в случае, если форма привязана к данным). Указав, например, «ZakazID=56325», можно открыть форму именно со значением данных ZakazID равным 56325.

В качестве значения «OpenArgs» можно указать какие-либо параметры, которые на открываемой форме можно будет считать
в Private Sub Form_Load() с помощью Me.OpenArgs. Если необходимо передать несколько параметров, то можно передать их в виде текстовой строки, разделив символом. И далее в Private Sub Form_Load() разбить на части:

 If Len(Me.OpenArgs) > 0 Then    
    x = Split(Me.OpenArgs, "|") ' разрезаем параметры на массив строку, разделенную символом |
    param1 = x(0)
    param2 = x(1)
    param3 = x(2)
  End If

8. Многие забывают, что Access работает не только таблицами из файлов mdb/accdb, но и с таблицами других баз данных. Для экспорта уже существующих таблиц есть бесплатная утилита SQL Server Migration Assistant, хотя можно воспользоваться и встроенным функционалом или найти стороннее решение.

И напоследок небольшой хинт из разряда «Это знает каждый ребенок, но я вот этого не знал…»:

Знаете ли вы, что для того, чтобы при открытии файла Access не срабатывали макросы и не открывалась форма по умолчанию, необходимо держать нажатой клавишу Shift?