Microsoft Office Tutorials and References
In Depth Information
Choosing Field Types
Storing pictures and other files
Access provides two field types that allow you to store entire files of
information, usually pictures. The old type, OLE Object, provides a link to the file,
but unless the files are small, doing so turns out to be a bad idea. The
database reacts to a large OLE object the way an anaconda reacts to trying to
swallow a rhino: Its size balloons. Instead, you can use an Attachment field,
which compresses the file before storing it in your database.
You have another option: Don’t store the file in your database at all. If your
pictures are large, if they change frequently, or if you use them for other
purposes and need to store them as separate files anyway, store the pathname
that leads to the files containing the pictures. In the retail-store example
earlier in this chapter, the Products table includes a Product Photo field.
Instead of making that field an Attachment field, you can store all the pictures
in a separate folder on the hard drive and store filenames for each picture
in a Text field or a hyperlink. If the pictures are in various folders, store the
entire pathname in the field, as in the following example:
The disadvantage of this method is that if you move your database to another
computer, you need to move all these files, too, so that the pathname is the
same on the new computer as it was on the old one. You can prevent this
problem by storing your photos in the cloud on SkyDrive — a cloud storage
facility that’s being introduced in Office 2013.
Storing names, money, codes, and other stuff
Here are a few other field-type suggestions:
✦ People’s names: For lists of people, creating a Name field and putting
full names in it is tempting. Don’t do it! You’ll want to sort records by
last name, create listings with last name first, or otherwise fool with the
format of people’s names. Create separate First Name and Last Name
fields. You may even want Middle Name and Salutation (such as Mr.
and The Reverend) fields.
✦ Phone numbers and postal codes: Use Text fields rather than Number
fields, even if you plan to type only digits in the field. The test to use is
this: Is there any chance that you’d ever want to do math with this
information? If the answer is no, use a Text field. (If you store a zip code in a
Number field, Access feels compelled to drop leading zeros, so the zip
code for Middlebury, VT turns from 05753 to 5753 — not good.)
✦ Money: Use a Currency field rather than a Number field. Calculations
made on Currency fields are faster than those made on most Number