MyTetra Share
Делитесь знаниями!
Джентльменский набор разработчика Access
03.08.2018
00:02
Текстовые метки: VBA Access, access, сниппеты, полезности, трюки, нюансы, microsoft, vba, sql
Раздел: VBA

Джентльменский набор разработчика 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?

Метки:

Так же в этом разделе:
 
MyTetra Share v.0.52
Яндекс индекс цитирования