Gentlemen Access Development Kit

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




1. When working with Access, during the execution of the query, warning messages appear. They are quite useful when debugging an application, but for users, as a rule, they are not needed. You can disable / enable them using a small VBA code:
')

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

Specifying in the form of the parameter 0 to disable and 1 to enable.

2. In order to protect the database from prying eyes and to differentiate access rights, the workgroup file (.MDW format) is used. The path to the workgroup file can be specified manually, but if there are many users on the network, it is much more convenient to have an Access file with a button, which, when pressed, executes one line of code defining the path to MDW:

 Application.SetDefaultWorkgroupFile Path:="D:\  \file.MDW" 

3. I don’t know how I would work (I’d probably work the same way, but much slower), if I hadn’t created my own form with text search in the content of requests or forms. It is often necessary to perform some kind of refactoring or determine the scope of a table or field.

For searching queries, a form with a 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 for 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 find errors when working in production mode on the working database, it is highly desirable to add a VBA module (tracing module) to record the events taking place in a text log file. A simple function of writing 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 put into a separate Access database file and added to all existing databases via References / VBA editor links.

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



6. Often, queries specify 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 like this:

 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 query:

 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 launching the query, you must set the value of the global variable (you can set it when opening the main form):

 start_ID=3 '         

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

 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 information in the table (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, we set a ban on viewing and changing the demotable table, and on the request we will set the permission. But since the query from us uses a table to which it is not authorized, it will not return data to us. In order for it to return the data you need to add to the query sql at the end

 WITH OWNERACCESS OPTION 

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

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

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

As “WhereCondition”, you can specify the condition on which form entry you need to open it (in case the form is bound to data). By specifying, for example, “ZakazID = 56325”, you can open the form with exactly the ZakazID data value of 56325.

As the value of "OpenArgs", you can specify any parameters that can be read on the opened form
in Private Sub Form_Load () using Me.OpenArgs. If you need to pass several parameters, you can transfer them as a text string, separated by a symbol. And then in the 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. To export existing tables there is a free utility SQL Server Migration Assistant , 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 did not know this ...":

Did you know that in order to open the Access file, the macros do not work and the default form does not open, you must hold down the Shift key?