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