I’m getting better at MS Access design. The nice thing about Access is that you have your toolkit right there in front of you and once you get to know that well, working with Access is mainly a matter of form design/structure. Database schema design itself is not that hard, but with a very complex underlying table structure, determining the best way to efficiently present the information on a set of forms so that it’s editable, straightforward to use and looks nice is often a challenge. The tools we have available are things like Tab Controls which let a lot of data fit into a smaller space, Subforms which enable the display of data from multiple tables on one form, and combo and list boxes for navigation and lookups. Often all of these need to be dynamic: tabs hide or show themselves based on criteria in the table containing them, the list of items in the combo box needs to be updated based on other fields, and so on.

Another limitation I run up against in Access is that the program becomes unstable as forms become complex. If I embed a subform into a form where the subform itself already has a subform, then there seems to be a significant chance that the program will crash when switching from Form View to Design View. However, sometimes the use of nested subforms is necessary to achieve a certain interface. With three levels of nesting, at least with a particular design I tested, a crash was inevitable so I had to try something else. Fortunately Access seems stable enough when just running, and not switching to Design View to edit forms, so I can deal with the crashes.

By “stable enough” above I don’t mean that Access is a good platform for storing the actual data, however. It’s easy for a database file to become corrupted and irreparable, especially when run off of a network drive and in a multiuser environment. Frequent backups are important. So Access is a very poor database, but it’s a great tool to use for creating complicated graphical interfaces to data, quickly. For the actual database (data storage and database schema), use a solid database server such as MySQL or SQL Server. I’ve been using the former for quite some time, and despite having to work around a few quirks (which I’ll write about next time), it’s a good combination of programs.