13.3. Linking Libraries in Access 2003

There are two ways to link a library to an Access 2003 database; either reference the library, or declare it.

13.3.1. Referencing a Library

When you set a reference to an external library in Access 2003, you can use its functions as though they were built-in to Access. You can reference type libraries, object libraries, and control libraries.

For example, because the Microsoft Excel Object Library is itself a library of functions, you can reference (link to) it in Access and use its functions as though they were part of Access. To reference a library, launch the References dialog box, as shown in Figure 13-3, by selecting References from the Tools menu in the Visual Basic Editor.

Browse the list to see a large range of libraries. Some typical libraries include:

• Microsoft Office 11 Object Library

• Microsoft Excel, Word, and the other members of the Office suite

• Microsoft ActiveX Data Objects 2.7 Library

• Microsoft DAO 3.6 Object Library

• Microsoft Scripting Runtime

• Microsoft SQLDMO Object Library

Of course, many of the libraries you'll find listed in the References dialog box are from suppliers other than Microsoft, and depend on the applications you have installed on your computer. You might find such things as

• Corel—CorelDraw 11.0 Library

• Symantec.Norton.AntiVirus.OfficeAntiVirus 1.0 Type Library

To reference a library, browse the list and check the box next to the library you want to use, then click OK. If you don't see anything you like, click Browse and locate the file.

It's worth noting here that not all the libraries can be used without purchasing a license from the supplier. Others are specifically written for C++ or Visual Basic and cannot be used in Access.

Reference object libraries by selecting ActiveX Control from the Insert menu in form Design View. The same thing can be accomplished by clicking More Controls on Toolbox. Figure 13-4 shows the Insert ActiveX Control dialog box.

When you add a custom control (OCX) to a form in this way, Access adds a reference to it in the References dialog box. For example, adding the Microsoft Calendar Control adds a reference to C:WindowsSystem32mscal.ocx.

13.3.1.1. How Microsoft Access Resolves VBA References

When Access needs to use the file you've referenced, it does so in the following sequence.

If, after doing all this, Access still can't find the referenced file, it generates an error. When you check the References dialog box, you may see a reference marked MISSING, as shown in Figure 13-5

Although Access will warn you about broker references when you display the VBA editor, the following method call, issued in the Immediate window, will return True if a reference is broken, and False otherwise.

?Application.BrokenReference

Appendix B fully explains creating and managing references.

Once a library is linked, you can use its functions as easily as you would a built-in Access function. For example, after linking Excel, the following code demonstrates how to access Excel's InchesToPoints() function, which, as you would expect, converts inches to points.

Public Sub Linking2Excel()
    Debug.Print Excel.Application.InchesToPoints(1)
End Sub

13.3.2. Declaring APIs

The other way to link an external library is to declare it, naturally enough by using the Declare keyword. The Declare statement typically consists of eight parts, and supports both functions and subprograms (subs). You can only declare a procedure at module level (in the Declarations Section).

Here is Syntax 1:

[Public | Private] Declare Sub name Lib "libname" [Alias "aliasname"]
[([arglist])]

And here is Syntax 2:

[Public | Private] Declare Function name Lib "libname" [Alias
"aliasname"] [([arglist])] [As type]

13.3.2.1. The Declare Keyword

The Declare keyword alerts VBA that what follows is the interface definition for a procedure stored in a DLL. The Declare statement also defines the type of procedure being declared: Function or Sub.

As you've already discovered, you can specify that the procedure be either Public or Private, depending on whether you want the procedure to be available to the entire project or only to the module in which it appears. Declare statements made in class modules can only be Private.

13.3.2.2. Naming the Procedure

The name that follows the Declare Function or Declare Sub keyword is the name you'll use to call the procedure from VBA. There is a degree of flexibility here, because this name need not be the actual name of the procedure in the DLL.

As in the following example, you can rename the procedure to almost anything, provided you use the Alias keyword to specify the actual name of the API procedure.

Private Declare Function MySillyProcedureName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, _
    nSize As Long) As Long

The Alias keyword specifies the actual name of the procedure as it appears in the API. You cannot change this, but as we've seen, you can change the Name argument in the procedure declaration.

There are several reasons for renaming an API procedure:

• Some API procedures begin with an underscore character (_), which is illegal in VBA. To get around this, rename the procedure and use the Alias keyword.

• API procedure names are case sensitive, and terribly intolerant of programmers who forget that. VBA, on the other hand, doesn't care one way or the other, so by renaming the procedure, you build in a level of forgiveness.

• Several API procedures have arguments that can accept different data types. Supplying a wrong data type to such a procedure is a good way to get the API angry, because VBA does not check the data types of the arguments you supply. The kind of response you are likely to get by using a wrong data type can range from erroneous data, unexpected application behavior, application hang, or system crash. To avoid type problems, declare several versions of the same procedure, each with a different name and each using arguments of different data types.

• Some Windows APIs have names that are the same as the reserved keywords in Access, such as SetFocus and GetObject. Using these keywords will result in a compile error. So since youcan't rename the Access keywords, the API gets to have a new name.

• Most API procedures that can take string arguments come in two flavors: one for ANSI and one for Unicode. The ANSI version is suffixed by an A, as in the GetUserName (or MySillyProcedureName) example. The Unicode flavor has a W suffix. VBA uses Unicode internally and converts all strings to ANSI before calling a DLL procedure, so you would usually use the ANSI version. But if you need to use both versions in the same project, renaming one or both of them would make sense.

What's Unicode? The Win32 API supports three different characters sets. The first two are the single-byte and double-byte character sets. The single-byte character set is 8-bits wide, and provides for 256 characters. The double-byte character set (DBCS) is also 8-bits wide, but some of its byte values are called DBCS lead bytes, which are combined with the byte that follows them to form a single character. DBCSs provide a sufficient number of characters for languages such as Japanese, which have hundreds of characters. The third type is the 16-bit character set called Unicode, which provides for up to 65,535 characters; enough to support all the characters in all the languages around the world. Lastly, you can create procedure names that conform to your object naming standards.

If you make a mistake when declaring the Alias, VBA won't be able to find the procedure in the DLL, will present a runtime error 453, Can't find DLL entry point GetUserNameB in advapi32, which your error handling code can trap.

13.3.2.3. Specifying the Lib(rary)

The Lib keyword specifies the filename of the library that contains the procedure you're declaring. You declare the filename as a string inside quotes. If VBA can't find the file, it generates a runtime error 53, File not found, which your error handler can also trap.

13.3.2.4. Specifying the Argument List

The API argument list is specified in much the same way as it is in standard VBA subs and functions. However, there are a few rules that you must understand and adhere to when calling API procedures.

13.3.2.5. Passing and Returning Values

Two things are worth notingat this point. First, even API subs can return values and second, the values returned by many API procedures can be quite different from those returned by VBA procedures. Let's deal first with those strange subs that return values.

Now we'll explain how the GetUserName arguments work.

Private Declare Function GetUserName _
    Lib "advapi32.dll" Alias "GetUserNameA" _
    (ByVal lpBuffer As String, _
    nSize As Long) As Long

Private Const MAXLEN = 255

Function GetLoginName() As String
    Dim strUserName As String
    Dim lngSize As Long
    Dim lngReturn As Long

    lngSize = 256
    strUserName = Space(MAXLEN) & Chr(0)

    If GetUserName(strUserName, lngSize) <> 0 Then
        GetLoginName = left(strUserName, lngSize - 1)
    Else
        GetLoginName = ""
    End If
End Function

You'll notice that two arguments are passed to the GetUserName procedure: lpBuffer and nSize. lpBuffer is a fixed-width string storage area that contains the value returned by the procedure. The declaration tells the API where to find the string in memory, and its width (nSize).

Now you might be thinking, "Hang on, ByRef passes a pointer, not ByVal." Normally we would agree, but VBA is somewhat inconsistent in the way it deals with strings.

By default, VBA passes variables ByRef, that is, it passes a pointer to the location in memory where the value can be found, thereby allowing the procedure to modify the actual value. To test this behavior in VBA, create the following two procedures in a standard module.

Public Sub TestByRef()
    Dim intMyValue As Integer

    intMyValue = 1

    Debug.Print "Initial value: " & intMyValue
    ChangeMyValue intMyValue
    Debug.Print "New value: " & intMyValue
End Sub

Private Sub ChangeMyValue(ByRef intSomeValue As Integer)
    intSomeValue = 3
End Sub

Run TestByRef() and you'll see that the value of intMyValue changes. If you modify the ChangeMyValue() procedure to pass intSomeValue ByVal, and re-run TestByRef(), the value doesn't change. In VBA, this is true of strings as well. But when you pass strings to an API, the reverse happens.

The reason is that since a string variable is itself a pointer, passing it to an API ByRef actually passes a pointer to an OLE 2.0 string (a BSTR data type). Generally the only APIs to use this type of string are those that are part of the OLE 2.0 API. Other APIs don't take too kindly to it. Windows API procedures expect strings to be passed as a pointer to a Null-terminated string, that is, the string ends with an ASCII zero character. This is a C language convention. When we pass a string ByVal, VBA converts the string to C language format by appending a Null-termination character. Because the value is a pointer, the DLL can modify the string even though the ByVal keyword is used.

If you fail to specify the ByVal or ByRef keyword, you run the risk of an error 49, Bad DLL calling convention. Also, if you pass a value ByRef when ByVal is expected, or vice versa, the procedure can overrun memory that it shouldn't, and the system can crash.

The other set of rules that must be followed when passing values to API procedures is that of data types, which are discussed in the next section.