Access gentleman development kit

During the time I worked as an Access developer, I got a bunch of usefulness, which I consider it my duty to put it on Habr. Many of these snippets are online, and some are difficult or hopelessly lost.




1. When working with Access, warning messages appear during query execution. They are quite useful during debugging applications, but for users, as a rule, are not needed. You can disable / enable them with a little VBA code:

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

Specifying as parameter 0 to disable and 1 to enable.

2. In order to protect the database from prying eyes and to differentiate access rights, a workgroup file (.MDW format) is used. You can specify the path to the workgroup file manually, but if there are many users on the network, then it is much more convenient to have an Access file with a button that, when clicked, processes one line of code that sets the path to MDW:

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

3. I don’t know how I would work (I would probably work the same way, but much more slowly) if I hadn’t created myself a form with text search in the contents of requests or forms. Very often it is necessary to perform some kind of refactoring or determine the scope of the table or field.

To search for queries, a form with the code that searches in the query text will help:

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

To search by forms, the code is a bit more voluminous:

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. In order to make our work a little more solid from the point of view of programming and to be able to search for errors when working in production mode on a working database, it is very desirable to add a VBA module (tracing module) for recording events in a text log file. A simple function to write to a text file will be very useful when debugging.

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. This code (from point 4) can be easily transferred to a separate Access database file and added to all existing databases through the References / Links of the VBA editor.

If you have several Access database files, then any duplicate code can be moved to a separate file. The only change that may be necessary is if the CurrentDb object is used in the code, then replace it with CodeDb, so that the access goes to the objects of the base that is used as the custodian of the common code.



6. Often in requests indicate the value of an open form field as a parameter. For example, in this way:

WHERE demotable.infonumber>Forms!Form1!Field25

But sometimes it is necessary to specify the parameter directly in the request itself. You can do it this way:

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

And then, from the Access code, set these parameters and execute the request:

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

An alternative would be to create a VBA module and add a global variable to it, as well as a function that returns this variable.

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

Before starting the request, you must set the value of the global variable (you can set it when you open the main form):

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

And in the request itself, specify with the parameter the name of the function returning the value:

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

6.1. This method of obtaining a query parameter can be used to partially restrict access to table information (in the case of a workgroup). When loading the form, depending on the current user, set the value of the global variable:

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

Next, set the ban on viewing and changing the demotable table, and set the permission for the request. But since the request we have uses a table that has no rights, it will not return data to us. In order for it to return data, it is necessary to add to the sql of the request at the end

WITH OWNERACCESS OPTION

As a result, the Buh user will have access to all rows of the table, and all other users to all rows except the first 1000.

7. In order to open the form from the code, the code is used:

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

As “WhereCondition”, you can specify the condition on which form record you need to open it (in case the form is data-bound). Specifying, for example, “ZakazID = 56325”, you can open the form with the ZakazID data value equal to 56325.

As the value of “OpenArgs”, you can specify any parameters that can be read
in the Private Sub Form_Load () using Me .OpenArgs. If you need to pass several parameters, you can pass them as a text string, separated by a symbol. And further in Private Sub Form_Load () break into parts:

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

8. Many people forget that Access works not only with tables from mdb / accdb files, but also with tables from other databases. There is a free SQL Server Migration Assistant utility for exporting existing tables , although you can use the built-in functionality or find a third-party solution.

And finally, a small hint from the category “Every child knows this, but I didn’t know this ...”:

Do you know that in order to open macros and not open the default form, you need to hold down the Shift key?