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

• Microsoft 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?

Метки:

• access
• сниппеты
• полезности
• трюки
• нюансы
• microsoft
• vba
• sql