Automating Microsoft Access with VBA

You probably know that VBA is not the only programming language that can be used by Windows applications. In fact, there are hundreds of alternatives, including C#, C++, Visual Basic .NET, and others. You can't use the other languages within an Access application like VBA, but they do show the variety of programming languages available for Windows.

Although the various computer languages offer different ways of doing things, they all share one key feature: they all work on Windows. This is made possible by a layer of software called the Windows Application Programming Interface, or Windows API. The Windows API, supplied by Microsoft, contains thousands of functions for performing tasks such as drawing windows, sending information over networks, and so on.

Sometimes a particular part of the Windows API isn't available directly through a particular programming language. For example, VBA doesn't enable you to directly retrieve the name of the computer where the code is running, even though that function is part of the Windows API. But VBA provides something almost as good: a general-purpose mechanism that enables you to call procedures from the Windows API, usually referred to as API calls. This chapter shows you the basics of this mechanism.

Using a Windows API call is a two-step process. First, you need to declare the API call that you're going to use. This sets up a connection between VBA and the underlying Windows software. After that, you can call the procedure like any other procedure.

NOTE

Calling the Windows API directly is a rather advanced topic, and many developers might never need to use this facility. But we suggest that you at least skim this chapter so that you understand the power that is available if and when you need it.

For example, the Windows API call to retrieve the name of the local computer is named GetComputerName. Here's how you can declare it in the declarations section of a VBA module:

Declare Function GetComputerName _ Lib "kernel32" Alias "GetComputerNameA" _ (ByVal lpBuffer As String, nSize As Long) _ As Long

This declaration tells VBA a number of things:

• The name of the function in VBA code is GetComputerName.

• The actual function is contained in a Windows library named kernel32.

• The name of the function within the Windows library is GetComputerNameA.

• The function takes two arguments, one String and one Long.

• The function returns a single Long value.

TIP

The VBA help files don't contain any information on Windows API calls. Instead, you have to go to the Windows Platform Software Developers Kit, usually just called the Platform SDK. The entire Platform SDK is available online. You can find documentation on GetComputerName, for example, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sysinfo/base/getcomputername.asp.