Part of the process of creating tables in an Access database is ensuring that each field has the appropriate data type. If the field type does not reflect the type of data entered into the field, then whenever the database is sorted, and when searches and queries are performed, erroneous results will be produced. Thus, for example, if you have a field called "date_of_birth" and you define it as a text field, you will still be able to input data into it. However, when you sort on this field, you will get an alphabetical sort, whereby "11/4/1969" will be placed before "12/7/1989".There are 9 different data types to choose from in Access 2007 and each time you create a field, you need to pause for a second and think about the data it will contain and the range that that data will fall into. The Access data types are Auto Number, Number, Currency, Text, Memo, Date/Time, Yes/No, Hyperlink, Attachment and OLE Object.
The Auto Number data type is a specialised long integer which is used for primary key fields which need to contain a unique numerical value. As the name suggests, you cannot directly enter a value into a field defined with this data type; Access automatically assigns a new value each time a record is created.
The portmanteau Number field type should be used for all fields which contain values which are definitely numeric in nature such as a person's salary. Having chosen this data type, you can then specify a Field Size value in the Field Properties section of the table design view. Thus, if you had a field called "width", you might choose integer which allows you to input values up to approximately 32,000.
When defining fields which will store monetary values, you should consider using the Currency data type rather than the more general Number type. Currency is designed to perform accurate calculations on money-related figures. It allows 15 figures to the left of the decimal, and 4 to the right of, the decimal point.
The Text and Memo fields are both used for fields which will contain alphanumeric characters, such as "description", "telephone" or "firstName". The Text data type will accept a maximum of 255 characters while the Memo type can accept up to approximately 65,000 characters.
| Data Type | Used to store | Limitations/Restrictions |
|---|---|---|
| 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. |
As in Microsoft Excel, Access treats dates and times as specialized number fields. The Date/Time data type is designed to accurately hold dates and times. Sorting, calculations and queries performed on this type of field will give the correct chronological results.
The Yes/No field type allows you to store Boolean (True or False) values. Examples where this field might be useful include "internal", "approved", "inStock", "female", etc.
With the advent of the web, database fields that contain URLs are fairly common. The Hyperlink data type is used to create a Microsoft style hyperlink, as found in Word and Excel. You can right-click on this field and choose Edit Hyperlink from the context menu to display the standard Microsoft dialog for editing hyperlink properties.
Access 2007 has two data types which can be used for storing binary data such as images and other documents: Attachment and OLE Object. Attachment is the more efficient of the two. As the name suggests, it works in a similar way to the attachments you add to an email. Access embeds the attached file in the table so that it becomes part of your database, increasing the file size accordingly. The OLE Object data type can be used in much the same way but is less efficient and exists principally for backward compatibility.
0 comments:
Post a Comment