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