Microsoft Access Secrets, tricks, and pitfalls

Microsoft Access Secrets, tricks, and pitfalls

Brian C. Blackman.

System Support Engineer, Microsoft Access Basic

Summary

This article outlines that when you use Microsoft Access, you can increase the execution speed of your application by using Access Basic, reduce the amount of coding, and reduce the number of times you are programming for the Microsoft Windows application interface. The technology of problems encountered in developing applications. Careful use of variables and the storage space it occupies can effectively reduce the resources consumed by an application, while also improving its performance overall.

Incorrectly invoking the Windows application interface can have unintended side effects and potentially damage to the code and data segments of an application. Using an empty 32-bit pointer correctly is essential in Microsoft Access.

Microsoft access has an attribute with no formal text when working with tables and reports. This feature allows you to make a procedure call from the Design Window Nature Sheet window, by pressing the shift and F2 keys while invoking the method.

Introduced

Microsoft Access Basic provides a rich development environment. This development environment gives you enough flexibility and control over the Microsoft Windows application interface, while protecting you from all the hassle of developing a high-level or low-level language development environment. However, many optimizations, valid data, and modularity can only be used by application designers. Developers should be committed to using algorithms with care. In addition to the general program design concept, there are some special storage space management technology, the correct use of these technologies can improve the execution speed of applications, reduce the application of better storage resources.

Increase speed and reduce code volume

You can use several techniques to improve your coding speed, but you can't find a substitute for a valid algorithm. The next few tips will increase your coding speed while reducing the storage space your application consumes.

Mathematical operations using an orthopedic data type

Even though Microsoft Access uses a federated processor to handle floating-point arithmetic, integer arithmetic is always faster. When your calculations do not contain decimals, try to use an integer or a long integer instead of a variable or a double integral type. Integer division is also faster than floating-point division. When you use some other valid data types, you will be warned that nothing can replace an effective algorithm.

Avoid using procedure Calls

Avoid using subroutines or function calls in the loop body. Each call increases the burden on the code due to extra work and time. Each call requires that the function's local variables and parameters be compacted, and that the stack size is fixed, not arbitrarily enlarged, and shared at Microsoft Access.

Cautious use of indefinite data types

Indefinite data types provide greater flexibility, such as allowing for proper handling of null values and automatic processing of overflows. In addition, this data type is larger than the traditional data type and consumes more storage space. As mentioned earlier, variables of indefinite length data types are slower in mathematical calculations.

Use variables to store frequently used properties

It is faster to find and set a variable than to perform these actions on a property. If you want to get or lookup a property value many times, then assign this attribute to a variable and use this variable instead of the attribute, your code will run much faster. For example, in a loop where you look up a control attribute in a table, assign the attribute to a variable outside the loop, and then query a variable in the loop instead of the lookup attribute.

Pre-Download form

When your application starts and sets the visibility properties to ' false ', if you install all of your tables, the performance of your application will make you feel pretty fast. When you need to display a table, you only need to set the Visible property of the table to ' true ', which is much faster than installing a table. Keep in mind that for each form you install, you need to consume storage space from the application's global heap.

Traps in Access Basic

A trap that is often encountered in Access Basic is a call to an external procedure in a Dynamic connection library (DLLS). When you provide a program to your users, a warning will appear when you invoke the external dynamic link library, otherwise you will get an error message that attempts to define the installation module with the same function.

Use a unique alias

In Access Basic, if you know the entry point (the name of a function in a dynamic-link library), you can call the external function in the dynamic-link library. However, the limitation of using this method is that you can only declare external functions once. If you install the same Windows application interface that calls your module call, you will not get an unknown error: An attempt was made to define the installation module with the same function.

The module you are trying to install either contains the same function name or the procedure name contained in the existing module. To remove these procedures and use the Find command in the Edit menu to locate the duplicate process name, you need to use a technique called alias usage to resolve this problem. This technique allows you to give your process a unique name. However, the alias you choose may not be unique, so to make your name unique, you can use the initial value and underscore to first declare all the processes that you call from the dynamic link library, for example, the declaration GetActiveWindow:

Declare Bcb_getactivewindow Lib "Kernel" Alias "GetActiveWindow" () as Integer.

How to pass a null pointer to a dynamic link library

An empty 32-bit pointer is valid or requires parameters for some dynamic-link libraries. To specify a null value, use 0&. When your function calls a procedure and passes an expression 0&amp, "&" specifies a 32-bit null pointer, in the function declaration, an as any parameter instructs Access Basic to not type check for that parameter and passes the value to the called function.

The following code demonstrates a correct way to declare an Access basic function that passes a null pointer parameter to a procedure in a dynamic-link library. This section declares the WriteProfileString application interface function. This function comes from the external Windows dynamic link library "kernel".

Declare Function writeprofilestring Lib "Kernel" (ByVal lpapplicationname as any, ByVal lpkeyname as any, ByVal lpstring A s any)

The next function calls an external procedure, and the bits specify a null value for each parameter. This will allow the writeprofilestring to fill its internal cache area and any changes to the Win.ini are written to disk.

Important to

Improper use of this function can cause a general protection failure or modify your Win.ini file.

Function Nflushinicache ()
Nflushinicache = WriteProfileString (0&, 0&, 0&)
End Function

How to pass and receive unsigned integer numbers from a dynamic-link library

There is often a situation in which an unsigned integer of two bytes long is returned from the call to the external dynamic link library. However, Access Basic does not support this type of data. The correct calculation of this data type requires converting it from an unsigned integer to the Access basic Long data type.

The number of Access basic integers ranges from 32768 to 32767. A range of unsigned integers is from 0 to 65535. Access Basic uses the most obvious one to place the sign bit of the value, so when the number is over 32767, the 16th digit is placed into a negative sign. To calculate an unsigned integer, you must manually adjust the 16th digit.

There are two ways to convert from an unsigned integer to an Access basic long integer or from an Access Basic's long integer to an unsigned integer. The first method uses the most basic mathematical operation (65535 is added or reduced by unsigned integer values). The second method uses the bitwise operation. In fact, the arithmetic method works just as well as the bitwise method, except that the arithmetic method may be more readable and the bitwise method may be quicker to execute.

Arithmetic methods

The following larithuinttoint (Nuint) and Larithinttounint (lbytes) procedures demonstrate how to use arithmetic methods that convert unsigned integers. The first function reads an unsigned integer and returns a value that has a changed bit long integer. The second function reads a long integer value, and then returns a value that is converted to an unsigned integer.

Function Larithuinttoint (nuint as Integer)
If Nuint < 0 Then
Larithuinttoint = Nuint + 65536
Else
Larithuinttoint = Nuint
End If
End Function

Function Narithinttounint (lbytes as Long)
If lbytes > 32767 Then
Narithinttounint = lBytes-65536
Else
Narithinttounint = Lbytes
End If
End Function

Bitwise action Method

The following nbwuinttoint (Lbytes) and Lbwinttouint (Nuint) procedures demonstrate the use of the bitwise method to convert unsigned integers. The first function reads an unsigned integer and returns a value that has been converted to an integral type. The second function reads a long integer value, and then returns an unsigned integer value that has been converted. The second function uses a prompt box statement to prevent overflow information from being passed to a function with a value greater than 64KB.

1010001111100000 (-23584)
and 1111111111111111 (ffff&)
----------------
1010001111100000 (41952)

The functions follow:

Function Lbwinttouint (nuint as Integer)
Lbwinttouint = Nuint and &HFFFF&
End Function

Function Nbwuinttoint (lbytes as Long)
Dim Ntemp as Integer

If lbytes > 65535 Then
MsgBox "You passed a value larger than 65535"
Exit Function
End If

Ntemp = Lbytes and &h7fff
Nbwuinttoint = ntemp Or-(Lbytes and &h8000)
End Function

Note that the:&hffff& requires "&" at the end of the 16 number. This ensures that the 32-bit 16-digit number is represented by a 16-bit value.

Tips in Access Basic

In Microsoft Access, there are features that have no formal text previously, and they allow you to specify a procedure that is invoked from the property sheet window of a from or a statement. To get to the minimum window, you just have to press the shift and F2 keys at the same time.

BUILDER form function

If the definition of a function in a module begins with a "Builderr Form" containing a similar OnClose property or event identifier, the process can be invoked by pressing the shift and F2 keys. For example, when you are in the design window of a form, and the current cursor falls on the edit control of the OnClose event in the Properties window, pressing the SHIFT and F2 keys will call the Builderformonclose function. This makes most of the properties and all events for form and reports. The following code describes the format of the function:

Function Builderformonclose (Szformname As String, Szcontrolname as String, Szcurrentvalue as String, szreserved as String )

If szcurrentvalue = "" Then
DOCMD SelectObject A_macro, "", True
SendKeys "%n%fs" & "New Macro" & "{Enter}"
Forms (Szformname). OnClose = "New Macro"

Else
DOCMD SelectObject A_macro, Szcurrentvalue, True
SendKeys "%d"

End If End Function

Parameters Szformname, Szcontrolname, Szcurrentvalue, and szreserved must be declared even if you never use them. When your function is called, these arguments are always passed. If they are not declared, then Microsoft Access will not call your function. If a parameter is assigned to the OnClose event, the preceding procedure opens a macro, and if the edit control is empty, the code generates a new macro named "New Macro" and sets the edit control value to "new Macro". Admittedly, the previous example is not perfect, but it does illustrate the conditions necessary to produce a perfect and effective program.