Share a database

[BittenApple]

Hello,

We have a microsoft Database in our department.
I wonder if we can share this dbase that everybody gets the write right?

Regards,
BittenApple.

[HansV]

As long as all users have full permissions on the folder that contains the database, an Access database is shared by definition.

If you want the database to be used by several people, it's best to split the database into a backend containing the tables and a frontend containing queries, forms and reports, plus links to the tables in the backend. Give each user an individual copy of the frontend.

Why? See for example 10+ reasons to split an Access database.

[CData]

I think HV is being too gentle with you - and in my experience one must absolutely positively split the database. Access is fully multi user and rock solid when installed correctly via the split database method.

you will find the split feature embedded in the product up in the ribbon.... the back end file holds the tables that are common to all. So there is only 1 back end file - - then you copy the front file so each user has their own on their PC - - and they all link to the common back end file.

there is in addition 2 levels of security that can affect things:
1. Access - this is referred to as the Trust Center; on each user's PC - the folder where the front file sits must be set up as a trusted location and the folder where the back file sits must also be set up as a trusted location. you will find this feature also embedded in the product - - it is a bit obtuse so you'll want to find instructions on line......

2. OS/directory/folder: for this one can use a common sense sanity check....when sitting at a user's PC - - open the folder where the back end Access file is located - - add or edit a Word doc or Excel doc; if it won't let you do that then your permissions need to be changed....

hope this helps....

[BittenApple]

Hello CData,
I will go through the instruction and I will get back to you.
What do you mean by saying this?
-So there is only 1 back end file - - then you copy the front file so each user has their own on their PC - - and they all link to the common back end file.

When I make a form each person goes into the form and make data entry and the result will be recorded on the back end table.

1. Access - this is referred to as the Trust Center; on each user's PC - the folder where the front file sits must be set up as a trusted location and the folder where the back file sits must also be set up as a trusted location. you will find this feature also embedded in the product - - it is a bit obtuse so you'll want to find instructions on line......
I have one access db that front file and backend sits. Backend and frontend of the database are not coming in two separate folders.
Let me work on it and get back to it.
Thanks,
BittenApple

[CData]

*. "each person goes into the form" - as you have written: yes but each user has their own copy of the front end file, which contains all the user interface objects i.e. forms. the front end files all link to the single back end file (that holds nothing but tables/data) - - - - this is the way a single database is made multi user

1. "Backend and frontend of the database are not coming in two separate folders"; when you move to multi user one must have a front end file per user and this file should sit in a folder on the user's PC

[BittenApple]

Hello CData:

How to achieve this?
*. "each person goes into the form" - as you have written: yes but each user has their own copy of the front end file, which contains all the user interface objects i.e. forms. the front end files all link to the single back end file (that holds nothing but tables/data) - - - - this is the way a single database is made multi user

What is the instruction to make front end of Access database to be multi user?

I clearly understand this topic: in every company, we have systems/ application, we see only the front end of the application and each of us has a copy of the front end in our machines while we don't have Access to the backend (tables and database objects).

How can I make my Access to have multi user?
Any instruction on when you move to multi user one must have a front end file per user and this file should sit in a folder on the user's PC?

Regards,
BittenApple.

[HansV]

An Access database is multi-user by definition. You don't have to do anything special for it.

But it works best if you split the database into a frontend database and a backend database.

The backend database contains all the tables you need, and nothing else. The backend table will be stored in a shared network folder. All users should have full permissions (read/write/create/delete) on this network folder.

The frontend database contains links to the tables in the backend, plus all the queries, forms, reports, macros and code modules.
Give each user a copy of the frontend database; the user should store this copy on their own computer.

You can split the database manually - it's not difficult - or use the button for it on the Database Tools tab of the ribbon:

S0834.png

[BittenApple]

Hello Hans & CData,

I followed the instruction. I created a database and split it. I have one database with forms and linked tables and one only with forms.
Should I hide the linked table in the front end from end users as we do when we don't split the database?
Should I encrypt the backend with a password?
When I created the database and split it; I saved the first database on my desktop. The other copy is created on the desktop.
The backend table will be stored in a shared network folder.
At work, where should I save it? How can I find the shared folder? We have a network and we work out of network.
Should I enable trust center? Should I change the Trust Center setting?

2. OS/directory/folder: for this one can use a common sense sanity check....when sitting at a user's PC - - open the folder where the back end Access file is located - - add or edit a Word doc or Excel doc; if it won't let you do that then your permissions need to be changed....

If I want to give a read, write access to database, should I apply it to the folder itself where the database backend is located in?
Many thanks for all these and I think I have relieved from some dismal.

Regards,
BittenApple.

[HansV]

You should hide the navigation pane, and disable the Access Special Keys (such as F11 to activate the navigation pane) in the frontend, from File > Options > Current Database.

Whether you want to protect the backend with a password depends on how secure you want the database to be.

Create a folder on a network drive for the backend and grant full permissions on this folder to all users who will be using the database. If you cannot do this yourself, or don't know how to do this, ask the person who manages the network (usually the IT department of your company).

Each user receives a copy of the frontend and stores it in a folder on their computer. They should make this folder a trusted location for Access in File > Options > Trust Center > Trust Center Settings > Trusted Locations.

[BittenApple]

Hello,
is there any recommendation where/ how to save the Access database?

I started a new folder and put Microsoft Access in that folder in a share network drive. I made a copy of the Access and then split the copy; I saved the backend and from and original database before splitting in one folder on shared network drive.

My plan is to create a folder for each end user in the network share folder and put one copy of the form in each folder.

Does this method sound all right?

Regards,
BittenApple.

[HansV]

That is a possibility.
If each user copies the frontend to a folder on his/her own computer, performance would probably be slightly better, but with the speed of computers and networks nowadays, it probably won't make much difference.

[BittenApple]

Hello all,
I followed what I have been instructed to. So far, it has gone well. I copied the form and I put it in my own folder and I put another copy where the backend and database before splitting are located. Both worked well.
Thank you so much for all generous help.
BittenApple.

[BittenApple]

Hello team,
As I said in my previous post. I split the database and I laid a copy of the front end to each user's folder. (I created a folder for each user on the shared network drive and laid a copy of the front ent).

I put the copies in a small test. All of the copies are working so far (just small testing) except the copy for one of the user.
When that users clicks on the front end copy, it says the database can't be found. I don't have exact wording.

How can I fix this issue(the copy for that end user is not found/ or he doesn't have access?

[HansV]

Make sure that this user has full permissions on (1) the folder that contains this user's copy of the frontend database, and (2) the folder that contains the backend database.

If you have ascertained that this is OK, check the path that the frontend uses to link to the backend. For example, if you used the drive letter L: for the path of the backend, but it is M: in this particular user's Windows, the link to the backend won't work.

[BittenApple]

Hello HansV,
I will check on these two and what CData suggested in the past and I will inform the forum.

I came to add the database location to trust center, I didn't, because I thought it might cause more problems.
Also, 3 other end users has the same path which was routing to program files and their form are working well; so, I didn't try to add the newly generated folder containing the form to the trust center.

Also, I saw that the primary key in one the tables has a PK as auto number as double and the same pk in another table has come as FK and its data type is Integer.
I left as it was in order to get verification from you to make sure I am doing the right thing. (Because I have to present my form tomorrow, everybody is going to open their form and enter data all at the same time; I didn't want to bear risks.

If I need to have an update query, should I create the update query at the front end?

I appreciate all your help.
Thanks,
Bitten Apple.

[HansV]

It would be better if the Primary Key and Foreign Key have the same data type and size. Long Integer is the most common size for a Primary/Foreign Key.

You only need to change the data type in the backend.

[BittenApple]

Hello,
I noticed that those team members who don't have access, have a different path or drive name.
Our drive is a shared drive on the network.

Should I remap the folder name?

If yes, how can I do that/

Regards,
BittenApple

[HansV]

Instead of a path with a drive letter such as

H:\Corporate\Access

you can use the UNC path of the shared folder. This looks like:

\\OurServer\OurShare\Corporate\Access

The UNC path should be the same for all users.

If you don't know what the UNC path of the shared folder is, ask your IT department.

[BittenApple]

Fine, After I find out what it is, where do I need to enter it? or what the instruction is?
Regards,
BittenApple

[HansV]

Open the frontend database.
On the External Data tab of the ribbon, click Linked Table Manager.
Tick the check box "Always prompt for new location".
Click "Select All", then click OK.
Click in the file name box, then enter the UNC path + filename of the backend database.
Click Open.
You should get a message that the tables have been linked successfully.
Click OK, then click Close.