I'm looking for some help regarding Microsoft Access and database design.
I have been developing a database application on Microsoft Access for use in my organisation. I work in a hospital and we have a very old fashioned(!) way of keeping track of where our patients are in the hospital, and what's wrong with them. We create a daily Word document that is stored on a shared drive on a LAN. As can be imagined – this becomes a nightmare when it comes to things like trying to maintain some kind of audit trail!
My alternate solution is a relational database, split into a 'front-end' with queries, forms and reports, and a 'back-end' comprised of 15 tables. Both of these are Access DB files are contained within a folder on the network drive. The overall database structure is not too complex – it is in essence a table containing patient details, and other tables containing details pertaining to encounter details, blood tests, scan results, etc – linked with a one-to-many relationship based on the patient's unique hospital number. Patient lists are essentially reports embedded within the front-end, based on queries that pull patients that are currently marked as 'admitted' within the database.
It works reasonably well when one user is using the database, and runs fast when executed on a local machine, but the problem is — it's frustratingly slow — and sometimes doesn't even run at all (causing MS Access to stop responding and crash) — when more than one user tries to access the front-end via the network drive.
I'm wondering whether there is something missing with regard to how I can optimise the database to perform across a LAN? Or whether this is a technical limitation of MS access and whether I should be diverting my energy into a different solution (eg SQLite with a VB frontend)? Unfortunately, using anything other than a shared file-based solution is not an option as my organisation will not support it (e.g. a dedicated MySQL server).
Grateful for your thoughts!
Best Answer
My experience with MS Access is similar in that it doesn't do well with multiple users. You're better off using an RDBMS that was designed for multiple users.
In the US, hospitals have legal requirements for storing and protecting patient data. I would take local regulations into account when picking out an RDBMS. Cost being one of the lower priority requirements. Row Level Security (RLS) would be high on my decision matrix.
IIRC - MS Access front end can connect to MS SQL (Express) RDBMS. It might be able to connect to other RDBMS via ODBC. So, all your UI work may not have been lost.
As you grow, you'll have multiple applications interacting with the data. You should plan on that happening. I do this by putting most/all of my data logic in the database (stored procedures, etc). The front-end technology is then chosen based on how easy it can handle this type of design.
Beyond that: "Which RDBMS should I use?" is off topic for this site.