Microsoft Access is the most popular desktop database in the world with millions of databases; some being mission critical. As such, CTO’s need to recognize and manage these kinds of applications to ensure business success and continuity.
The very worst moment to find out you’ve got a mission critical Access database is when it stops working, with little-to-no knowledge of how to troubleshoot the issue. Therefore, we’d like to cover what you can do in situations like these to ensure individual and business success.
Follow these steps to identify mission critical applications:
Inventory all Access files that end in ACCDB on your network.
Sort the list by last modified date.
Most, but not all Access applications are split into a data file and a programming file; it is likely that the data file will have a more recent date with a file name that ends in BE.
With your sorted list, approach departments and ask them to identify any mission critical apps, along with a brief summary of what the database does and why it’s mission critical. Ask them to list the owner of the databases and the author, who may not be the same.
Make sure best practices are followed:
Now that you’ve identified your mission critical databases, follow these best practices:
Make sure every database is split into a Front End file with code, with a Back End file containing your raw data.
The Back End file should be on your network, and you should ensure to back up the file each night.
The Front End file should be saved on each user’s PC.
Have a database diagram created for each database if there is none.
Ensure that the VBA and macro code is well documented.
Add a table to document changes to the system by version and date.
Modify the startup form to include the version and issue date.
Meet with the database owner and author to document and understand the business case for the application:
Why is it mission critical?
Why can’t they use existing enterprise systems?
Are they experiencing issues with slowness and bugs that may impact performance as the system grows? (Access has a hard limit of 2 Gigabytes in size and as you approach it, it will get slower).
Maintenance of the Access database consists of doing a compact and repair on your Back End file every 3 or 6 months, as needed.
Your employees will continue to use Access, enable them.
Access can be a great tool in the right hands, allowing companies to leverage its ease of programming and rapid development. With your applications remaining as toolsets in Office installed on your desktops, your employees can successfully learn how to develop and manage Access apps, while fully reaping its benefits.
As a result of its short development cycle, Access is the ‘go-to’ application for productivity improvements. Therefore the time to identify when an Access application is mission critical is before a key developer leaves, or if the system stops working or becomes unstable.IT managers need to be proactive, working with departments to identify databases and document their behavior.Having a strategy in place for dealing with mission critical Access databases will ensure your company can continue leveraging the technology for years to come.
About the Author:
Juan Soto is the President of IT Impact and a leading professional in the industry. He has been named an Access MVP by Microsoft since 2011 and is a frequent author on the official Microsoft Access blog as well as the co-founder of AccessUserGroups.org, where groups of Access enthusiasts around the world meet once a month on a wide range of topics. You can reach Juan at https://accessexperts.com/contact/