Splitting an Access Database without the ability to create unique fe

1
votes

I created a 2010 Access database for my team to use. Right now the database is in a shared folder on the server and everyone uses the same database file. I've run into some problems recently with the database auto-backing itself up and creating extra files (Database.mdb, Database1.mdb, Database2.mdb, etc). From what I can tell, I need to split the database into a front end and a back end and give each user a separate/local copy of the front end to interact with.

The way the company is set up, everyone uses a virtual desktop (built on Citrix). My team has a shared folder that everyone can access. I can't have everyone download a copy of the database to their local computer as we don't consistently use the same computers depending on where we are in the hospital. I don't have any access or authority to download a copy to each user's login. I don't have any experience with databases or CS/programming except what I taught myself to build the one for my team and I am having trouble understanding how splitting a database would functionally work when using a server like Citrix. Everything I have read assumes you have access to control how things are set up on the back end.

Say I split the database and store the front end in the shared folder so everyone can access it and store the back end in a separate location. Since everyone has a unique login to get onto the virtual desktop, does that mean that users essentially have a unique copy of the front end that they interact with even though everyone is accessing the file through the shared folder? Or is everyone still using the same front end?

If everyone would still be using the same front end, does it make a difference/is there a point to splitting the database? Would I be able to split the database and store the back end on my personal login ID or would I have to store it in a separate folder in the shared folder so that the data everyone puts in updates/saves to the database? If it's not feasible to split the database the way I am supposed to, is there another way to protect the data/files from corrupting?

Any help understanding this would be greatly appreciated.

1
databasems-access-2010
Using Access as a shared database is your first mistake. Get a real database - there are lots of free open source relational databases that would be better suited. SQLite, MySQL, PostgreSQL would all be a better idea that Access. It's difficult because you're trying to do something that Access isn't intended for. It sounds like you really need a web app with a relational database in the back to accomodate multiple simultaneous users. - duffymo
Unfortunately I don't have access to anything else. I am not involved with IT or IS or anything close so I don't have the necessary permissions. Access is what everyone has on their virtual desktops, and we are not allowed to download other applications. - Alisa
Ask if somebody in your company can write a proper application. This way is not sustainable. It's not in their interest to have non-developers address shortfalls this way. - duffymo
I've been trying to get in touch with someone to help. However, I don't this is something they would help develop since it won't affect a large number people (I work in a large hospital). The program itself works fine and is a lot better than what we were using. I am just concerned about long-term stability of the application. - Alisa
Anything that is patient or business critical should not be done using desk drawer software. But if it's better and working, then go with it. Just find out how you can version and back up that folder every day; more often would be better. That way you only lose 24 hours or less if it goes belly up. - duffymo

1 Answers

2
votes

One of the companies I work for still uses Access on a daily basis, so I feel your pain. With multiple users accessing the data at the same time, it is necessary to split your database to a front end and a back end. If you don't you will see a lot of corrupted databases. Once you deploy a front/back end, you will reduce the number of corruptions by quite a bit. It's just good practice overall to split your database

You shouldn't have any problems with multiple users accessing your front end. At the company I work for, they have front ends distributed to each persons desktop. There are a few users though that have to share the same front end though and they have never had a problem.

Once you have the database split, you can used the linked table manager to point your front end to your back end tables. I would suggest putting your back end in a separate folder.

Here is a decent write up of what you are wanting to do. http://www.fmsinc.com/microsoftaccess/databasesplitter/