Less P. Wright, Jr.
SQL-Win Database Solutions
Created: September 23, 1993
The purpose of this article is to provide readers with a thorough foundation on how to use the Microsoft® Windows™ application programming interface (API) in their Microsoft Access® database applications, provide some functional code routines involving the Win16 API, and leave them prepared to develop more powerful code involving the Win16 APIs and Access Basic. The article assumes that the reader has some familiarity with Access Basic and the Access Basic coding environment.
Access Basic, compared with macros, provides you with a much greater degree of control over your database applications. However, if you've reached the point where you'd like to go beyond what even Access Basic allows you to do, it’s time to take your database programming one step further and begin using the Microsoft® Windows™ application programming interface (API). For instance, if you'd like to extend the control of your application during run time, perhaps by changing the title bar on the fly, or if you'd like to customize your application by setting the Microsoft Access® title bar to something other than the default "Microsoft Access", or if you want to preserve custom user settings between sessions, learning how to use the Win16 API will provide the solution.
In addition to merely extending Access Basic, API functions can frequently supplant the need to write Access Basic code in the first place. Why write an Access Basic function that duplicates an API function that (a) already exists, (b) is written in a faster language (C as opposed to the Access Basic interpreted p-code format), and (c) has already been coded for you? Generally, the only difficulty that prevents many people from using the API is knowing how to pass parameters to the Win16 API and how to use the return data that Windows provides in response to your request for information or services. This article is intended to overcome this problem area.
Rather than simply listing some useful API declarations and letting you hack away if you want to use additional API functions, this article will attempt to demonstrate the key components that are used in calling and using most of the Win16 functions from Access Basic. It will also highlight some traps and pitfalls that frequently ensnare people when they begin using the API and provide you with some immediately functional code examples. I hope this article will help you safely venture deeper into the Windows API in search of programming gems that will help you extend and customize your Microsoft Access application even further than Access Basic allows.
We'll begin with the basics, for example, what does API mean? API stands for application programming interface. It simply means functions and subroutines that belong to an application (for this article, functions that belong to Windows), but are available for use by other outside programs. The link between your Access Basic code and the Win16 API is provided via the Declare statement. The Declare statement provides Microsoft Access with instructions on where to find the API procedure, as well has how to interact with that particular function or subroutine.
The first step to incorporating an API function into your Microsoft Access application is to tell Microsoft Access that you intend to use the API procedure. This is done in the declarations section of a module with the Declare statement. Depending on whether the API function returns a value or not, your Declare statements will always follow one of two formats:
If it does not return a value (it is an API subroutine), use:
Declare Sub globalname Lib library-name [Alias aliasname] [(parameter1,
parameter2,...)] as datatype
If it does return a value (it is an API function), use:
Declare Function globalname Lib library-name [Alias aliasname] [(parameter1,
parameter2,...)] as datatype
Note that the syntax is exactly the same except for the “Sub” or “Function” specification after the “Declare” keyword. To gain a thorough understanding of what is involved in calling the APIs, we'll take a close look at the key components that can follow the Declare Sub/Function syntax by "stepping-through" the creation of a Declare statement for the GetPrivateProfileString API.
After Declare Function, you see globalname, which simply represents the name of the particular procedure that you want to use; for example:
Declare Function GetPrivateProfileString...
(“GetPrivateProfileString” is the global name.)
Lib library-name is the next portion. Lib is short for "Library" and is followed by the actual name of the dynamic-link library (DLL) that contains the procedure that you are calling. This article is concerned with the Win16 API, which consists primarily of three libraries:
Our declaration now looks like the following:
Declare Function GetPrivateProfileString Lib "Kernel.exe"....
(It is not necessary to provide the .EXE extension for the three primary Windows DLLs because of their significance in Windows programming. If you were calling another library besides the above three, however, the extension would be necessary.)
Next is the optional Alias aliasname section. Aliasing an API call allows you to call the API procedure by a different name within Access Basic than the name used in the API library. Why would we want to do this? Several reasons:
I'll talk more about the declaration conflicts later, so let’s look at an example where you must alias an API call in order to make use of the API in Access Basic. If you wanted to use the _lopen API function in Access Basic, you would alias the function as follows:
Declare Function lopen Lib "Kernel" Alias "_lopen" ....
or, stated more generically,
Declare Function "AB_acceptable_name" Lib "Kernel" Alias "True_API_name" ....
Referring back to our GetPrivateProfileString declaration, let's alias it as "GetINIsetting" so we don't have to type "GetPrivateProfileString" every time we need to call the function:
Declare Function GetINIsetting Lib "Kernel" Alias "GetPrivateProfileString"....
Now you can call GetPrivateProfileString by simply calling GetINIsetting in your program.
Note It may help to mentally substitute "is really named" in place of Alias to help keep your declarations correct.
Now, we'll move on to the final component of an API call, the parameter list. The parameter list is the placeholder for the parameters that you will need to pass to the API function or subroutine to allow it to do its job properly. This is probably the most important aspect of using the Win16 API successfully from both the Visual Basic® and Access Basic environments.
The first item of discussion is passing values by reference and by value. When passing parameters, Access Basic defaults to passing by reference. This means that Access Basic tells the function being called where the variable is located in memory (passes a pointer to the memory address). Since the function being called can go to the memory address where the variable resides, it can also modify its value.
If, however, you preface the parameter with ByVal, you are telling Access Basic not to give the memory location of the variable to the function, but to pass a copy of the variable value instead. This protects your variables from being accidentally modified by another function, because the function now knows the variable value only, not where the variable is located in memory.
With that in mind, you need to be aware of an exception to ByVal in regards to calling an API function with a string parameter. Windows API functions are coded in C, and thus you must prepare your string parameters accordingly when interacting with the API. In C, a string is passed by providing the memory address (a pointer) of a string, and having the string tagged at the end with a null (ASCII value of 0) character. The null "terminator" serves to let the function know that the end of the string has been reached. If you pass a string in Access Basic with the ByVal preface, Access Basic will pass the string in a C-style fashion (pointer to a null-terminated string). However, this means that Access Basic is passing the memory location of the string, and hence the function being called can modify it even though we stated ByVal, which normally prevents passing the memory location. While this makes learning the APIs a little trickier, it also provides some benefits. The Windows API cannot pass back a string as a return value to Access Basic; however, since we are passing the memory location of the string, the API can go in and appropriately modify or deposit a string in that memory location and then simply tell us that the string is ready for us to use and how long it now is. It’s a bit indirect, but it does the job if you set things up properly. The most important thing is to be sure to allocate sufficient space via the string you pass in, to ensure that no matter what the size of the string Windows returns, your string’s memory buffer can handle it. The maximum string size that an API will return is 255, so an inefficient but safe way is to declare the string you are sending in as "Dim buffer as string * 255”. Once you make the call, the Windows API will return a value telling you how long the string was that it placed in your memory buffer (the string you passed in as a parameter). Using the return value, you can then clip any excess characters off your string before you use it in your program.
Our complete declaration for GetINIsettings (a.k.a. GetPrivateProfileString) now looks like this:
Note The declarations shown in this article appear on multiple lines because of formatting restrictions. In your own code, an API call must be placed on a single line; your declaration will not work if you break the statement up into multiple lines.
Declare Function GetINIsetting Lib "Kernel" Alias "GetPrivateProfileString"
(ByVal Section$, ByVal Entry$ as Any, ByVal NotFound$, ByVal ReturnedString$,
ByVal Size%, ByVal Filename$) as integer
It’s an eyeful, all right. In just a second we'll focus on each of the parameters and see exactly what they do. An important thing to note about parameter lists and API calls is that when defining your Declare statement, the parameters within that statement are dummy placeholders. Their purpose is to communicate the data that is necessary for the API, but you can call the parameters whatever you want in your Declare statement. For instance, if you go into the Visual Basic Windows API Help and retrieve the Declare statement for GetPrivateProfileString, you will see that the first parameter they list is ByVal lpApplicationName$, and not ByVal Section$ as I have listed. It doesn't matter though.... I'm using Section$ because it tells me that this parameter should be the section of the .INI file I want to look up, and lpApplicationName$ doesn't tell me that as clearly. Use whatever works best for you.
The complete breakdown of the parameters is as follows:
One final point before we discuss how to use our API knowledge in Microsoft Access. In the ReturnString$ parameter above, I noted that it is a "pre-allocated" buffer string. This is extremely important, and that's why I am repeating it: If you do not properly allocate a sufficient string buffer, Windows will return the desired string and continue overwriting beyond your allocated memory space into someone else's memory space. This corrupts the memory, and will promptly result in a GPF. Be sure to allocate 1 more than the maximum size of the string that Windows can return. You can determine the largest string that a given API function will return by consulting the Windows Software Development Kit (SDK), or you can simply use the "inefficient but safe" technique I mentioned earlier and dimension your string to 255, knowing that no API can return a string larger than 255. Thus, "Dim ReturnBuffer as string * 255" will ensure that no matter what API call you use, you will be safe.
All right, let’s put some of these APIs to work and enhance our Microsoft Access applications. The first example is a simple one, but one I have found handy. The caption property of a Microsoft Access form allows you to specify the text that the Windows title bar will display, but it can be set at design time only. What if you want to change the text of the title bar at run time to respond to various events or conditions? Well, with Access Basic you could hide the form, go into design mode, manipulate the property, go back into form mode, and then show the form again, or you could make one simple API call and rapidly change the title bar on the fly. Personally, I'll take the API route:
In your declarations section, you'll place the following Declare statement for the SetWindowsText API:
Declare Sub SetWinTitle Lib "User" Alias "SetWindowText"
(ByVal hWnd%, ByVal Title$)
The only thing that might be confusing in the above declaration is the first parameter hWnd%. All windows in Windows have a unique 16-bit identifier that distinguishes them from all other windows. This "identifier" is termed a handle, and you will make extensive use of handles whenever you use the APIs. You'll also see that I've aliased the subroutine to shorten and clarify the name, so we can call it in Access Basic by SetWinTitle now. Let’s write our appropriate function to implement the API call.
Note Before compiling and running any code containing freshly typed in API calls, be sure to save your work. That way if you end up crashing, you can simply exit Windows and fix the problem, rather than having to redo everything. Also, if you should get a GPF when debugging your API calls, bail out and restart Windows. Otherwise, you may indeed fix the problem with your API call(s), but you will continue to get the GPF error because you are still accessing corrupted memory addresses (the code modules may not have properly closed out and deallocated their memory).
Function ChangeTitlebar (ByVal Myform as string, ByVal NewTitle as string)
as integer
hWnd% = Forms(Myform).hwnd 'Note that Access conveniently retrieves the
'Windows handle for you for forms via the hWnd
'property. Makes it even easier to use the APIs.
SetWinTitle hWnd%, NewTitle
End Function
Now you have a ready-made function (without error-handling, though) that will allow you to dynamically alter the title bar on the fly. As you can see, there's not much code involved—that's the one of the great benefits of using the APIs.
That’s good, but your clients will want to see their company name on the application they just paid you to develop. With Access Basic alone, all they are going to see emblazoned at the top is "Microsoft Access"; with API calls, however, you can easily place your client's company name at the top of your Microsoft Access application and impress them.
In your declaration section, you'll need to add a few more API declarations in addition to the "SetWindowText" we already put in there. You should add:
Declare Function GetActiveWindow Lib "User" () as integer
Declare Function GetClassName Lib "User" (ByVal hWnd%, ByVal ClassName$,
byVal MaxBufferSize%) as integer
Declare Function GetParent Lib "User" (ByVal hWnd%) As integer
Now let’s create our function to search for the handle to the Microsoft Access main window:
Function ChangeAccessTitle (MyTitle as string) as integer
Const ACCWIN = "OMain"
ChangeAccessTitle = FALSE
hWnd% = GetActiveWindow() 'Start from the first window and walk backwards.
If hWnd% <> 0 then
While ((CheckWindowClass (hWnd%) <> ACCWIN) And (hWnd% <> 0))
hWnd% = GetParent(hWnd%)
Wend
Else
Exit function
End if
'Once we've exited we assume we've got the handle to the main Microsoft
'Access window, but we'll check to be sure BEFORE we call the API
If hWnd% <> 0 Then
SetWinTitle hWnd%, mytitle 'We're just using the same API call as in
'the previous function.
ChangeAccessTitle = TRUE
End if
End Function
The following function will call the GetClassName API (using the pre-allocated string technique we discussed earlier), and then trim the results according to the value that the API returns so we can use it properly in our comparisons against "OMain" above.
Function CheckWindowClass (hWnd%)
Const MAXSTRINGSIZE = 255
Dim SafeMemBuffer as string * MAXSTRINGSIZE
If (hWnd% <>0) then
stringlength% = GetClassName (hWnd%, SafeMemBuffer, MaxStringSize)
'Note that return% tells us how long the string is that Windows
'returns. We need to trim off the extra characters to compare it
'properly.
CheckWindowClass = Left$(SafeMemBuffer, stringlength%)
End if
End Function
Now, you can simply change the Microsoft Access title bar to your client's company name in your Autoexec function. As an added bonus, whenever your clients minimize your application, the icon will display your application's name instead of "Microsoft Access" as well. Pretty impressive....
Well, you should now have a solid, fundamental understanding of what's involved in calling the Windows APIs from Access Basic, and have some code samples that you can use. In a future article, we'll discuss passing types to the Windows API, sending/posting messages, Windows constants, and yes, we'll even make use of the extensive GetPrivateProfileString API that I dragged you through to explain the components of an API call. See you next time!