There is a wide spectrum of opinion regarding the use of MS Access in business. It’s a RAD tool that employees from beginner to experienced can create some fairly functional applications. However, there are lots of opportunities to build nightmares in terms of support, corruption, delivery platforms, and instability. Some tasks are better suited to SharePoint lists, off the shelf software, Excel (yes, I said it), web applications, Power Apps, …
If your company plans to use MS Access I would keep these as priorities:
- Good specifications and scope. Try not to build on the fly. Use good methodology to identify functionality and use cases
- Split your applications into front-end and back-end files. The FE contains all of the forms, reports, code, queries, etc while the BE has only tables.
- Every user should be working with their own copy of the FE. Sharing the FE on a server can lead to corruption.
- Spend a lot of time on table structures and normalization. If you don’t you will often need to add fields which means modification of forms, reports, queries, etc. Maybe even step away from the keyboard and up to the whiteboard.
- Consider using other data sources. SQL Server is much more robust, secure, and scaleable. You can link to SharePoint list for viewing and editing. Access is a great tool for combining data from many datasources.
- Enforce a naming convention. Personally I don’t use spaces or special characters in naming anything. There are lots of resources on the web regarding standards. Find one and implement it.
- Don’t attempt to mimic another system. I have often seen a requirement to have the screens look just like the Excel version. Invest in usability and flexibility, not familiarity.
- Plan for support and end of life. It’s not if your business needs will change but when.
There are more tips on the web but this should get you started in the right direction. I would appreciate any comments.