I've worked with Microsoft Access for nearly 15 years now and I still love it as a database platform. Over the years I've worked with Oracle, MySQL and a great deal with Microsoft SQL Server. As a result, I've become a "lowest common denominator" style programmer. I've avoided the "fancy" platform-specific things wherever possible, and so far it's served me well.
That said, I think the special data types in Microsoft Access are well worth looking at if you're sure you won't be upsizing to another database engine in the future. I mean, it would be a real bummer to try to migrate to SQL Server, where the user interface depends on the database to deliver presentation and formatting functionality. But if your application is to remain in Microsoft Access, then these fields are going to save you a lot of time and trouble.
Introduction to Fancy Data Types
The screen shot below shows what you get when you activate the drop-down box on the Click to Add field that shows up as the right-most table column. At the top are the old faithful types: Text, Number, Currency, Date & Time, Yes/No and Memo. The fancy ones, which we are about to consider, include the following:
o Rich Text
o Lookup & Relationship
o Calculated Field
o Paste as Fields
Paste as Fields
Of these fancy options, the Paste as Fields was the most interesting to me, so I started there. Rather than read the help file, I assumed it simply does what its name implies. Not surprisingly, it does! To test it, I opened Notepad and typed in a header row, followed by two rows of data implementing numeric, text and date type data. I copied the text from my Notepad file, clicked on the Paste as Fields option and voila, my table sported three new fields and two new rows.
I frequently copy and paste data between Excel or SQL Server and Access. In Access 2003, it was easy enough to do that into a new datasheet, but there were limits. You could get 10 columns and 22 rows of data, but the column headers were simply discarded and the columns were named Field1, Field2, etc.
Using the Paste as Fields option, I added 224 fields with 1061 rows, and the column names were perfectly preserved. Field data types are also correctly assigned. This is one of the most valuable new options I've seen in Microsoft Access in some time.
Common but Useful Types
The Hyperlink Field
In the image below, you'll see I added three other types of fields: Hyperlink, Attachment and Rich Text. The Hyperlink is pretty much what you'd expect. Put something akin to a web address in there and it will "linkify" it for you. That is, its behavior is changed so that, when clicked, an action is executed that attempts to open the address. Below I have several different URLs and yes, o.co is a real web address. Clicking on any of these opens the associated web page. You can also insert email addresses and the action associated with the click invokes whatever default mail program you have assigned as default on your system.
If you copy the contents of a Hyperlink column and paste it into Notepad, you might be surprised with the results. For the column shown below, I get the following text:
As you can see, Access has reformatted the data and added tags to make the click action possible. While this is great for the presentation layer, mining data from this column programmatically requires that you manipulate the text a bit.
The Attachment Field
The Attachment field allows you to save multiple files in your database. Again, this isn't something you can upsize to SQL Server if you ever decide to migrate, but if you stick with Access as the data store, this field might prove very valuable for you. The image shows the Attachment dialog box, which sports my three files. The dialog box exposes commands to Add, Remove or Open any of the stored files.
The Rich Text Field
Unlike a standard Memo field, the Rich Text field allows you to save formatted text, as shown in the screen shot below. It's still a Memo field in that there is no text size limit, but now you have the ability to apply text formatting to part or all of the text it contains. In addition to being able to use the Text Formatting commands on the ribbon, a pop-up menu appears when you click in the box and/or select a portion of the text therein.
This could be very useful and it will save you time when preparing forms for the presentation layer, but again, don't plan on getting this kind of support in SQL Server.
Uncommon and New Types
Calculated fields fly in the face of all that is normal. They break the purist's rules for database design. Still, if you're building a little Access application for yourself and don't care about the rules of normalization, you'll probably find a use for this field.
Below is one example based on those three records I pasted in: Age, Name and DOB. I used the Expression Builder to create a calculated field that reads:
[FirstName] is [Age] years because date of birth is [DOB]
Notice how the Expression Builder helped me out with Intellisense, supplying a list of fields I might select from. The new field, Field2 in my table, now displays this concatenation of text. While this example might not be very useful, you might want something like this to multiply Quantity and Unit Price to get an Extended Price. We usually do this in a query, but with Calculated Fields, you can now add this type of field to the table so you build it once and forget about it.
Lookup & Relationship Fields
Access has always allowed for fields to perform a lookup to another table. This is convenient when you want to store something like a StateID, but display the State Name or Code from a lookup table. The difference with this new field is that you can, by simply checking an option box, allow for multiple lookups.
In the example below, I point the field to my lookup table that contains days of the week, plus a few extra for Friday, since it always seems like it takes forever for Fridays to end. By making this column a Multi-Select field, Access gives you the user interface shown where multiple options may be selected. If you build a form from this table, this custom drop down interface is preserved. You need not write a line of code to expose this rather complex functionality. The selected options then display in the field, separated by commas.
Parts is Parts
The last data type/field feature we'll examine was formerly called "Application Parts." You can explore these by clicking on the More Fields drop down from the Table Tools | Fields ribbon area. You'll find options like Name, Address, and Phone. These build an array of columns with the most commonly used parts, like First and Last names, etc. Unfortunately, each text field defaults to 255 characters, the max allowed. I'd rather it implement some reasonable standard, but I suppose there would always be an argument about what was correct. I use 24 characters for First Name and 32 for Last Name, but who says my convention should be the standard.
Other options include things like Category, Payment Type and Status. In these cases, Access does try to anticipate what values should be included. For example, Payment Type comes preloaded with these options: "Cash";"Credit Card";"Check";"In Kind". Even if it's not what you'd choose as options, the fact is that some of your work has been done for you, saving you time and adding a level of consistency.
I like anything that saves me time and clicks of the mouse. Honestly, I use these data types when I build little helper tools for myself, but I've never implemented them in a production application. Should I have? I don't know. I enjoyed playing with them for this article and I believe they have their place, but I'm still cautious when implementing them.