General
Use a naming convention
As trivial as this sounds, it is one of the most important things that you can do to help yourself. You exponentially increase the complexity of development if you do not use a naming convention. Access wizards compound this problem. For example, if you create a table called “Customers” and use a wizard to create a query based on that table, the query will also be called “Customers”. The same holds true for any form or report that is based on that table or query, and was produced by a wizard. When you begin writing expressions or code and you refer to “Customers”, you will most certainly make some mistakes, because you have four objects in you database with the same name. You can use any naming convention that you feel comfortable with, as long as it make a clear distinction between object types, and clearly identifies the purpose of each object. The gold standard for MS Access naming conventions is the “Leszynski Development Framework”. A complete explanation is available at the Access 97 Expert Solutions website Leszynski Naming Conventions for Access. Your first act after creating a new form or report, should be to re-name all of the controls on that object using your chosen naming convention. Combo24 (a typical name provided by Access) tells you that this control is an Access combo box, however, it provides no information about what the box contains. A more concise and helpful name would be cboCustomer. In addition to using less characters to identify the object type (3 versus 5), it also tells us what information the control contains.
Table design comes first.
I see many new developers creating forms and reports before solidifying their table structure. This is a recipe for failure. Forms and reports are based on a rigid table structure. If you need to make significant changes to the underlying table structure after you’ve built forms and reports, you will have to make major changes to those objects, or redo them from scratch. Make sure that your data is fully normalized, and that you have established the appropriate relationships. Set referential integrity for all relationships to prevent orphaned records.
Use Access Autonumbers for primary keys
Much of the documentation on relational database design insists that the actual data be used as a primary key for each table. This attitude comes from purists and mathematicians, not developers. In truth, there are many reasons NOT to use data for primary keys.
- Many times, multiple fields will be required to create a unique key. This will adversely affect performance, since multiple joins will be required every time a child table is joined to this table in a query. This also complicates your work as a developer, since you need to deal with these joins whenever you work on a form, report or query which includes these tables.
- When using data, the values combined to form a unique key are oftem text fields. Again, this introduces a performance compromise, since Access can join, sort and filter much faster on a single numeric value, than it can on multiple text values.
- Data changes. As sure as you are that something as finite as a social security number will never change, you must allow for data entry errors. If you were to use a piece of data such as this, and it needed correction, you run the risk of orphaning child records if relational integrity is not enabled.
User Interface
Be judicious in your use of colors and fonts
Generally speaking, it is a bad idea to use different font faces and sizes. Consistency throughout the application will give it a more polished appearance. If you want some color on your forms, pick a muted color, and use it throughout your application. Avoid mixing different colors on the same form, and certainly do not use colors that clash.
Limit Scrolling
Listboxes and continuous forms are the only things that should scroll, and they should only scroll vertically. NOTHING SHOULD EVER SCROLL HORIZONTALLY! This is the worst design faux pas, and it is unfortunately far too common. There are numerous techniques that can be used as an alternative to horizontal scrolling.
Control your controls
The number of controls that are placed on a form should not be determined by the size of your monitor. This is another common mistake; putting a control for every field in a table on the same form. Sometimes this is ok, and sometimes it’s not. The user shouldn’t be overwhelmed by any form or report. Instead, provide users with a means to see just the information they need, not ALL of the information at once. Keep your form sizes consistent throughout the app, and keep them small. If you need to show more than will fit on the form, use tabs to organize controls into groups, and/or use pop-up forms to provide drill-down capability.
Macros and VBA
Don't use macros
Macros are popular with beginning Access users because they are easy to create, and extend Access’ built-in capabilities. Although they have a place as a prototyping tool, or for temporary use by a developer, macros should never be used in a finished software application. Macros have no error-handling capability. Your users can find themselves abruptly ejected from your program, with no hope of recovery. In some cases, they may even have to close Access. Professional Access developers use VBA (Visual Basic for Applications) code exclusively. VBA offers much more power than Macros, and provides for robust error-handling. If you have already created some macros, all is not lost. Access has a built-in tool for converting macros to VBA code. This option can be used as a training tool for anyone familiar with macros, trying to learn VBA. The only caveat is that the conversion tool writes very poor VBA code. A seasoned developer’s code will almost always be more efficient and safer than the code produced by the conversion wizard. As a general rule, any of the code written by the Access wizards is substandard.