Microsoft Access Tips for xBase developers

Provided by Allen Browne, allenbrowne.com

Data Types in Access

Field types in Access are somewhat different to those in xBase. Here are your choices.

Text. Essentially the same as the Character type in xBase. 255 character maximum. You can and should define a width, but Access doesn't use fixed width storage so is not as wasteful of disk space.

Number. This category includes Byte (unsigned 8-bit), Integer (signed 16-bit), Long (signed 32-bit), Single (signed 32-bit), and Double (signed 64-bit). Access XP introduces the Decimal (96-bit fixed point, scaled), but there is no corresponding data type in VBA (so use Variant).

Currency. A 64-bit fixed point number, designed to give the accuracy needed for financial data. 15 digits of whole dollars, plus four decimal places (hundredths of a cent).

Counter. (Access 1 and 2 only.) A Long integer type, automatically assigned by Access. To look up a Counter in a one-to-many relationship, use a Long for the field type in the related table.

AutoNumber. (Access 95 onwards.) The AutoNumber replaces the Counter as an automatically assigned number. It can be either a Long integer type, or a ReplicationID (see below), and can be sequential or random. To look up an AutoNumber in a one-to-many relationship, the foreign key must be the same type (Long or ReplicationID). For a simple database, use a sequential Long AutoNumber.

ReplicationID. (Access 95 onwards.) Use this field type only for a foreign key field that looks up a ReplicationID AutoNumber. The ReplicationID is designed to enable off-line replica databases at different sites to assign primary key values without the likelihood of duplicates begin assigned. Also called a GUID (Globally Unique IDentifier). If less than 100 new records will normally be added between synchronizations of the replica databases, this type will probably not be needed.

Date/Time. Access stores Dates as 8-byte floating point numbers, where the integer part refers to the day, and the fraction part refers to the time. While Now() + 1/24 is theoretically one hour from now, use the DateAdd() function to avoid rounding errors if you plan to use the result in comparisons. When constructing queries, keep in mind that if a Date/Time field contains a fraction part, it will not match a criteria which is just a date: Now() is not equivalent to Date(), though Int(Now()) is equivalent to Date().

Yes/No. A Logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False, equivalent to -1 and 0 respectively. Unfortunately, Access 95 onwards do not permit Null values in Yes/No fields.

Memo. Similar to a Memo field in xBase. Up to 64K of text. Memos cannot include formatting (e.g. bolding a word). However, they are searchable.

OLE Object. Use this type to store pictures, audio, video, or other BLOBs (Binary Large OBjects). Double-clicking fires up the associated application. Access 2 and later support in-place activation, e.g. WinWord documents can be stored and edited as OLE objects in a form (though Access will not be able to search them).

Hyperlink. (Access 97 onwards.) Use this type to store the location of a file on your computer, a local network, or the world wide web. The field contains three parts separated by pound signs (#), with each part up to 2K in size. The first part is the text to be displayed, the second is the address of the file, and the third can reference a named section within the file. Example:

    Access Tips#http://allenbrowne.com/tips.html#

Visual Basic data types are not identical to these field types. In modules, Access supports only the following types:

• boolean (Access 95 onwards. Use Integer in Access 1 and 2),
• byte (Access 95 onwards. Use Integer in Access 1 and 2),
• integer,
• long,
• single,
• double,
• currency,
• string,
• object,
• variant,
• date (Access 95 onwards. Use Variant in Access 1 and 2).

Variants are very flexible: if the contents look numeric they can treated arithmetically or as strings. For this reason, Access uses ampersand (&) as a concatenation operator. Under certain circumstances, you will need to specifically type-cast variants with CInt(), CStr(), CDec(), CDate(), etc. This is especially true of dates in international settings.

Only variants can contain the values Null, Empty, or Error. In Access a Null is a non-entry and is not the same as a zero-length string. Use IsNull() to test for Nulls, or Nz() to assign an assumption for Nulls.

User-defined types are permitted in all versions, but only in Access 95 and later can user-defined types be based on other user-defined types. The StrConv() function provides powerful string conversions (Access 95 onwards).