Applecore Pages on Microsoft Access

What are APIs, why use them, how to use them, and how to find out more

What are APIs?
API stands for Application Programming Interface. Essentially, it is a way of interfacing with the Windows environment.
Why use APIs
There are several reasons why you might wish to use APIs instead of or in addition to the built-in VBA functions:
  • Speed - although there might be only a fraction of a millisecond's difference between a VBA function and using an API call, if you are using it repeatedly, then this difference mounts up. A good example of this is recursively searching for a file through the directories and sub-directories;
  • Reliability - you wish to ensure a more reliable application, either to avoid 'DLL Hell', caused by setting a reference to a particular version of a common dll, such as ComCtl32.dll, or a setting that can be 'confused', such as Environ;
  • Extensibility - you wish to perform something that cannot be achieved using VBA functions.
However, there is a steep learning curve with APIs - you are more likely to either crash Access or even the system when testing APIs. Therefore saving your code before you test it is vital. Fortunately, once you have the code working, there shouldn't be any problems.

How to use APIs?
In general, an API is declared as below:

[Private|Public] Declare [Function|Sub] APIName Lib [DLLName] (Alias APIName) (Arguments) (Return Type)

For example:

Private Declare Function apiGetDC Lib "user32" Alias "GetDC" (ByVal hwnd As Long) As Long


This determines the scope of the function of subprocedure. This is mostly a matter of preference. I prefer to declare my API calls private within a module, and then use a function to call them. This allows me to have a module that is stand-alone and can be copied to another database without reliance on other modules.


Whether it is a subprocedure or a function. Nearly all APIs are functions, and they nearly all return a value directly.


The name of the DLL that the procedure is in. For the standard DLLs, user32.dll, kernel32.dll or gdi32.dll you can omit the file extension, but for all other DLLs you must include the file extension.

(Alias APIName):

If you have declared the API as being different from the name that it is known within the DLL you must specify the correct name here. There are several reasons why you may wish to do this:

  • The name of the API is not a valid VBA function name, such as '_lwrite';
  • You are declaring it twice, for example to accept different argument types to get around the 'As Any' variable type;
  • You wish to have a common naming policy for API calls, such as prefixing them all with 'api'
Note that the API name must be in the correct case - 'findfile' is not equal to 'FINDFILE'


As with VBA procedures, APIs may accept various arguments. However, this is one area where care needs to be taken to ensure that you pass ByRef or ByValue as needed. You will often also need to predeclare string arguments to be a certain length. You may also find that you pass a Type Structure as an argument, and the values that you want are in that Type Structure.

(Return Value):

The datatype that the API returns. Normally this will be a Long Integer, with 0 often indicating an error.

How to find out more about them
There are several very good resources for APIs, both written and on the Web:

The Access WebOver 60 APIs set up for use in Access, including using the Windows File Open/Save dialog box and returning the name of the user logged into the Operating System.
Randy Birch's VBNetA VB-orientated site that covers more APIs, but for a VB environment
AllAPI.netA huge site that lists most, if not all, of the Windows APIs in great detail, including a description of the various arguments that are required, as well as examples.
Windows API GuideAnother site dealing with Windows APIs for the VB programmer. Unfortunately, this will no longer be kept current.
VBA Developer's HandbookA companion to the highly regarded Access Developer's Handbook(s) by Getz/Gilbert/Litwin, this covers many of the most useful APIs as far as Access developers will be concerned.
Dan Appleman's Visual Basic Guide to the Win32 APIThis is probably the book about using API calls within a VB environment. It primarily focuses on the main DLLs present in Windows.
Dan Appleman's Win32 API Puzzle Book and Tutorial for VB ProgrammersAnother book by Dan Appleman, this one dealing with how to troubleshoot code with APIs in that doesn't work as expected, and how to interpret the documentation to get the APIs working in VB.



Copyright & Disclaimer


Last modified at 06/06/2006 14:53:13