Safe end-user querying for Access
When developing a new database, smart practice is to start from the reports and work backwards from there. It’s easy to get excited about all the interesting data that we want to put into the thing. But at the end of the day, what really matters is the data that somebody, someday, will need to get out. In good database design, the “out” largely defines the “in.”
In the real world, that reporting piece is never really finished. Sooner or later – however beautiful your design and development – users will start to wish for new queries and reports. That’s why I encourage end users to learn how to query and report for themselves. Yes, SQL takes a day or two to learn, but it’s a powerful tool to have in an increasingly data-driven world. If clients are using MS Access, I can introduce them to the visual query builder – a great shortcut tool that makes SQL querying (almost) easy for beginners.
With great power comes great responsibility. When end users play with live data tables, accidents can happen. Ideally they’d have read-only access for their experiments, to avoid any accidental changes or deletions to live data. And here’s another issue: where can end users store their new queries and reports? Not in the front end file, since that file will be replaced whenever there’s an upgrade, thus overwriting all their hard work.
Here’s my solution to both problems. (This solution is for MS Access; for online databases it’s a whole different set of questions.) I give end users a “work file” that sits beside the main interface. Like the interface file, the work file connects to the single shared data file (which is usually living on a file server somewhere on the local network). But – unlike the typical interface file – the work file doesn’t use linked tables, which have live data and are therefore vulnerable to mishap. Instead, it automatically imports in all the tables from the central data file. Then the user can experiment with the data without any worries. Somebody might be doing data entry at the same time, and those newest changes wouldn’t be visible to the work file until the data is refreshed. But in most office and lab environments, there’s no need for such minute-to-minute results.
The work file is also a good place for the end user to build and save sophisticated queries and reports. Whenever the data is refreshed, the saved objects give real-time answers. If the database is upgraded, those objects should continue to work as before, so long as the data structures haven’t changed too drastically.
Leave a Reply