Data types used in microsoft access

MS Access — Data Types

MS Word Online Training

32 Lectures 2.5 hours

MS Excel Online Training

102 Lectures 10 hours

MS Word: Create Long Documents Like a Pro

17 Lectures 3.5 hours

Every field in a table has properties and these properties define the field’s characteristics and behavior. The most important property for a field is its data type. A field’s data type determines what kind of data it can store. MS Access supports different types of data, each with a specific purpose.

  • The data type determines the kind of the values that users can store in any given field.
  • Each field can store data consisting of only a single data type.

Here are some of the most common data types you will find used in a typical Microsoft Access database.

Type of Data Description Size
Short Text Text or combinations of text and numbers, including numbers that do not require calculating (e.g. phone numbers). Up to 255 characters.
Long Text Lengthy text or combinations of text and numbers. Up to 63, 999 characters.
Number Numeric data used in mathematical calculations. 1, 2, 4, or 8 bytes (16 bytes if set to Replication ID).
Date/Time Date and time values for the years 100 through 9999. 8 bytes
Currency Currency values and numeric data used in mathematical calculations involving data with one to four decimal places. 8 bytes
AutoNumber A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. 4 bytes (16 bytes if set to Replication ID).
Yes/No Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off). 1 bit.

If you use previous versions of Access, you will notice a difference for two of those data types.

In Access 2013, we now have two data types — short text and long text. In previous versions of Access these data types were called text and memo.

The text field is referred to as short text and your memo field is now called long text.

Here are some of the other more specialized data types, you can choose from in Access.

Files, such as digital photos. Multiple files can be attached per record. This data type is not available in earlier versions of Access.

Up to about 2 GB.

OLE objects can store pictures, audio, video, or other BLOBs (Binary Large Objects)

Up to about 2 GB.

Text or combinations of text and numbers stored as text and used as a hyperlink address.

Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters).

The Lookup Wizard entry in the Data Type column in the Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field.

A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.

Dependent on the data type of the lookup field.

You can create an expression that uses data from one or more fields. You can designate different result data types from the expression.

You can create an expression that uses data from one or more fields. You can designate different result data types from the expression.

These are all the different data types that you can choose from when creating fields in a Microsoft Access table.

Источник

SQL data types

Applies to: Access 2013, Office 2013

The Microsoft Access database engine SQL data types consist of 13 primary data types defined by the Microsoft Jet database engine and several valid synonyms recognized for these data types.

The following table lists the primary data types. The synonyms are identified in Microsoft Access Database Engine SQL Reserved Words.

1 byte per character

Any type of data may be stored in a field of this type. No translation of the data (for example, to text) is made. How the data is input in a binary field dictates how it will appear as output.

Yes and No values and fields that contain only one of two values.

An integer value between 0 and 255.

A scaled integer between – 922,337,203,685,477.5808 and 922,337,203,685,477.5807.

DATETIME (See DOUBLE)

A date or time value between the years 100 and 9999.

A unique identification number used with remote procedure calls.

A single-precision floating-point value with a range of – 3.402823E38 to – 1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.

A double-precision floating-point value with a range of – 1.79769313486232E308 to – 4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.

A short integer between – 32,768 and 32,767. (See Notes)

A long integer between – 2,147,483,648 and 2,147,483,647. (See Notes)

An exact numeric data type that holds values from 1028 — 1 through — 1028 — 1. You can define both precision (1 — 28) and scale (0 — defined precision). The default precision and scale are 18 and 0, respectively.

2 bytes per character (See Notes)

Zero to a maximum of 2.14 gigabytes.

Zero to a maximum of 2.14 gigabytes. Used for OLE objects.

2 bytes per character (See Notes)

Zero to 255 characters.

  • Both the seed and the increment can be modified using an ALTER TABLE statement. New rows inserted into the table will have values, based on the new seed and increment values, that are automatically generated for the column. If the new seed and increment can yield values that match values generated based on the preceding seed and increment, duplicates will be generated. If the column is a primary key, then inserting new rows may result in errors when duplicate values are generated.
  • To find the last value that was used for an auto-increment column, you can use the following statement: SELECT @@IDENTITY. You cannot specify a table name. The value returned is from the last table, containing an auto-increment column, that was updated.

Источник

Data types for Access desktop databases

When creating tables in Access, you need to select a data type for each column of data. The Short Text data type is a popular choice since it lets you enter almost any character (letter, symbol, or number). However, careful selection of data types can help you take advantage of more Access features (such as data validation and functions), and improves the accuracy of the information you’re storing. The table below gives an overview of the data types available in Access desktop databases (.accdb and .mdb).

For a comprehensive list of all the various field properties available for the various data types, see Introduction to data types and field properties.

The following table lists the data types available in desktop databases in Access 2013 and later versions.

Short Text (formerly known as “Text”)

Alphanumeric data (names, titles, etc.)

Up to 255 characters.

Long Text (formerly known as “Memo”

Large amounts of alphanumeric data: sentences and paragraphs. See The Memo data type is now called “Long Text” for more information on the Long Text details.

Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.

1, 2, 4, 8, or 16 bytes.

Dates and times.

Dates and times.

Encoded string of 42 bytes

Monetary data, stored with 4 decimal places of precision.

Unique value generated by Access for each new record.

4 bytes (16 bytes for ReplicationID).

Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.

Pictures, graphs, or other ActiveX objects from another Windows-based application.

Up to about 2 GB.

A link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on your local computer

Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters).

You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn’t available in MDB file formats.

Up to about 2 GB.

You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. Note, the Calculated data type isn’t available in MDB file formats.

Dependent on the data type of the Result Type property. Short Text data type result can have up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their respective data types.

The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.

Dependent on the data type of the lookup field.

The following table lists the data types available in desktop databases in Access 2010 and Access 2007.

Alphanumeric data (names, titles, etc.)

Up to 255 characters.

Large amounts of alphanumeric data: sentences and paragraphs.

Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.

1, 2, 4, 8, or 16 bytes.

Dates and times.

Monetary data, stored with 4 decimal places of precision.

Unique value generated by Access for each new record.

4 bytes (16 bytes for ReplicationID).

Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.

Pictures, graphs, or other ActiveX objects from another Windows-based application.

Up to about 2 GB.

A link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on your local computer

Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters).

You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn’t available in MDB file formats.

Up to about 2 GB.

You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. Note, the Calculated data type isn’t available in MDB file formats.

Note: The Calculated data type is not available in Access 2007.

Dependent on the data type of the Result Type property. Short Text data type result can have up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their respective data types.

The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.

Dependent on the data type of the lookup field.

Источник

Типы данных Microsoft Access

В следующей таблице показаны типы данных Microsoft Access, типы данных, используемые для создания таблиц и типов данных ODBC SQL.

Data Types Description Size
Тип данных Microsoft Access Тип данных (CREATETABLE) Тип данных SQL ODBC
BIGBINARY[1] LONGBINARY SQL_LONGVARBINARY
BINARY BINARY SQL_BINARY
BIT BIT SQL_BIT
СЧЕТЧИК СЧЕТЧИК SQL_INTEGER
CURRENCY ДЕНЕЖНАЯ ЕДИНИЦА SQL_NUMERIC
ДАТА и ВРЕМЯ DATETIME SQL_TIMESTAMP
GUID GUID SQL_GUID
LONG BINARY LONGBINARY SQL_LONGVARBINARY
ДЛИННЫЙ ТЕКСТ LONGTEXT SQL_LONGVARCHAR[2] SQL_WLONGVARCHAR[3]
ПАМЯТКА LONGTEXT SQL_LONGVARCHAR[2] SQL_WLONGVARCHAR[3]
NUMBER (FieldSize= SINGLE) ОДНОГО SQL_REAL
NUMBER (FieldSize= DOUBLE) DOUBLE SQL_DOUBLE
NUMBER (FieldSize= BYTE) НЕПОДПИСАННЫЙ БАЙТ SQL_TINYINT
NUMBER (FieldSize= INTEGER) SHORT SQL_SMALLINT
NUMBER (FieldSize= LONG INTEGER) LONG SQL_INTEGER
NUMERIC NUMERIC SQL_NUMERIC
OLE LONGBINARY SQL_LONGVARBINARY
TEXT VARCHAR SQL_VARCHAR[1] SQL_WVARCHAR[2]
VARBINARY VARBINARY SQL_VARBINARY

[1] Только приложения Access 4.0. Максимальная длина 4000 байт. Поведение, аналогичное LONGBINARY.

[2] Только приложения ANSI.

[3] Только приложения Юникода и Access 4.0.

SQLGetTypeInfo возвращает типы данных ODBC. Он не возвращает все типы данных Microsoft Access, если несколько типов Microsoft Access сопоставлены с одним и тем же типом данных ODBC SQL. Все преобразования в приложении D справочника программиста ODBC поддерживаются для типов данных SQL, перечисленных в предыдущей таблице.

В следующей таблице показаны ограничения типов данных Microsoft Access.

Тип данных Описание
BINARY, VARBINARY и VARCHAR Создание столбца BINARY, VARBINARY или VARCHAR нулевой или неопределенной длины фактически возвращает 510-байтовый столбец.
BYTE Несмотря на то, что поле Microsoft Access NUMBER с fieldSize, равное BYTE, не имеет знака, отрицательное число можно вставить в поле при использовании драйвера Microsoft Access.
CHAR, LONGVARCHAR и VARCHAR Строковый литерал символов может содержать любой символ ANSI (десятичное число 1–255). Используйте две последовательные одинарные кавычки (‘) для представления одной кавычки (‘).

Процедуры следует использовать для передачи символьных данных при использовании любого специального символа в столбце символьного типа данных.

DATE Значения даты должны быть разделены в соответствии с каноническим форматом даты ODBC или разделителями datetime («#»). В противном случае Microsoft Access будет рассматривать значение как арифметическое выражение и не будет вызывать предупреждение или ошибку.

Например, дата «5 марта 1996 г.» должна быть представлена как или #03/05/1996#; В противном случае, если отправлено только 03.05.1993, Microsoft Access будет оценивать это значение как 3, разделенное на 5, разделенное на 1996. Это значение округляется до целого числа 0, а с нулевого дня соответствует 1899-12-30, используется дата.

Символ канала (|) нельзя использовать в значении даты, даже если они заключены в обратные кавычки.

GUID Тип данных ограничен Microsoft Access 4.0.
NUMERIC Тип данных ограничен Microsoft Access 4.0.

Дополнительные ограничения на типы данных можно найти в разделе «Ограничения типов данных».

Источник

Читайте также:  Wordpress как поменять рубрику
Поделиться с друзьями
КомпСовет
Adblock
detector