Microsoft Access Field Consistency Tips

Access allows you to easily define a field in any table with a variety of property settings such as the field type, field size (for text fields), description, format, input mask, table lookup, whether it allows zero length strings, whether it’s required, etc.

Fields containing the same information should have the same field properties, or at least the most important field properties. This would begin with field name. If you have a CustomerID field which is an AutoNumber in the customer table, and is used by other tables, using the same CustomerID name would be very helpful to show it refers to the same data. Avoid using other names such as CustID, Customer, Customer ID, etc. Whoever needs to maintain your application in the future will appreciate your consistency.

One would presume that data could be freely moved among identically named fields. However, this may not be the case if the fields are not consistently defined. In particular, the data type and size (for text fields) should be identical.

For instance, the CustomerID field, which is an AutoNumber, should be a Long Integer in the other tables. A CustomerName field, which is 50 characters in the customer table, should be the same size in other tables. Failure to do so may result in data being truncated if it is moved from the larger field to a smaller one in an Append query, or may crash if you do this in code.

There are other field properties where it is are important to be consistent but tend to be less problematic because developers rarely set them or they are not inconsistent as often. These properties include:

Allow Zero Length Strings

If this setting for text fields is set to Yes, the field may contain Nulls or zero length strings. If you move a zero length string to a field that doesn’t allow this, you’ll encounter a crash. We recommend all text fields allow zero length strings.

Required

Leaving a required field blank could be a problem.

Unicode Compression

If you are dealing with Unicode data (associated with two byte international characters), you need to make sure your settings are consistent to avoid moving Unicode data into fields that cannot handle them.

In addition to documenting table structures and a wide variety of database analysis, Total Access Analyzer from FMS, Inc., performs a detailed field consistency check for Access databases.

This analysis flags identically named fields which do not share consistent data types and sizes. When an inconsistency is found, all the tables where the field name exists are listed along with their corresponding field description, data type, and size. This simplifies the process of cleaning up the structures and minimizing the problems associated with inconsistent field names.

For SQL Server, FMS offers Total SQL Analyzer Pro that provides server and database documentation along with field consistency analysis for tables within each database.

After data normalization, consistently named fields is the foundation of good database design. Making sure the same field name represents the same data and can always store the same data not only makes good design sense, it simplifies the understanding of your database and eliminates problems associated with moving data between tables. Good luck with your database development.

Author: Luke Chung, President and Founder of FMS, Inc. FMS, Inc. is the leading provider of 3rd party products for Microsoft Access users and developers. Visit the FMS web site for additional Access resources, utilities, technical papers, demos, and product information.