JET Database/Data types

Quick Reference

JET type	VBA type	Size	Values
byte, tinyint, integer1	Byte	1 byte	integers 0 to 255
smallint, short, integer2	Integer	2 bytes	integers -32,768 to 32,767
integer, int, long, integer4	Long	4 bytes	integers -2,147,483,648 to 2,147,483,647
single, real, float4, IEEESingle	Single	4 bytes	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
double, number, double precision, float, float8, IEEEDouble	Double	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
currency, money	Currency	8 bytes	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
decimal, numeric		17 bytes	fixed precision decimal numbers
counter, identity, autoincrement	Long	4 bytes	auto-incrementing integer for surrogate keys
bit, yesno, logical, logical1	Boolean	1 bit	0 or 1 (may appear as 0 or -1 sometimes)
datetime, date, time	Date	8 bytes	1 January, 100 to 31 December, 9999 with optional time information
text, varchar	String	<= 255 characters	variable-length text strings, with Unicode support since JET 4.0
char	String	<= 255 characters	fixed-length text strings, with Unicode support since JET 4.0
longchar, memo	Memo	<= 2 GB	variable length text BLOB
binary, binary varying	Binary	<= 510 bytes	fixed-length binary data
longbinary, image, general, oleobject	Binary	<= 2 GB	variable length BLOB
guid	Binary	16 bytes	uniqueidentifier

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 WITH COMPRESSION 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 char 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 char column are padded with spaces, if necessary, to the size of the column.

The char 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:

Create Table T1 (c1 char(10) with compression, c2 char with compression)

Variable-length text

The varchar data type is used to store variable-length text with up to 255 characters.Text values retrieved from a varchar 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:

Create Table T2 (c1 varchar(10) with compression, c2 varchar with compression)

Text BLOB

The longchar 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 longchar 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 longchar 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:

Create Table T3 (tb1 longchar with compression, tb2 memo with compression)

Text pseudo-types

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

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

Create Table T4 (c1 text(10) with compression, tb1 text with 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 byte 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: byte, tinyint, integer1.

It is analogous with the Visual Basic type Byte.

Smallint

The smallint 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: smallint, short, integer2.

It is analogous with the Visual Basic type Integer.

Integer

The integer 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: integer, int, long, integer4.

It is analogous with the Visual Basic type Long.

Single

The single 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: single, real, float4, IEEESingle.

It is analogous with the Visual Basic type Single.

Double

The double 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: double, double precision, number, float, float8, IEEEDouble.

It is analogous with the Visual Basic type Double.

Currency

The currency 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: currency, money.

It is analogous with the Visual Basic type Currency.

Decimal

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

Boolean (Yes/No)

JET supports a data type called bit 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: bit, logical, logical1, yesno.

It is analogous with the Visual Basic type Boolean.

Dates and times

The datetime 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: datetime, date, time, all capable of holding just date, just time, and both date and time information.

It is analogous with the Visual Basic type Date.

To determine whether a datetime 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.

Create Table D1 (dt1 datetime)
go
Insert Into D1(dt1) Values (#2007-02-02#)
go
Insert Into D1(dt1) Values (#2007-02-02 16:26:29#)
go
Select dt1, CDbl(dt1) - CLng(dt1) As check_time From D1
go

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

Counters

The counter 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: counter, autoincrement, identity.

A counter 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 counter.

Create Table A1 (id1 counter, c1 char(1))
go
Create Table A2 (id2 counter(10, 2), c2 char(1))
go
Insert Into A1(c1) Values('x')
go
Insert Into A1(c1) Values('y')
go
Insert Into A2(c2) Values('x')
go
Insert Into A2(c2) Values('y')
go
Select * From A1
go
Select * From A2
go

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 binary 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: binary, binary varying.

Longbinary

The longbinary 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: longbinary, general, image, oleobject.