Jet Datatypes

Datatypes are an integral part of every application, and a web application is no exception. It's vital that you understand the datatypes used in the Access database that's driving your website, and that you understand how your website will be impacted by those choices.

There are four types of Access datatypes (and this is typical of most RDBMSs):

Text String data can be composed of almost any set of characters. The important thing to remember is that string data doesn't respond to any type of arithmetic operators.

Numeric There are several numeric datatypes, and all store data with some type of number characteristic, meaning that the data can be interpreted by an arithmetic equation.

OLE Object An OLE Object is a picture or an object. The picture or object can contain text or numeric values, but Access doesn't interpret them as such. Access sees only the larger container—that being the picture or object.

Hyperlink This datatype is similar to text, but contains a web address or an e-mail address and has special linking properties. Some might put Hyperlink in the Text category.

Table 9.4 lists the many Jet datatypes and compares them to Access project datatypes. Use the Jet equivalent when creating and manipulating tables and fields using SQL. Keep in mind that comparable datatypes may not be exactly the same. Some may accommodate smaller or larger numbers of characters, require more or less memory, and so on. We're simply noting the datatypes that are the closest and that you'll most likely choose for the same types of data.

Table 9.4: ACCESS, SQL JET, AND PROJECT DATATYPES

Access Datatype

Description

Access Project

Jet Equivalent

Text

Stores up to 255 characters.

VarChar, NvarChar

TEXT

Memo

Stores up to 65,536 characters.

Text

LONGTEXT

OLEObject

Stores pictures or objects up to 2.14GB. Use this datatype when storing documents or large amounts of formatted text.

Image

LONGBINARY

Yes/No

Integers 1 and 0 only (yes/no; true/false).

Bit

BIT[*]

Number: FieldSize = Byte

Any integer value between 0 and 255.

TinyInt

BYTE, TINYINT[**]

Number: FieldSize = Integer

A short integer between –215 and 215-1.

Short, SmallInt

SHORT

Number: FieldSize = Long

A long integer between –231 and 231-1.

Long, Int

LONG

Number: FieldSize = Single

A single-precision floating-point value from –3.40E+38 through 3.40E+38.

Single, Real

SINGLE

Number: FieldSize = Double

A double-precision floating-point value from –1.79E+308 through 1.79E+308.

Double, Float

DOUBLE

Decimal

An exact numeric type that stores values from –10–28 through 1–28–1.

Decimal, Numeric

DECIMAL[**]

Date/Time

A valid time or date. Jet supports dates from the year 100 through 9999.

DateTime, SmallDateTime

DATETIME

Currency

A scaled integer between –263 through 263-1. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. Use Currency to prevent rounding errors

Money, SmallMoney

CURENCY, MOMEY, SMALLMONEY[**]

AutoNumber:FieldSize = Long Integer

.A long integer between –2,147,483,648 and 2,147,483,647

Int(defined as ldentity)

COUNTER, AUTOINCREMENT

Hyperlink

An e-mail integer between -2,147,483,648 and 2,147,483,647

Char, NChar, VarChar, NVaarChar(defined as Hyperlink)

LONGTEXT[***]

Number;FieldSize = ReplicationID

Globally unique identifier (GUID)

. UniqueIdentifier

GUID

[*]Microsoft documentation recognizes the Jet SQL reserved word BOOLEAN, but tests from the SQL window and ADO code both failed

[**]The following aren't available via the SQL window; they require ADO code: TinyInt, Decimal, SmallMoney.

[***]LONGTEXT creates a Memo, not a Hyperlink; currently, there isn't a Jet SQL Hyperlink equivalent.

start sidebar
Choosing the Correct Datatype

One thing most RDBMSs have is plenty of datatypes from which to choose. If you're in doubt as to which datatype is the most efficient and accurate for your data, answering the following questions should help you decide: Will you use the data in any mathematical equations? If so, you definitely need a numeric datatype.

  • How much storage space is required? If text data is large, it may require a memo field instead of a plain text field.

  • Will you want to sort or index the field? You can sort and index both text and numeric values, but you can't sort or index an OLE Object field. Nor can you group on an OLE Object field.

  • Should data sort as numeric or text values? For instance, the numeric values 1, 2, 3, 10, 100, and 200 will sort differently, depending on the datatype. When defined as a numeric datatype, the values sort as 1, 2, 3, 10, 100, and 200. When stored in a text field, these values sort alphanumerically as 10, 100, 2, 200, and 3.

end sidebar



Mastering Dreamweaver MX Databases
Mastering Dreamweaver MX Databases
ISBN: 078214148X
EAN: 2147483647
Year: 2002
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net