Microsoft Access Development

When coming into a business area, you as a dba may encounter numerous problems with your clients' databases. This is usually due to a lack of formal, in house knowledge. These may have been created for smaller tasks, but years later are now mission critical. Below are some common problems.

• Table normalization - Lack of 3NF leads to update, insert, and delete anomalies.
• Fixed width text fields too large - Datatype of char(255) where a smaller width or varchar should be used.
• Lack of integrity constraints - to implement this effectively, you must work closely with the business unit to determine the business logic to enforce in the database.
  • Referential integrity - foreign keys
  • Domain constraints - ex. An SSN field should be 9*[0-9], a State field should be [A-Z][A-Z]
• Improper grouping in queries - queries created using the query designer tend to group on too many fields. When there is poor key design, this might not be noticed for a long time. With proper keys on tables, improper grouping makes itself more apparent.
• Tables without keys or with arbitrary "id" keys - tables should have keys made up of existing data. Only when absolutely necessary should an arbitrary ID field be added in order to have a key.
• Data types - inconsistency of data types for the same value. Ex. SSN or Group# stored as char in one table, and long in another. This causes comparisons to fail.
• Lack of data preprocessing when importing data from foreign sources - foreign data such as data from clients, TPAs, partners, etc must be properly processed before bringing into our own systems. Ex. Bad parsing of name fields leading to first and last name together in first name field. Names are put together in a number of ways by different systems. Ex. SSN fields stored as text being imported as numbers. This causes leading zeros to drop. Lack of domain constraints allows this bad value to propagate throughout the system.
• Data retention - due to Access' limitations, periodic data may currently be purged. SQL Server migration is one solution. Splitting up the database into multiple .mdb's is another. In our area, monthly data is purged and renewed each month. A period field can be added to relevant tables and multiple months' worth of data could be retained.
• Security - Access supports using special files to allow certain users in, but you may not have the permissions and authority to implement these changes to their systems. Locking down the Access front end is a cumbersome, error prone, and easily bypassed measure. Once someone bypasses the front end and accesses the object browser, they have full reign over the data.