SQL Data Types |
Microsoft Access Data Types |
| Data Type | Use and Description | Storage Size |
| AutoNumber | Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added. | 4 bytes or 16 bytes for Replication ID (GUID). |
| Byte | Allows whole numbers from 0 to 255 | 1 byte |
| Currency | Use for currency values and to prevent rounding off during calculations. | 8 bytes |
| Date/Time | Use for dates and times | 8 bytes |
| Double | Double precision floating-point. Will handle most decimals | 8 bytes |
| Hyperlink | Use for hyperlinks. A hyperlink can be a UNC path or a URL. | Up to 2048 Characters |
| Integer | Allows whole numbers between -32,768 and 32,767 | 2 bytes |
| Long | Allows whole numbers between -2,147,483,648 and 2,147,483,647 | 4 bytes |
| Lookup Wizard | Use to create a field that allows you to choose a value from another table or from a list of values using a combo box—-choosing this option in the data type list starts a wizard to define this for you. Requires the same storage size as the primary key that corresponds to the Lookup field—- |
Typically 4 bytes |
| Memo | Use for lengthy text and numbers, such as notes or descriptions. Cannot be sorted on. | 63,999 Characters |
| Number | Numeric data to be used for mathematical calculations, except calculations involving money (use Currency type). | 1, 2, 4, or 8 bytes. 16 bytes for Replication ID (GUID) only. |
| OLE Object | Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE protocol. | up to 1GB (Limited by diskspace) |
| Single | Stores numbers from –3.402823E38 to –1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values. |
4 bytes |
| Text | Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. | Stores up to 255 characters. |
| Yes/No | Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null values are not allowed. | 1 bit |
Numeric Field Sizes
| Data Type | Use and Description | Storage Size |
| Byte | Stores numbers from 0 to 255 (no fractions). | 1 byte |
| Integer | Stores numbers from –32,768 to 32,767 (no fractions). | 2 bytes |
| Long Integer | (Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). | 4 bytes |
| Decimal | Stores numbers from -10^38 -1 through 10^38 -1 (.adp) Stores numbers from-10^28 -1 through 10^28 -1 (.mdb) |
12 bytes |
| Single | Stores numbers from –3.402823E38 to –1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values. | 4 bytes |
| Double | Use for hyperlinks. A hyperlink can be a UNC path or a URL. | 8 bytes |
| Replication ID | Globally unique identifier (GUID) | 16 bytes |
Related SQL Sample Code:
|
|