Multiple users on front end database

[direptor]

Hi,

I've prepared a front end database with forms that allow data to be keyed into linked tables to my main databse.

However, currently the front end database only allows 1 user to do it at 1 time and when another user tries to open the file, an error "The Database Has Been Placed in a State by User on Machine That Prevents It From Being Opened or Locked'

Is there anyway to allow multiple users to use the front end database at the same time?

 

[PaulO]

In Access 2003 ...

Tools>Options>Advanced Tab>Default Open Mode=Shared

 

[direptor]

In Access 2003 ...

Tools>Options>Advanced Tab>Default Open Mode=Shared

Its already shared.

I've also enabled all marcos as adviced from another site but it doesn't seem to work as well.

 

[boblarson]

Each user should have a copy of the frontend on THEIR machine. You should NOT be having each user open the same exact frontend file from a network location.

 

[direptor]

Do i just ask everyone to copy the front end database file on to their own computers and run the file from there? First time creating a database for multiple users so unsure about how to get the whole thing running well.

 

[boblarson]

Do i just ask everyone to copy the front end database file on to their own computers and run the file from there? First time creating a database for multiple users so unsure about how to get the whole thing running well.

I would suggest one of two options.

1. Create a batch file for each user to run each time they open the database and have that batch file copy the current frontend down to their machine and open it. This would ensure they have the latest frontend file.

2. But #1 can create a bit of network traffic so you may want to use an Auto Updater. I have a FREE frontend auto uipdate enabling tool which can be used to make your frontend auto updating. So, what would happen is that

a. You make changes to the frontend.
b. You change the version numbers in both the frontend and backend.
c. The next time a user opens their frontend and the version doesn't match what is in the backend table, it tells them their frontend is out of date.
d. It then closes their frontend file
e. and deletes it from their computer
f. and then copies the new frontend down from the network location
g. and automatically opens it up for them.

You can find it under MY FREE ACCESS TOOLS link on my website (see my signature).

 

[pr2-eugin]

Yes, each user should have a personal copy of the front end file. preferably placed on their local machines.

 

[direptor]

All i need users to do is to key in data in the forms on the front end.

Does that causes any versions etc to change since i should not be making any changes to the database anymore? Will i still need an auto update tool or just copy and paste the front end file to individual desktops will do?

 

[boblarson]

All i need users to do is to key in data in the forms on the front end.

Does that causes any versions etc to change since i should not be making any changes to the database anymore? Will i still need an auto update tool or just copy and paste the front end file to individual desktops will do?

If you want to manually copy the frontend file to each user's machine each time you make a change in the frontend (not a change in the data) that is fine too. But that can get unwieldy and it can have someone fall through the cracks and you don't know who is using the most current frontend or not. Remember, data is stored in the backend - where the tables are. The frontend is LINKED to the backend so the data, while changed in the frontend is actually being changed in the backend through that frontend interface.

 

[direptor]

The purpose of my database is more for people to key in data on on a group of people for us to generate reports and maybe analyse the data. Once the data is all keyed in, i will remove the front end since there is no need for it anymore and create a new backend and front end for the following year with a different list of people.

For my case, is a manual copy and paste sufficient since the users can delete the file they copied once they have done with keying in their data.

 

[boblarson]

No problem. So what is the reason that you do a new backend and frontend file each year? Does the table structure change from year to year? Is it a legal requirement to keep the data segregated and not just have a year field in there so you can even run multi-year analysis? I'm just curious.

But the big takeaway is that everyone needs their own copy of the frontend file.

 

[direptor]

My list of people that we need different staff to assess and key in data changes every year and i need to keep previous data on record so i was thinking of just archiving each year's file and copy and paste to create a new file with a new list of people each year rather than have all the data together in 1 database.