Access as a solution platform - good or bad

Engineering Software general discussion Databases
#1

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.

4 Likes
#2

Amazing how much that looks like good CAD design advice. As designs get more complex (>100 parts in assembly) people should be doing almost all of the things in your list because planning and organizing the design before it’s made can save SOOOO many headaches later.

A few months ago I settled down to plan a business database to track the large number of intellectual property items that my company has developed over several decades. We currently have a database of sorts, but it doesn’t track what customer has bought which design in the past, or which aircraft they installed the product on. There actually is ANOTHER database that tracks some of that, but it’s horribly incomplete so splicing the two together is not a start of a solution.
The complexity of what we need drove me into planning mode, not coding, first. I ended up making a set of flowcharts, and I’ve started using them to explain how the many pieces of the DB will work together.
It’s a start…
I haven’t done half the things Duane has recommended, but will begin considering them, for sure. Thank you!

#3

Thanks for the reply SparWeb and good luck with the database building. I suppose lots of projects at home and work can benefit a lot from stepping away from the “tools” and thinking through things on paper or whiteboard.