MS-Access / Getting Started

---

SQL Data Types

When you create column names for a table, each column must contain a data type. A data type specifies the type of data a column can store. For example, if you create a column that can only store numbers, you must assign it a specific data type that will only allow numbers to be stored in the column. SQL view supports a variety of different data types. Tables-2 and 3 list data types used in Microsoft Access.

Table-2: Common Microsoft Access data types

Data Type	Description
Numeric:
DECIMAL	An exact numeric data type that holds values from -10^28-1 to 10^28-1.
FLOAT	Stores double-precision floating-point values.
INTEGER	Also called INT. Stores long integers from -2,147,483,648 to 2,147,483,647.
REAL	Stores single-precision floating-point values.
SMALLINT	Stores integers from -32,768 to 32,767.
TINYINT	Stores integers from 0 to 255.
String:
CHAR	A fixed-length data type that stores a combination of text and numbers up to 255 characters.
TEXT	A variable-length data type that stores a combination of text and numbers up to 255 characters. The length is determined by the Field size property. The string can contain any ASCII characters including letters, numbers, special characters, and nonprinting characters.
Miscellaneous:
BINARY	Enables you to store any type of data in a field. No transformation of the data is made in this type of field.
BIT	Used to store one of two types of values. For example, true/false, yes/no, or on/off.
COUNTER	Stores a long integer value that automatically increments whenever a new record is inserted.
DATETIME	Stores date and time values for the years 100 to 9999.
IMAGE	Used to store Object Linking and Embedding (OLE) objects. For example, pictures, audio, and video.
MONEY	Stores currency values and numeric data used in mathematical calculations.
UNIQUEIDENTIFIER	A unique identification number used with remote procedure calls.

Table-3. Additional Microsoft Access data types

Data Type	Description
Currency	Used for monetary calculations.
Memo	Variable-length text field from 1 to 65,536 characters in length.
Number	Numerical data that can be used in all forms of calculations except those dealing with money. The Field size property determines the number of bytes that are used to store the number and, subsequently, the number range.
OLE Object	Any linked or embedded object including such things like images, Excel spreadsheets, Word documents, or virtually anything else.
Yes/No	Boolean values, which have only two states like yes/no, true/false, or on/off.

Note: Some data types do not require a field size.

Looking at the list of above data types the astute reader will ask "How does this list of data types compare to the standard Access data types that appear when building a table manually?" The answer is Microsoft has a very elegant solution to the different types - a list of synonyms (Table-4). In other words, when you manually build a table you are actually using the data types listed above, but you are using the old Access naming conventions.

Table-4. Microsoft Access data types and synonyms

ANSI SQL Data Type	Microsoft Access SQL Data Type	Synonym
BIT, BIT VARYING	BINARY	VARBINARY, BINARY VARYING, BIT VARYING
Not supported	BIT	BOOLEAN, LOGICAL, LOGICAL1, YESNO
Not supported	TINYINT	INTEGER1, BYTE
Not supported	COUNTER	AUTOINCREMENT
Not supported	MONEY	CURRENCY
DATE, TIME, TIMESTAMP	DATETIME	DATE, TIME
Not supported	UNIQUEIDENTIFIER	GUID
DECIMAL	DECIMAL	NUMERIC, DEC
REAL	REAL	SINGLE, FLOAT4, IEEESINGLE
DOUBLE PRECISION, FLOAT	FLOAT	DOUBLE, FLOAT8, IEEEDOUBLE, NUMBER
SMALLINT	SMALLINT	SHORT, INTEGER2
INTEGER	INTEGER	LONG, INT, INTEGER4
INTERVAL	Not supported
Not supported	IMAGE	LONGBINARY, GENERAL, OLEOBJECT
Not supported	TEXT	LONGTEXT, LONGCHAR, MEMO, NOTE, NTEXT
CHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING	CHAR	TEXT(n), ALPHANUMERIC, CHARACTER, STRING, VARCHAR, CHARACTER VARYING, NCHAR, NATIONAL CHARACTER, NATIONAL CHAR, NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING

Example-A

In this example, we'll create a table that demonstrates three data types described above. The following SQL script creates a table named TableOne with three columns (Field1, Field2, and Field3). Each column specifies a different data type (COUNTER, TEXT, and CURRENCY).

    CREATE TABLE TableOne
    (
    Field1 COUNTER (4),
    Field2 TEXT,
    Field3 CURRENCY
    );

In this SQL script, the Field1 column contains a COUNTER data type. The COUNTER data type stores a long integer value that automatically increments whenever a new record is inserted. Notice the number 4 is defined immediately following the COUNTER data type. This number causes the Field1 column to default to 4 and increment thereafter. If you do not specify a number with the COUNTER data type, the column will begin incrementing with the number 1.

The Field2 column contains a TEXT data type. The TEXT data type is a variable-length data type that stores a combination of text and numbers up to 255 characters.

The Field3 column contains a CURRENCY data type.