Hi
I’m using a WD My Cloud 3TB to share folders with about 5 other computers. I have a folder with a database in it that only one person should edit and so I have it set to read/write for 1 user and read only for the rest.
Due to MS Access being microsoft, they’ve nicely made it so that is not the end of the story.
If someone with read only permissions open the database to look up info and the person with read/write permissions open it while the file is in use then they can also only open it as read only! From what i understand it is because the permissions of the folder needs to be set as read/write for all and the actual file itself needs to be read/write for 1 and read only for the rest.
Is there any way to do this?
Cheers
No idea how access would deal with a read only DB file but you could move the DB to its own share and set privileges there.
Hi Larry
Cheers for the reply. The problem is that the folder and the actual individual file permissions need to be different but all I can do is change permission for the whole folder
There is a totally different way to solve this issue, let me please explain my solution.
MS Access always loads the full content of tables into queries prior to running the query. Other way round, changed content of database entries will be written back into the table after you applied the changes. Your main problem is the write access into the database but not the folder on the NAS.
Divide your MS Access project into two parts, the frontend and the backend. The frontend contains all queries, reports, forms whatever. The backend contains the tables only! Connect the backend tables into the frontend part by linking the tables of the backend into the frontend. Here you can find the full story: Split an Access database - Microsoft Support
At the end, use the built-in user rights management of MS Access to grant access to the database but keep the backend database in a special shared folder on the My Cloud. This folder only needs/grants access to one(1) user account. This NAS user account need not to be similar to an existing user account on your PCs, it’s only for establishing the connection between frontend and backend of your MS Access project. Please don’t mix this with the way ODBC “Open Database Connectivity” is working, ODBC is not applicable here as we don’t have Windows OS on the My Cloud.
The authentication account you setup on the NAS need to be used for the conenction to the .accdb file stored on the NAS. If you want to, you can even write a short VBA script to to the linking, so you can hide the login credentials to “normal users”. The frontend can be deployed on each client PC.
Splitting your MS Access project into frontend and backend offers you multi-user environment, a much better performance of your MS Access database and a huge decrease of network load - all in once at the same time.
Try it, it works perfect.
I don’t believe there is an easy/supported way to do this, permissions are only at the share level
you may be able to do it with manual Samba config which can get very involved and it has been too many years ago for me to even be sure if it is possible or not
Thank you to all for the replies.
Joerg I will have a look into this and see how I get on. Will report back if I am successful haha
1 Like
