Access VBA Macro Programming

The purpose of this chapter is to give you an idea of how you can use API calls within

your code. The examples given are not comprehensive, and you can use large numbers of

API calls for different functions. Some books are available on API calls if you wish to

investigate this topic further.

API calls are normally functions that return a value of some type, although they often take

some action at the same time. Certain subroutines also only take action (remember the

distinction made in Chapter 3 between a function and a subroutine). To use them, you must

first declare the function or subroutine you wish to use; this is the hardest part. TheDeclare

statement sets up the description of the function or subroutine statement within the dynamic

link library (DLL) file. It describes which DLL will be used, what the name of the function or

subroutine is, and what the parameters to be passed are. The declarations are quite complicated,

and if any mistake is made, the call will not work and may even crash the system. Before

making an API call, make sure your file has been saved, because these calls are not very

forgiving when things go wrong. You may find you have to reboot your computer to get

things running again, and this will lose any data you have not saved. As I mentioned, API

calls are not very forgiving if they go wrong, and pressingCTRL+BREAKto stop them will

have no effect whatsoever. For example, simply passing the wrong type of value is enough to

cause a crash. However, they are an example of the wonderful versatility of VBA, and when

used properly, they can provide functionality not normally available in Access.

Using an API Call

The following are some examples of API calls and how to use them within your VBA code.

Getting Disk Space

For this example, you will use an API call that gets the spare disk space from a disk device.

First of all, you must make the declaration. You do this in the declarations section of a module

(at the top of the module page). The syntax for this particular declaration is as follows:

Private Declare Function GetDiskFreeSpaceEx Lib "kernel 32 " _

Alias "GetDiskFreeSpaceExA" (ByVal lpDirectoryName As _

String, lpFreeBytesAvailableToCaller As Currency, _

lpTotalNumberOfBytes As Currency, lpTotalNumberOfFreeBytes _

As Currency) As Long

This is quite a long statement, and it has to be completely accurate to work. If you have access to

Visual Basic, all the API declarations are contained in a file called API32.TXT and can easily be

copied and pasted into your declarations. Further information can be obtained from the Microsoft

Developer Network (MSDN) at msdn.microsoft.com, which is the perfect place to find more

advanced information on Access VBA and API calls.

Basically, this statement sets up a reference to the kernel32.dll that resides in the Windows

system directory. It specifies the way parameters must be used and what they are called. Its

purpose is to put a function into your code that you can use to call this library function from

kernel32.dll.

254 Microsoft Access 2010 VBA Macro Programming