Microsoft Access 2010 Bible

Chapter 11: Mastering VBA Data Types and Procedures

429

Note
How big is a string variable? As shown in Table 11.1, string variables can contain quite a bit of data. How
much text data could be stored in a single VBA string? The Oxford English Dictionary (OED) is widely
accepted as the definitive reference of the English language. Although not the world’s largest dictionary (the
Dutch Woordenboek de Nederlandsche Taal is considerably longer) the OED is quite impressive. Containing
more than 301,000 main entries, 22,000 pages, and 59,000,000 individual words, the OED is available as a
20-volume printed edition.

As large as it is, the OED is only 540MB of data. A single VBA string variable, therefore, could contain almost
four complete copies of the OED!

When you want to assign the value of an Access field to a variable, you need to make sure that the

type of the variable can hold the data type of the field. Table 11.2 shows the corresponding VBA

data types for Access field types.

TABLE 11.2

Access and VBA Data Types

Access Field Data Type VBA Data Type

AutoNumber (Long Integer) Long

AutoNumber (Replication ID) —

Currency Currency

Computed —

Date/Time Date

Memo String

Number (Byte) Byte

Number (Integer) Integer

Number (Long Integer) Long

Number (Single) Single

Number (Double) Double

Number (Replication ID) —

OLE object String

Text String

Hyperlink String

Yes/No Boolean

Now that you understand variables and their data types, you’re ready to learn how to use them

when writing procedures.