You are here: Home / Microsoft Office / Office 2007 / Access 2007 / Add Data To the Database / Select the Data Type

Select the Data Type

No matter how you choose to enter your data, data typing is a critical component of building any database. The typing must be consistent with the data that will be entered into the field.

What are the different data types?

Type Description More Information
Text Alphanumeric data (text and numbers) Stores up to 255 characters.
Memo Alphanumeric data (text and numbers) Stores up to 2GB of data (the size limit for all Access databases), if you fill the field programmatically. Remember that adding 2GB of data causes your database to operate slowly.If you enter data manually, you can enter and view a maximum of 65,535 characters in the table field and in any controls that you bind to the field.

When you create databases in the Office Access 2007 file format, Memo fields also support rich-text editing.

 
Number Numeric data Number fields use a Field Size setting that controls the size of the value that the field can contain. You can set the field size to 1, 2, 4, 8, or 16 bytes. 
Date/Time Dates and times Access stores all dates as 8-byte double-precision integers. 
Currency Monetary data Stores data as 8-byte numbers with precision to four decimal places. Use this data type to store financial data and when you don’t want Access to round values.
AutoNumber Unique values created by Access when you create a new record Stores data as 4-byte values; typically used in primary keys. 
Yes/No Boolean (true or false) data. Access uses -1 for all Yes values and 0 for all No values.
OLE Object Images, documents, graphs, and other objects from Office and Windows-based programs Stores up to 2GB of data (the size limit for all Access databases). Remember that adding 2GB of data causes your database to operate slowly. OLE Object fields create bitmap images of the original document or other object, and then display that bitmap in the table fields and form or report controls in your database.For Access to render those images, you must have an OLE server (a program that supports that file type) registered on the computer that runs your database. If you don’t have an OLE server registered for a given file type, Access displays a broken image icon. This is a known problem for some image types, most notably JPEG images.

As a rule, you should use Attachment fields for your .accdb files instead of OLE Object fields. Attachment fields use storage space more efficiently and are not limited by a lack of registered OLE servers.

 
Hyperlink Web addresses Stores up to 1 gigabyte of data. You can store links to Web sites, sites or files on an intranet or Local Area Network (LAN), and sites or files on your computer.
Attachment Any supported type of file New to Office Access 2007 .accdb files. You can attach images, spreadsheet files, documents, charts, and other types of supported files to the records in your database, much like you attach files to e-mail messages. You can also view and edit attached files, depending on how the database designer sets up the Attachment field. Attachment fields provide greater flexibility than OLE Object fields, and they use storage space more efficiently because they don’t create a bitmap image of the original file..

In the Navigation Pane, do one of the following:

  • Double-click the table that you want to investigate, and on the Datasheet tab, in the Data Types & Formatting group, look at the value in the Data Type list. The value changes as you place the cursor in the various fields in your table. This figure shows the list:

or

  • Right-click the table you want to investigate and click Design View.Access opens the table in the design grid, and the upper section of the grid shows the name and data type of each table field. This figure shows a typical table in the design grid.

Note:   In Office Access 2007, you can set the data types for your table fields by working in either Datasheet view or Design view. Steps in this section explain how to set the data type in both views. When you set data types in Design view, you see an 11th choice, Lookup Wizard. That choice is actually not a data type. Instead, you use the Lookup Wizard to create lookup fields, which link foreign key fields to other tables. By default, Access sets lookup fields to the Number data type.

Finally, data types provide a basic form of data validation because they help ensure that users enter the proper types of data in your table fields. For example, you cannot enter text in a field set to accept only numbers.