9 Signs that an Amateur Built Your Access Application

When we’re called in to look at an existing application, we’re always curious to see how well it is built. A few key things can tell us whether we’re dealing with a pro or an amateur. If we see any of these trouble signs, we know the developer isn’t very experienced. You can look for the same problems in your Access application.

1. The application isn’t split between a front-end application and back-end database.
   • Every Access application should be split into two files. Period. The benefits are many, and the extra effort is minimal. Relinking tables is easy with the free J Street Access Relinker. A separate copy of the front-end application should be on each user’s local PC (or user folder if using Citrix/RDP), not shared among users.
2. There are spaces or special characters in the tables and field names.
   • Although Access will tolerate some pretty bad naming, it doesn’t mean you should. Tables and field names with spaces and special characters are harder to work with and may cause problems when your database is upsized to another platform like SQL Server. We recommend names like ContactFirstName and OrderCreateDate.
3. There is no relationships diagram, no relationships are defined between the tables, or they don’t have RI enforced.
   • Every back-end database should have a nicely organized diagram of all the tables, with relationship lines drawn between them and referential integrity enforced. If you don’t see all these things, you must question the organizational skills of the developer, and worse, the integrity of the data.
4. There are queries or tables with temporal words (like “Jan2014”) in them.
   • If you see these kinds of queries or tables, you are probably dealing with a developer who doesn’t understand how to apply selection criteria dynamically. Any query can be supplied a Where clause in code, without needing to be saved under a special name.
5. Reports or forms cause a parameter dialog box to pop up.
   • Parameter prompts are unwieldy, repetitive, and prone to data entry error. Seeing them in a production application is another indication that the developer doesn’t know simple techniques to apply criteria using an easy-to-use form.
6. The application doesn’t compile.
   • If the Access application doesn’t compile (after checking obvious things like the References list), it’s an indication that the developer was just sloppy and left broken or non-functional code in there.
7. The reference list includes early binding to Office programs like Word or Excel.
   • Experienced developers know that late binding is the professional way to automate other programs because it allows the application to handle the other programs not being installed. Early binding is rigid and prone to failure. Late binding takes a bit more effort, but pros know it’s worth it.
8. Error handling is missing.
   • Let’s put it this way – errors are always handled. If your developer doesn’t, Access will – probably in an undesirable way. A pro makes sure that unexpected errors are trapped and handled as gracefully as possible. If error handling is missing in a production application, it indicates sloppiness or laziness.
9. Option Explicit isn’t specified in every module.
   • Option Explicit at the top of each module ensures that any variables that are used must be explicitly defined using a Dim statement. If it’s missing, variable names can be made up on the fly and used, often leading to errors that are difficult to debug. A pro adds Option Explicit to every module.