Microsoft Access Database Problems

by Rebecca Johnson

Microsoft Access, part of the Microsoft Office Professional Suite, is the industry standard for desktop databases. Problems occur with Access when there are poorly designed tables or relationships. There are also times that Access is not the appropriate database tool because there are many users or there are many millions of records. Another reason to not choose Microsoft Access would be if the database needs complex security.

Poor Table Design

Poor table design can either stop a database in its tracks or cause problems after years of use. There are many types of poor table design. One of the major causes of table issues is not having tables meet the three standard forms of normalization. First Normal Form eliminates repeating fields in tables, such as fields called Class 1, Class 2, Class 3. This presents a problem when a student takes a fourth class. The table does not contain a field titled Class 4. It would be better to have two tables--one that tracked students and one related to the Students table that tracked classes. Second Normal Form prevents the same field from appearing in more than one location, which could lead to problems with data entry and querying the data. When the fields are placed in the wrong table, Third Normal Form is broken, and data integrity is not achieved.

Poor Relationship Design

Poor relationship design can also stop a database in its tracks. If the relationships are not properly developed, then sharing data between tables to create queries, data entry forms and reports cannot happen. Also, if tables do not have a primary key set, relationship problems can become apparent after the database has been used for a while.

Too Many Records

Microsoft Access can handle millions of records; however, it does have a limit. Any one table in an Access database should not contain more than 5 million records, or 1 million records that are memo fields. This problem will probably not become apparent until after the database has been in use for a long period of time, and it is diagnosed when adding new data records is slow or the system crashes.

Too Many Users

Another problem with Microsoft Access is that it cannot handle a lot of users. The amount that it can handle varies depending on if the database is split, meaning that there is a "back end" (all of the database structure), and multiple "front ends" (all of the data entry forms and reports). If the database is split, then number of users does not have a significant impact unless the forms and reports need frequent updating; then it becomes cumbersome to update the individual front ends. If the database is not split, then the number of users should be limited to 15.

Need Complex Security

Access has security that can be applied to a database. However, this security system is difficult to apply, and security is usually regulated to access being granted through network securities on the folder that contains the database. If a system requires stronger security, then Access is not the database system to use.

About the Author

Rebecca Johnson has been a public-sector technical trainer since 1996. Along with creating training materials, she specializes in technical writing and how-to documentation for computer software. Johnson is a Microsoft-certified master instructor and holds a bachelor's degree in communicating technology.

Photo Credits

  • photo_camera "Mi primer curro" is Copyrighted by Flickr user: Manuel Cernuda (Manuel Cernuda) under the Creative Commons Attribution license.