A comment on one of my answers on Stack Overflow brought to my attention a blog article “The Evils of Lookup Fields in Tables” that argues against defining lookup fields in MS Access table definitions. I don’t do nearly as much Access development as I once did, but I had never encountered any serious issues using Lookup fields.

The article is written by Arvin Meyer and Joan Wild, who appear to be part of the Microsoft MVP program.

ms access rowsource not updating-54ms access rowsource not updating-73

Reports based on the lookup field need a combo box to display the data, causing them to run more slowly. If you want to display a value from a related table, the report is going to need to pull it from that other table combo-box or not.

The underlying record source can also be modified to include the table, however the index, (unless it was set up within a proper relationship) may not be optimized. Assuming that the combo-box method is slower than adding a join to the underlying query, so what?

Is it really that confusing if you get an error telling you it doesn’t have permissions on just because it isn’t explicitly in the query? As pointed out in the comments (thanks James L), in my rebuttal to the weak arguments against using lookup fields, I was remiss in not mentioning that there are some legitimate complaints about the implementation of this feature in MS-Access.

I still don’t think the feature should be discarded completely, but it definitely needs to be used with an understanding of its quirks and shortcomings.

My experience predisposes me to give a lot of credibility to MVPs even when I am initially skeptical of something they say.

However, given the full context of their complete argument. First, a quick primer on this handy feature of MS Access for anyone that is not familiar with it.In fact, this feature encourages the use of good database design by giving the developer a way to hide the technical details behind the scenes. Other databases might not support this particular feature to display the data automatically with the cool drop downs, but they can use these fields just like any other.The upsizing thing is irrelevant, despite the fact that you configure this in the table design, it is a UI feature not a data format. If security is implemented, permissions to tables[are] usually denied, and [Run with owner’s permissions] queries are used for data access.There will often be errors that there are no permissions on a specific table that isn’t even being used in a query (because the lookup field is).If the queries are nested or complex, it can take some time to track down the lookup that’s causing the error (that is, if it occurs to you).Although, I have no idea what they mean by “the data in the table can be over-written”, it is true that someone could modify the form design to do things that mess up the data. Rebuttal: You shouldn’t use a feature that simplifies life for the developer and end user because it might bloat the database?