JET Database/Data types

Text types
There are two basic data types, plus a BLOB type, that are used to hold text values in JET databases. Prior to JET 4.0 they could only hold 8-bit text characters, but databases created with an Engine Type code of 5 or greater (i.e. JET 4.0) can also store Unicode characters.

Since JET 4.0, text values that are primarily one byte (8-bit) text characters are stored in two bytes unless otherwise specified. This increases the storage required for all text data, effectively doubling it. JET 4.0 has an additional property that can be set on text columns to compress text data to one-byte characters when it is able to. This property is set by specifying  on each text column that requires it. When a table is created in a JET database using Microsoft Access, text columns are normally created with this setting turned on (as Unicode compression).

Fixed-length text
The  data type is used to store fixed-length text with up to 255 characters. Specifying the number of characters to store limits how big the column will be. Text values retrieved from a  column are padded with spaces, if necessary, to the size of the column.

The  data type is not available from the Access designer.

The following statement creates a table with a 10-character text column and a 255-character text column, both with Unicode compression:

Variable-length text
The  data type is used to store variable-length text with up to 255 characters.Text values retrieved from a   column are trimmed of any trailing spaces.

The following statement creates a table with a 10-character text column and a 255-character text column, both with Unicode compression:

Text BLOB
The  data type is used to store variable-length text with an unspecified number of characters, limited only by the maximum size of JET database files (2 GB – about 1 billion uncompressed Unicode characters).

Some software libraries are able to handle  columns as basic text columns, but others must use BLOB techniques for accessing their data. In particular, the ADO components so often used in Visual Basic, VBA and ASP applications can access  columns as basic text when using the JET 4.0 OLE-DB provider to access the database, but must use BLOB handling routines (GetChunk / AppendChunk) when using an ODBC connection.

The following statement creates a table with two variable-length text BLOB columns, both with Unicode compression:

Text pseudo-types
The  data type is a pseudo-type. When a size is specified, it is taken to be a  and is limited to 255 characters. When no size is specified, it is taken to be a.

The following statement creates a table with a 10-character text column and a variable-length text BLOB column, both with Unicode compression:

Numeric types
There are several basic numeric data types available in JET, allowing for a wide range of data storage possibilities for numeric values.

Byte
The  data type is an 8-bit unsigned integer that can hold values between 0 and 255 inclusive. It can be referred to by the following aliases:,  ,.

It is analogous with the Visual Basic type.

Smallint
The  data type is a 16-bit (2-byte) signed integer that can hold values between -32,768 and 32,767 inclusive. It can be referred to by the following aliases:,  ,.

It is analogous with the Visual Basic type.

Integer
The  data type is a 32-bit (4-byte) signed integer that can hold values between -2,147,483,648 and 2,147,483,647 inclusive. It can be referred to by the following aliases:,  ,  ,.

It is analogous with the Visual Basic type.

Single
The  data type is a 32-bit (4-byte) single precision floating point number that can hold values from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. It can be referred to by the following aliases:,  ,  ,.

It is analogous with the Visual Basic type.

Double
The  data type is a 64-bit (8-byte) double precision floating point number that can hold values from -1.79769313486231E308 to -4.94065645841247E-324 for negative values, and 4.94065645841247E-324 to 1.79769313486232E308 for positive values. It can be referred to by the following aliases:,  ,  ,  ,  ,.

It is analogous with the Visual Basic type.

Currency
The  data type is a 64-bit (8-byte) exact precision decimal number (implemented as a scaled integer) that can hold values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 inclusive. It can be referred to by the following aliases:,.

It is analogous with the Visual Basic type.

Decimal
The  data type is a 17-byte exact precision decimal number that can hold values from -1028-1 to 1028-1. It can be referred to by the following aliases:,  ,.

Boolean (Yes/No)
JET supports a data type called  that can hold one bit of information, used for holding simple Boolean states of true or false. It can only hold the values 0 or 1, but some interfaces into the JET database present these as 0 and -1, or No and Yes. It can be referred to by the following aliases:,  ,  ,.

It is analogous with the Visual Basic type.

Dates and times
The  data type is a 64-bit (8-byte) double precision floating point number that can hold both date and time information, in the range 1 January, 100 to 31 December, 9999 and with optional times in hours, minutes, seconds. It can be referred to by the following aliases:,  ,  , all capable of holding just date, just time, and both date and time information.

It is analogous with the Visual Basic type.

To determine whether a  holds time information, check to see whether the double precision number is the same as the integer truncation of that number; if they differ, then there is time information, otherwise there is no time information.

dt1                    check_time --- - 02/02/2007 16:26:29    -0.314942129632982 02/02/2007              0

Counters
The  data type is an auto-incrementing 32-bit (4-byte) integer, often used for creating surrogate keys. It can be referred to by the following aliases:,  ,.

A  can take optional parameters, defining the initial value to start counting at, and the increment to add each time a new value is created. The following code shows both the default, starting at one with increment of one, and with specified start of 10 and increment of 2. Each table can have, at most, one column of type.

id1        c1 --- -- 1          x 2           y

id2        c2 --- -- 10         x 12          y

BLOBs
JET can store binary large objects, or BLOB data, in the database directly as binary data. Accessing this data typically requires special BLOB handling functions, for example, ADO provides the functions GetChunk and AppendChunk for reading and writing BLOB data.

Binary
The  data type is a variable-length binary object type that can hold up to 510 bytes of binary data. If the size is not specified, then the maximum size of 510 bytes is used. It can be referred to by the following aliases:,.

Longbinary
The  data type is a variable-length binary object type with an unspecified capacity, limited only by the maximum size of JET database files (2 GB). It can be referred to by the following aliases:,  ,  ,.