Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

Data Types

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

880 total views, 1 views today