About differences between data types in an Access database and Access project

ShowAbout Access database data types

Decide what kind of data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) to use for a field based on these considerations:

ShowChoosing between a Text or Memo field

Microsoft Access provides two field data types (field data type: A characteristic of a field that determines what kind of data it can store. For example, a field whose data type is Text can store data consisting of either text or numeric characters, but a Number field can store only numerical data.) to store data with text or combinations of text and numbers: Text and Memo.

Use a Text data type to store data such as names, addresses, and any numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. A Text field can store up to 255 characters, but the default field size is 50 characters. The FieldSize property controls the maximum number of characters that can be entered in a Text field.

Use the Memo data type if you need to store more than 255 characters. A Memo field can store up to 65,536 characters. If you want to store formatted text or long documents, you should create an OLE Object field instead of a Memo field.

Both Text and Memo data types store only the characters entered in a field; space characters for unused positions in the field aren't stored.

You can sort or group on a Text field or a Memo field, but Access only uses the first 255 characters when you sort or group on a Memo field.

ShowChoosing between a Number or Currency field

Microsoft Access provides two field data types (field data type: A characteristic of a field that determines what kind of data it can store. For example, a field whose data type is Text can store data consisting of either text or numeric characters, but a Number field can store only numerical data.) to store data containing numeric values: Number and Currency.

Use a Number field to store numeric data to be used for mathematical calculations, except calculations that involve money or that require a high degree of accuracy. The kind and size of numeric values that can be stored in a Number field is controlled by setting the FieldSize property. For example, the Byte field size will only store whole numbers (no decimal values) from 0 to 255 and occupies 1 byte of disk space.

Use a Currency field to prevent rounding off during calculations. A Currency field is accurate to 15 digits to the left of the decimal point and 4 digits to the right. A Currency field occupies 8 bytes of disk space.

Number and Currency fields provide predefined display formats (format: Specifies how data is displayed and printed. An Access database provides standard formats for specific data types, as does an Access project for the equivalent SQL data types. You can also create custom formats.), or you can create a custom format.

ShowChoosing between an incremented or random AutoNumber field

Microsoft Access provides the AutoNumber data type to create fields that automatically enter a unique number when a record is added. Once a number is generated for a record, it can't be deleted or changed. An AutoNumber field can generate three kinds of numbers: sequential numbers that increment by one, random numbers, and Replication ID (also referred to as GUIDs — globally unique identifiers) numbers. AutoNumbers that increment by one are the most common kind of AutoNumber and are a good choice for use as a table's primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.). Random AutoNumbers will generate a random number that is unique to each record within the table. Replication ID AutoNumbers are used in database replication (database replication: The process of creating two or more special copies (replicas) of an Access database. Replicas can be synchronized, changes made to data in one replica, or design changes made in the Design Master, are sent to other replicas.) to generate unique identifiers for synchronizing replicas (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replica are sent and applied to the other replicas.).

ShowAbout Access project (SQL Server) data types

Objects that contain data have an associated data type that defines the kind of data (character, integer, binary, and so on) the object can contain. The following objects have data types:

  • Columns in tables and views.
  • Parameters in stored procedures.
  • Variables.
  • Transact-SQL functions that return one or more data values of a specific data type.
  • Stored procedures that have a return code, which always has an integer data type.

Assigning a data type to an object defines four attributes of the object:

  • The kind of data contained by the object. For example, character, integer or binary.
  • The length of the stored value, or its size. The length of an image, binary, and varbinary data type is defined in bytes. The length of any of the numeric data types is the number of bytes required to hold the number of digits allowed for that data type. The length of the character string and Unicode data types is defined in characters.
  • The precision of the number (numeric data types only). The precision is the number of digits the number can contain. For example, a smallint object can hold a maximum of 5 digits; it has a precision of 5.
  • The scale of the number (numeric data types only). The scale is the number of digits that can be stored to the right of the decimal point. For example, an int object cannot accept a decimal point and has a scale of 0. A money object can have a maximum of 4 digits to the right of the decimal point and has a scale of 4.

For example, if an object is defined as money, it can contain a maximum of 19 digits, 4 of which can be to the right of the decimal. The object uses 8 bytes to store the data. The money data type therefore has a precision of 19, a scale of 4, and a length of 8.

All data stored in SQL Server must be compatible with one of these base data types. The cursor data type is the only base data type that cannot be assigned to a table column. It can be used only for variables and stored procedure parameters.

User-defined data types can also be created, for example:

-- Create a birthday datetype that allows nulls.
EXEC sp_addtype birthday, datetime, 'NULL'

            
-- Create a table using the new data type.
CREATE TABLE employee
emp_id char(5)
emp_first_name char(30)
emp_last_name char(40)
emp_birthday birthday
            

User-defined data types are always defined in terms of a base data type. They provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. This can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type.

ShowComparison or mapping of data types between an Access database and Access project

The following table compares data types (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) between a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.) and a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.).

Microsoft Access data type SQL Server data type
Yes/No (Yes/No data type: A field data type that you use for fields that will contain only one of two values, such as Yes or No and True or False. Null values are not allowed.) bit (bit data type: In an Access project, a data type that stores either a 1 or 0 value. Integer values other than 1 or 0 are accepted, but they are always interpreted as 1.)
Number (Number data type: In a Microsoft Access database, a field data type designed for numerical data that will be used in mathematical calculations. Use the Currency data type, however, to display or calculate currency values.) (Byte) tinyint (tinyint data type: In an Access project, a data type of 1 byte (8 bits) that stores whole numbers in the range of 0 through 255.)
Number (Integer) smallint (smallint data type: In an Access project, a data type of 2 bytes (16 bits) that stores whole numbers in the range of -2^15 (-32,768) through 2^15 - 1 (32,767).)
Number (Long Integer) int (int data type: In an Access project, a data type of 4 bytes (32 bits) that stores whole numbers in the range of -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).)
Number (Single) real (real data type: In an Access project, an approximate numeric data type with seven-digit precision. It can hold positive values from approximately 1.18E - 38 through 3.40E + 38, negative values from approximately -1.18E - 38 through -3.40E + 38, or zero.)
(no equivalent) bigint (bigint data type: In an Access project, a data type of 8 bytes (64 bits) that stores whole numbers in the range of -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).)
Number (Double) float (float data type: In an Access project, an approximate numeric data type with 15-digit precision. It can hold positive values from approximately 2.23E - 308 through 1.79E + 308, negative values from approximately -2.23E - 308 through -1.79E + 308, or zero.)
Currency (Currency data type: In a Microsoft Access database, a data type that is useful for calculations involving money or for fixed-point calculations in which accuracy is extremely important.)

money (money data type: In an Access project, a data type that stores monetary values in the range -922,337,203,685,477.5707 through 922,337,203,685,477.5807, with accuracy to a ten-thousandth of a monetary unit.)

smallmoney (smallmoney data type: In an Access project, a data type that stores monetary values from -214,748.3648 to 214,748.3647, with accuracy to a ten-thousandth of a monetary unit. When smallmoney values are displayed, they are rounded up to two decimal places.)

Decimal/numeric (decimal data type (Access database): An exact numeric data type that holds values from -10^28 - 1 through 10^28 - 1. You can specify the scale (maximum number of digits) and precision (maximum total number of digits to the right of the decimal point).)

decimal (Decimal data type (Access project): An exact numeric data type that holds values from -10^38 - 1 through 10^38 - 1. You can specify the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point).)

numeric (numeric data type: In an Access project, an exact numeric data type that holds values from -10^38 - 1 through 10^38 - 1. You can specify the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point).)

Date/Time (Date/Time data type: An Access database data type that is used to hold date and time information.)

datetime (datetime data type: In an Access project, a date and time data type that ranges from January 1, 1753, to December 31, 9999, to an accuracy of three-hundredths of a second, or 3.33 milliseconds.)

smalldatetime (smalldatetime data type: In an Access project, a date and time data type that is less precise than the datetime data type. Data values range from January 1, 1900, through June 6, 2079, to an accuracy of one minute.)

AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) (Increment) int (int data type: In an Access project, a data type of 4 bytes (32 bits) that stores whole numbers in the range of -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).) (with the Identity property defined)
Text (Text data type: In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the FieldSize property, whichever is less.) (n)

varchar(n) (varchar (n) data type: In an Access project, a variable-length data type with a maximum of 8,000 ANSI characters.)

nvarchar(n) (nvarchar(n) data type: In an Access project, a variable-length data type with a maximum of 4,000 Unicode characters. Unicode characters use 2 bytes per character and support all international characters.)

Memo (Memo data type: In a Microsoft Access database, this is a field data type. Memo fields can contain up to 65,535 characters.) text (text data type: In an Access project, a variable-length data type that can hold a maximum of 2^31 - 1 (2,147,483,647) characters; default length is 16.)
OLE Object (OLE Object data type: A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database.) image (image data type: In an Access project, a variable-length data type that can hold a maximum of 2^31 - 1 (2,147,483,647) bytes of binary data. It is used to store Binary Large Objects (BLOBs), such as pictures, documents, sounds, and compiled code.)
Replication ID (also called globally unique identifier (GUID: A 16-byte field used in an Access database to establish a unique identifier for replication. GUIDs are used to identify replicas, replica sets, tables, records, and other objects. In an Access database, GUIDs are referred to as Replication IDs.) (GUID)) uniqueidentifier (uniqueidentifier data type: In an Access project, a 16-byte globally unique identifier (GUID).) (SQL Server 7.0 or later)
Hyperlink (Hyperlink data type: A data type for an Access database field that stores hyperlink addresses. An address can have up to four parts and is written using the following format: displaytext#address#subaddress#.) char (char data type: In an Access project, a fixed-length data type with a maximum of 8,000 ANSI characters.), nchar (nchar data type: In an Access project, a fixed-length data type with a maximum of 4,000 Unicode characters. Unicode characters use 2 bytes per character and support all international characters.), varchar, nvarchar (With the Hyperlink property set to Yes)
(no equivalent) varbinary (varbinary data type: In an Access project, a variable-length data type with a maximum of 8,000 bytes of binary data.)
(no equivalent) smallint (smallint data type: In an Access project, a data type of 2 bytes (16 bits) that stores whole numbers in the range of -2^15 (-32,768) through 2^15 - 1 (32,767).)
(no equivalent) timestamp (timestamp data type: In an Access project, a data type that is automatically updated every time a row is inserted or updated. Values in timestamp columns are not datetime data, but binary(8) or varbinary(8), indicating the sequence of data modifications.)
(no equivalent)

char

nchar

(no equivalent) sql_variant (sql_variant data type: In an Access project, a data type that stores values of several data types, except for text, ntext, image, timestamp, and sql_variant types. It is used in a column, parameter, variable, or return value of a user-defined function.)
(no equivalent) user-defined (user-defined data type: In a Microsoft SQL Server database, a definition of the type of data a column can contain. It is defined by the user, and based on existing SQL Server data types. Rules and defaults can only be bound to user-defined data types.)

 Note   In an Access project or SQL Server database, the "n" prefix stands for "national" and means that the data type is unicode-enabled. In an Access database, all text columns are unicode-enabled by default.

 
 
Applies to:
Access 2003