Checklist: The Best Access Applications
Good. Better. BEST. Is your Access application as good as it can be?
For this final checklist, I'm going to write about some professional software development practices that are too often missing when writing Access applications. These may be overkill for the personal database you wrote to manage your stuffed animal collection. But if you are writing and/or maintaining business-critical Access applications for paying clients, these techniques will help you build more reliable software.
- Use version control.
- Track bugs in a bug database.
- Don't develop new code against production data.
- Write automated tests.
- Script database schema changes.
- Create setup files that install dependencies.
- Store data in a client-server RDBMS.
Version control
If you want to be taken seriously as a software developer in 2020, you need to use version control. Full stop. This includes Access development.
Implementing version control in Access is more difficult than most other programming languages. The single large binary file nature of an Access project does not lend itself to easy versioning at a granular level. That said, there are options available.
Commercial options include OASIS-SVN and Ivercy. Alternatively, you can use the undocumented SaveAsText and LoadFromText methods to roll your own solution. A good starting point for this approach is this StackOverflow answer, which I expanded upon and documented in an earlier series of articles.
Bug database
Nobody writes bug-free code. Relying on users to self-report errors is endearing in its naïveté. Users won't read a twelve-word message box; you expect them to read your error message and then take time out of their day to tell you about it? You're adorable.
I'm reminded of the story of the Microsoft usability test in which an error message was displayed saying, "There is a $50 bill taped to the bottom of your chair." There was. It was still there at the end of the day.
- Joel Spolsky
Given that reality, what can you do about it? You need to automate the process of logging those errors. This can be an error logging table in your back-end database, a web-based application like FogBugz, or some other system. The important part is that you don't rely on users to find and report your system's errors. Because that ain't gonna happen.
Don't develop against production
But if you do, wear a giant blue foam cowboy hat to do it:
At my first real dev job you had [to] wear a giant blue foam cowboy hat when working directly on a production database. The whole office got tense when that thing came out.
— ben stein (@mrben522) December 10, 2020
Automated testing
I have a confession to make. I write very few proper unit tests in my code. Instead, I write doc tests (which are similar) and integration tests (which are different). Why is that? Three words: Return on Investment.
Doc tests are amazing ROI because the investment side of the equation is almost zero. I write integration tests for "high-leverage" scenarios where the "return" side of the ROI equation is so high that it justifies the significant investment needed to develop the tests.
Script schema changes
Changing your database schema is an inevitable byproduct of ongoing application development. It can also be scary as hell. This is especially true if you're trying to coordinate schema changes with updates to the application code. The only sane way to do that is to apply changes to a development copy of the database and test the new code there first.
Of course, testing your changes on a development copy of the database does you no good if you can't reproduce those exact schema changes on your production database. And the only way to ensure you can reliably do that is to script the changes so you can run them with the same single button press against both development and production.
Setup files
If you will be distributing your Access front-end to customers or other users, you will want something more professional than manually copying an .accdb/.accde to the workstation. This is especially true if your application relies on any third-party libraries, ActiveX controls, or the like.
Again, there are a few options here. Inno Setup is a freeware script-based installer that creates professional-looking setup files and allows for a lot of customization. It has a relatively steep learning curve, though. If you are looking for a simpler approach, SSE Setup is another free option that appears to have very good support for Access developers. I've never used it myself; drop me a line if you've used it.
Client-server RDBMS
The Jet/ACE database engine is a file-based database. While having the entire database in a single file is convenient, there are some important tradeoffs for that convenience. If robust security, point-in-time backups, or high concurrency are important to the success of your application, you should use a more capable back-end database, such as SQL Server.
I debated whether to include this item in the checklist. There are some scenarios where it's better to store all, or at least some, of the data in Microsoft Access files. However, there should be an intentional decision to not use something like SQL Server. If your data is stored in Access files simply because it's easier, then that is a problem.
Other best practices
This is not an exhaustive list of every modern software development best practice. If you follow this list, though, you are likely to end up picking up those other best practices along the way.
Recap
If you can confidently say yes to every item on this checklist, then you are at the pinnacle of Access application development. You are on equal footing with "real software developers." And, in many cases, you will be outpacing them.
