Джентльменский набор разработчика 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, дабы обращение шло к объектам той базы, которая используется в качестве хранителя общего кода.

image

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?
Поделиться публикацией

Похожие публикации

Комментарии 28

    +2
    В свое время, с некоторым удивлением обнаружил, что база Access позволяет хранить и выполнять хранимые процедуры.
      +1
      Ограничение в 2 гигабайта в свое время попортило мне много крови и нервов.
      В общем, по возможности, старайтесь MS Access не использовать.
      Его уже очень давно не развивают — оставили для совместимости и демонстративно-обучающих целей.
      Майкрософту еще один конкурент их же MS SQL-ю совсем не нужен.
        0
        Да, это что-то вроде лайт версии базы данных. Самое то для обучающий целей. В случае необходимости можно подцепить табличку из какой-либо базы ( из того же SQL Server-а ). А, еще удобнее, чем в Excel хранить какую-то базу данных ( например телефонов ).
        Так как язык Visual Basic довольно простой, то может использоваться более-менее продвинутым IT руководством в своих целях.
        Для каких-то серьезных многопользовательских проектов действительно лучше ни-ни…
        Я думаю Access переродится во что-то новое вроде Sharepoint или SQL Server Compact…
          –5
          А не дофига ли баблища хотят за примитивную записную книжечку для телефонов? ИМХО — есть более адекватные записные книжечки, может быть — даже облачные. А для целей обучения — вы ещё фокспро или клиппер предложите. Может быть, обучать стоит на чём-то более нормальном?
        –1
        Кто-то ЭТО всё ещё использует для чего-то, кроме дремучего легаси?
          +4
          У Access есть своя ниша и она не сказать что маленькая. Безусловно, это довольно мелкие приложения уровня отдела предприятия. Часто это промежуточный костыль между ERP и конечными пользователями.
            –1
            Хреновая ERP, если к ней костыли нужны. Тем более — такие.
              +2
              Это вы зря. Дело не в ERP, а в размерах предприятия, стоимости разработки для ERP, сложности и длительности процедуры запросить и получить любые изменения в ERP и т.д.
            +1
            Я, например, использую. Посоветуйте альтернативу для сложных расчетов (по количеству операций) на больших объемах данных — буду признателен. Такой же макрос в экселе работает в разы медленнее, спасибо хоть можно миллион строк теперь, но все равно иногда не хватает.
              0
              Не очень понятна сложность расчетов для которых Вы ищете альтернативу, но ограничение Excel в 1 млн строк легко снимается при помощи бесплатного add-in MS PowerPivot. А DAX, собственный язык PowerPivot, весьма функционален и шустр.

              Если честно, после появления PowerPivot я почти забыл про существование Access, хотя примеров использования именно как

              мелкие приложения уровня отдела предприятия. Часто это промежуточный костыль между ERP и конечными пользователями.
              привести могу, в том числе и в компаниях из списка Fortune Global 500 в связке с SAP например.
                0
                У меня, например, задача по сути сводится к расчету суммы f(n) по n от 1 до totaln, где totaln может быть достаточно большим. При этом f() — сложная функция, которая довольно долго работает, и нет возможности ускорить путём добавления промежуточных результатов в новые «столбцы» — время обновления сравнимо с временем расчета.
                Был огромный макрос в экселе, который все это считает, переход на access дал увеличение скорости в десятки раз.

                За PowerPivot спасибо — изучу. Но боюсь, все равно будет медленнее access — ну не заточен excel под такие объёмы.
                  0
                  Ограничение 20^2 строк в Excel сугубо теоретическое. Пользоваться Excel файлом уже после 100-200 тысяч строк проблематично, а уж с расчётами вообще — туши свет. Access как раз тут и вступает в свои права. SQL — это уже другой уровень и по инфраструктуре, и по деньгам, и по уровню навыков.
                    0
                    Видимо, я избалован SQL-серваками, но даже задачу уровня описанной ниже решал бы при помощи denvera. Если предложите решение в Access за 30 мин — с меня Вам плюс))
                      –1
                      Я думаю, что указанную задачу надо было решать при помощи одной единственной формулы в Excel :)
                        0
                        Либо я невнятно описал проблему, либо Вы невнимательно прочитали условие задачи :-)

                        Нужно вычислить не 1, а много медиан — специально указал на медиану в сводной таблице. Стандартной формулой не решить, хотя на excelplanet пытались, но у меня не взлетело — много если.

                        PS: думаю в условии следовало использовать «медиан» вместо «медианы» — тогда было бы яснее
                          –1
                          В любом случае, в Excel это решать сподручнее, чем на Access.
                            +1
                            Так и думал, что «плюс» останется у меня)))
                              –1
                              А вам показалось, что он мне был нужен? Напрасно.
                                +1
                                Извините, если обидел. Но, на конкретный пример из Вашего выражения
                                Пользоваться Excel файлом уже после 100-200 тысяч строк проблематично, а уж с расчётами вообще — туши свет. Access как раз тут и вступает в свои права.

                                Вы сами и ответили чуть выше.

                                ИМХО: лично мне очевидно постепенное сокращение зоны потенциального применения MS Access, как снизу, благодаря развитию возможностей MS Power, так и сверху, благодаря упрощению разворачивания полноценных SQL-серверов. Не удивлюсь, если через несколько лет MS Access в нынешнем виде может исчезнуть из продуктовой линейки. Время покажет…
                                  +1
                                  Кстати, глянул количество статей на Хабре с тегом Microsoft Access: 3 статьи, с 13 "+" на троих и менее чем 25000 просмотрами на всех…

                                  Неужели тема настолько непопулярна?

                                    –1
                                    Послушайте, я даже не думал ни спорить, ни тем более обижаться :)
                                    Абсолютно согласен, что времена настольных СУБД уходят. Действительно, Power надстройки Excel отвоёвывают нишу Access, а SQL продукты становятся достаточно привычными. Тот же MS SQL Express денег не просит (в отличие от Access), а обладает неоспоримыми преимуществами. Но лет 10 Access ещё точно протянет до момента изгнания из состава офисного пакета. Много старых приложений; SQL усложняется и пользователи его боятся; высок барьер знаний, чтобы начать им пользоваться; Excel слишком обременён своей архитектурой, чтобы хотя бы приблизиться к Access в плане производительности. Есть ещё ниша у Access, есть. Опять же Access позиционируется в качестве Front-End-а для SQL БД. В общем, это неплохой вариант для разработчиков низкой и средней квалификации. И он ещё долго им останется.
                                      0
                                      Не очень понял за что минусов наставили, я вот лично согласен с последней фразой (ну, кроме оценки времени). DSB75 — достойно дискутировал.
                                        0
                                        Да не обращайте внимания, я понял, что это не вы.
                                          0
                                          Видимо обострилось чувство справедливости в начале четвертого десятка))

                                          Согласен когда по делу, а просто так… не понимаю.

                                          PS: интересно, сам нарвусь на минусы?)
                      +1
                      Мдам, тогда согласен. У меня потолком сложности вычислений был одноразовый поиск медианы для массива данных в 100K строчек (в сводной таблице). При этом под рукой был мускульный сервак большой производительности, но силами SQL, без создания промежуточных таблиц не смог решить проблему — были только права на SELECT. Зато за 20 минут гуглинга нашел на просторах интернета .xla расширение с отличным кодом VBA внутри, решившим проблему за 10 сек раздумий.
                +1
                Базы MS Access когда-то было очень удобно использовать в качестве «лёгкого» решения для того чтобы использовать одну базу несколькими пользователями — база шарилась через виндовую шару и работаем с нескольких клиентов через драйвер Jet.
                Но сейчас с этим работать я бы уже не стал — лучше обновить до полноценного SQL Server.
                  +3
                  А как пользователю работать с SQL Server? Вручную запросы на SQL писать? Теребить программистов, чтобы сделали ещё одно приложение для очередного отчёта, который, быть может, никогда больше не понадобится? А в Access он сам за 15 минут склепал пару формуляров и таблиц и доволен.
                  +2
                  Для акцесса есть очень хорошая и удобная библиотека SQLhelper. Лежать она должна, по идее, по всему интернету, а скорость разработки с ней увеличивается очень значительно — особенно для тех, кто не пишет запросы руками. Основное ее предназначение — улучшить взаимодействие акцесса и ms sql. И вот такая обвязка работает просто чудесно, пишется быстрей, чем на дельфи, например.
                  Незаслуженно акцесс забывают, есть у него добрые фишки.

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое