Here's a response to a question about the standards we implement for our Microsoft Access applications. These are some fundamentals for ensuring a solid foundation for professional Access solutions in priority with some resources related to them:
- Establish a backup, compact and repair, and disaster recovery plan. We use our Total Visual Agent program to schedule and automate the daily database chores.
- Putting in a comprehensive and global VBA error handling structure so crashes are documented with the procedure call stack and we don't have to rely on a user's recollections. As part of the error handling, we also always add line numbers to the code we deliver so we can pinpoint exactly where a crash occurs. We use the Code Delivery feature of Total Visual CodeTools to do this.
- Split Database Design. Without this, it's nearly impossible to enhance the database while others are changing the data.
- Cleaning up VBA code. From code indentations to applying our variable naming conventions along with prefixes for global vs. module vs. procedure, and constants vs parameters vs. regular variable names. We use the Code Cleanup feature of Total Visual CodeTools to do this. Personally, without cleaning it up to our standards, I find it nearly impossible to get any work done while I'm struggling with someone else's convention.
- Setting up a development, testing and deployment process so changes can be implemented safely and efficiently. Lots of issues around this but having a fast, stable way to manage changes and how people launch the Access application is critical. We use our Total Access Startup program to help with this.
- After these structures are in place, we're ready to run Total Access Analyzer against the database to address the issues it finds wrong and could be improved in the application.
For additional details, including Luke Chung's PowerPoint presentation at the Portland Access User Group conference in May, read his paper Taking Over Legacy Microsoft Access Database Applications.