Access MVP (2010-2015)
As you can see, this table not only holds Access Data Types but SQL Server table Data Types. When I create an Access database I always have to take into consideration whether it will be upsized to an SQL Server in the future. So, I only use Data Types that are available in an SQL Server table. Below is table showing Access Data Types and there corresponding Data Types in an SQL Server table. You will note not all fields have a match… that is because they do not exist in an SQL Server table and therefore you will not find them in any of my databases.
| Data Type | Description | Storage | SQL Server | Additional Notes | |
| Text (up to Access 2010) |
Use for text or combinations of text and numbers. 255 characters maximum | up to 255 characters | NVACHAR (max) CHAR (size) VARCHAR (size) TINYTEXT |
||
| Memo (up to Access 2010) |
Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable | 64k | NVACHAR (max)VARCHAR (size) TEXT BLOB |
||
| Number: Byte | Allows whole numbers from 0 to 255 | 1 byte | TINYINT | ||
| Number: Integer | Use for integers that range from -32,768 to 32,767. Storage requirement is 2 bytes. | 2 bytes | SMALLINT | ||
| Number: Long Integer | Allows whole numbers between -2,147,483,648 and 2,147,483,647. Tip: Use Long Integer when you create a foreign key to relate to another table’s AutoNumber primary key field. | 4 bytes | INT | ||
| Number: Single | Single precision floating-point. Will handle most decimals (Decimal Precision 7) | 4 bytes | REAL INT |
||
| Number: Double | Double precision floating-point. Will handle most decimals. Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 (Decimal Precision 15) | 8 bytes | FLOAT |
9.18.2013 BIGINT removed… Turns out BIGINT translates to TEXT when linking to an Access Table which causes #Deleted to show in all the fields in the Linked Table(s) | |
| Number: Replication ID (guid) | Use for storing a globally unique identifier required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format. | UNIQUEIDENTIFIER | |||
| Number: Decimal | Use for numeric values that range from -9.999… x 1027 to 9.999… x 1027. (Decimal Precision 28) | 12 bytes | FLOAT | ||
| Currency | Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. | 8 bytes | MONEY | ||
| AutoNumber | AutoNumber fields automatically give each record its own number, usually starting at 1 | 4 bytes | INT COUNTER |
||
| Date/Time | Use for dates and times | 8 bytes | DATETIME() | ||
| Yes/No | A logical field can be displayed as Yes/No, True/False, or On/Off. Note: Can be empty which translates to NULL when upsizing. | 1 bit | BIT | Note: Null values are not allowed if you plan to edit and/or edit data in the table | |
| Ole Object | Can store pictures, audio, video, or other BLOBs (Binary Large OBjects) | up to 1GB | VARBINARY (max) BLOB |
||
| Hyperlink | Contain links to other files, including web pages | up to 64,000 characters | |||
| Lookup Wizard (Access 2003 and up) | Let you type a list of options, which can then be chosen from a drop-down list | 4 bytes | |||
| Attachment (Access 2010 and Access 2013) | Use an attachment field to attach multiple files, such as images, to a record | There is no Attachment Data Type in SQL Server. The only suggestion I can offer here is FILESTREAM Storage. | |||
| Calculated (Access 2010 and Access 2013) | See Allen Browne’s Tips for Casual Users – Calculated Fields | ||||
| Short Text (Access 2013) |
Use for text or combinations of text and numbers. 255 characters maximum | up to 255 characters | NVACHAR (max) CHAR (size) VARCHAR (size) TINYTEXT |
||
| Long Text (Access 2013) |
See MEMO field | 64k | NVACHAR (max)VARCHAR (max) BLOB |
Last updated July 30, 2013
5,105 total views, 1 views today
