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!

  • "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)." - Why wouldn't they support it but they do support MS Access which stores the data in a similar way (database files in a folder somewhere - in your case a server share)?
    – J.D.
    Commented Feb 23, 2022 at 20:52
  • @J.D. - using database files stored on an existing network drive requires no additional setup from our IT department, whereas maintaining a MySQL server, apparently, would. As is commonplace in the (British) healthcare system, effecting change involves fighting through layers of governance-related bureaucracy that, as a healthcare professional and relatively novice IT enthusiast, I'm somewhat unwilling to fight! :)
    – slimn
    Commented Feb 24, 2022 at 20:57
  • Gotcha. BTW a file share is a reference to a folder location on a server as well, which your IT department has to maintain just the same as any other server, even a database server, there's no difference. It would just be an extra server on their list to maintain, which perhaps they just don't want the added work. That being said, you can have a modern database system store it's database files on a file share no different than you're already currently doing with MS Access. The database system would also just need to be installed somewhere, just like MS Access does, so its logistically similar.
    – J.D.
    Commented Feb 24, 2022 at 22:48

1 Answer 1


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.

  • Thank you -- I thought as much! At the very least it has served as a useful 'prototype'!
    – slimn
    Commented Feb 24, 2022 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.