Brian C. Blackman
Systems Support Engineer, Microsoft Access
Created: July 6, 1993
This article outlines techniques that when applied to Microsoft® Access®, using Access Basic, can increase application execution speed, decrease code size, and reduce common pitfalls in application development when programming for the Microsoft Windows™ APIs (application programming interfaces). Prudent use of variables and the memory space those variables occupy can significantly reduce the resources an application consumes and increase its overall performance.
Incorrect calling of Windows APIs will have undesirable side effects and potentially corrupt an application's code or data segment. Correct use of a Null 32-bit (far) pointer in Microsoft Access is imperative.
Microsoft Access has an undocumented feature that can be used when working with forms and reports. This feature allows you to make a procedure call from the design view Property Sheet window by pressing the SHIFT + F2 key combination.
Microsoft® Access® Basic provides a rich development environment that gives you the flexibility and control of Microsoft Windows™ APIs while shielding you from many of the frustrations associated with using high- and low-level language development environments. However, many aspects of optimization and efficient data and code modeling can only be applied by the application designer. The developer should rely on prudent use of efficient algorithms. In addition to general programming concepts, there are specific memory management techniques that when correctly used can increase the application's execution speed and decrease the memory resource the application consumes.
You can use several tricks to increase the speed of your code, but there's no substitute for efficient algorithms. Following are several suggestions to increase the speed of your code and decrease the memory your application consumes.
Even though Access will use a coprocessor for floating-point math, integer math is always faster. Use Integer or Long data types in place of Variant or Double data types when your calculations don't include fractional values. Integer division (\) is faster than floating-point division (/). A warning in using more efficient data types: The code will be less forgiving. Remember, there's no substitute for efficient algorithms.
Avoid the use of Sub or Function calls in loops. Each call adds to the code's overhead by involving additional work and time. Each call requires that the function's local variables and arguments be placed on the stack. The stack size is fixed, cannot be increased, and is shared with Microsoft Access.
The Variant provides greater flexibility, such as allowing proper handling of Nulls and automatic handling of overflows. Yet this data type is larger and consumes more memory for storage than conventional data types. As previously mentioned, Variant variables are slower in mathematical calculations.
You can reference and set Variables faster than properties. If you are getting or referring to the value of a property many times, your code will run faster if you assign the property to a variable and use the variable instead. For example, in a loop you refer to a property of a control on a form; it is faster to assign the property to a variable outside the loop and then reference the variable in the loop instead of the property.
The performance of your application can be perceived as fast if you load all your forms when your application starts and set their visible property to False. When you need to show a form you just set the visible property to True—which is faster than loading the form. Remember that you will be consuming memory from the application's global heap for every form you load.
A frequently encountered trap in Access Basic is the use of external procedures in dynamic-link libraries (DLLs). When you supply your customers and clients with solutions, use caution when calling external DLLs; otherwise expect to get the error message "Tried to load module with duplicate procedure definition."
In Access Basic you can call external procedures in DLLs when you know the entry point (the name of the function in the DLL). However, the caveat is that you can only declare the external procedure once. If you load a library that calls the same Windows API that your module calls, you will get the infamous error, "Tried to load module with duplicate procedure definition."
You tried to load a module that either contains duplicate procedure names or contains procedure names that are in use by procedures in existing modules. To remove these procedures, use the Find command on the Edit menu to find the duplicate procedure names. To work around this problem you need to use a technique called aliasing that will allow you to give your procedure an unique name. There's always a chance that the alias you chose is not unique. So, to make your alias unique, you can precede all your declared procedures from DLLs with your initials and an underscore. For example, declare GetActiveWindow as:
Declare bcb_GetActiveWindow Lib "Kernel" Alias "GetActiveWindow" () As Integer
A null 32-bit (far) pointer is a valid or required parameter for some dynamic-link libraries (DLLs). To specify a null value, use 0&. When your function calls a procedure and passes the expression 0&, the ampersand (&) specifies a 32-bit (far) null pointer. In a function declaration, an As Any parameter instructs Access Basic to omit type checking for that parameter and to pass the value to the called function.
The following code demonstrates the correct method to declare an Access Basic function that passes a null parameter to a procedure in a DLL. This code declares the WriteProfileString API function from the external Windows dynamic-link library "kernel."
Declare Function WriteProfileString Lib "Kernel" (ByVal lpApplicationName
As Any, ByVal lpKeyName As Any, ByVal lpString As Any)
The following function calls the external procedure, specifying null for each argument. This causes WriteProfileString to flush its internal cache and writes to disk any changes to WIN.INI.
Important Incorrect use of this function can cause a general protection fault or modify your WIN.INI file.
Function nFlushIniCache()
nFlushIniCache = WriteProfileString (0&, 0&, 0&)
End Function
There are situations in which calling procedures from external DLLs returns a 2-byte unsigned integer. Access Basic doesn't support this data type. Correctly evaluating this data type requires converting it from an unsigned integer to an Access Basic long data type.
The Access Basic integer data type has a range of -32,768 to 32,767. An unsigned integer has a range of 0 to 65,536. Access Basic uses the most significant bit to set the sign of the value. Therefore, when a value exceeds 32,767, bit 16 is set to reflect a negative number. To evaluate an unsigned integer, you must manually adjust bit 16.
There are two methods you can use to convert to and from the unsigned integer type to the Access Basic long data type. The first method uses basic arithmetic (65,536 is subtracted or added to the unsigned integer). The second uses bitwise operators. The Arithmetic method works as well as the Bitwise method; however, the arithmetic method may be more readable, and the bitwise method may be faster when executed repetitively.
The following lArithUintToInt (nUint) and lArithIntToUnint(lBytes) procedures demonstrate using the Arithmetic method of converting unsigned integers. This first function reads in an unsigned integer and returns the converted value as a long. The second function reads in a long and returns 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
The following nBWUintToInt(lBytes) and lBWIntToUint(nUint) procedures demonstrate using the Bitwise method of converting unsigned integers. The first function reads in an unsigned integer and returns the converted value as a long. The second function reads in a long and returns an unsigned integer. The message box statement in the second function is used to prevent an overflow message when the value passed to the function is greater than 64 kB.
To illustrate what is taking place in the first bitwise function, nUint equals -23584, a value returned from an external DLL that is an unsigned integer and needs to be converted to a long:
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 The &HFFFF& requires the "&" at the end of the hex number. This qualifies the hex number as 32-bit versus 16-bit value.
There is a previously undocumented feature in Microsoft Access that allows you to specify procedures that are called from the Property Sheet window of a form or report when you press the key stroke combination for Zoom View, SHIFT + F2.
If a function identifier in a module begins with "BuilderForm" and contains a property or event identifier such as OnClose, pressing the key combination SHIFT + F2 will call this procedure. For example, when you're in design view of a form and the cursor location is in the edit control of the OnClose event in the Property Sheet window, pressing SHIFT + F2 will call the BuilderFormOnClose function. This applies to most properties and all events of both forms and reports. The format of the function is demonstrated in the following code:
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
The arguments 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're not declared then Microsoft Access doesn't call your function. The preceding procedure opens a macro if one is assigned to the OnClose event; if the edit control is empty the code creates a new macro called "New Macro" and sets the edit control value to "New Macro". Granted, the preceding example is not very elegant, but it does demonstrate the necessary components to create elegant and efficient database solutions.