Problem with Multiple Users Modifying and Adding Data

[sam.eade]

I have created a database for 2/3 users (will progress to more once the system has been smoothed over) who will have the db.file open from 9-5 and will be consistently adding and changing data within 2/3 forms. Most of the time but not always a message comes up saying,

"You do not have exclusive access to this database at this time. Any changes you make may not be allowed to be saved later"

I can understand that it can create data corruption if multiple users are allowed to edit and modify the same data at the same time but surely a database can't be that limited. I thought that splitting the database into a front end on a local server and then a back end on my computer would help the problem but everyone who is using the front-end still experiences the same problem.

Has anyone been able to overcome issues with multiple users access the same forms and tables? Even when another user is editing one form and another is editing a different form the message will still appear.



Any help would be appreciated.

[TG_W]

Are you editing forms/reports in the same database that they are working in?

[sam.eade]

Are you editing forms/reports in the same database that they are working in?

I had been initially, then I started to just create a copy and work on that whilst they kept inputting their data because I knew it would be problematic. Then I would just copy over any new data they'd added at the end of the day.

Recently, I have just imposed a cut-off at a certain time, where I will work on it after 4pm let's say. So they have to log out and let me manage the design. The multiple user access problem however I think is coming when they try and access the same front end forms and modify data.

[TG_W]

Are there any temporary tables where data is written to that are in the front ends? If not, do you think some might help with the issue? This would involve adding some update/delete/append queries, though, and might slow the database down some.

Also, how are the forms set up and what functions are they performing in them? If they are just entering data to a table via a form, it might be more beneficial to write to the table from the form rather than having a form that has 'Data Entry' property set to "Yes". Using this method, I was able to allow two users to perform the same actions at the same exact time without overlapping.

Having the database for analysis (sans confidential information) would allow for better troubleshooting.

[sam.eade]

Are there any temporary tables where data is written to that are in the front ends? If not, do you think some might help with the issue? This would involve adding some update/delete/append queries, though, and might slow the database down some.

Also, how are the forms set up and what functions are they performing in them? If they are just entering data to a table via a form, it might be more beneficial to write to the table from the form rather than having a form that has 'Data Entry' property set to "Yes". Using this method, I was able to allow two users to perform the same actions at the same exact time without overlapping.

Having the database for analysis (sans confidential information) would allow for better troubleshooting.

Here is the front-end minus all of the data. I edited some of the names, export locations just to remove last names and some search criteria that was a bit more personal. One of the reasons why most of the forms run through queries is because my manager wanted a daily report of what each user was doing. I created a field in the tables called last modified and then put a macro to update that field whenever one of the other fields were updated. Then simply exported all records that had been updated that day.

Raw DB.zip

I understand the db will not be optimal, i am a novice user but it was the best i could manage so dont be too critical of it

Thanks for your help.

[TG_W]

I would need the back end, too. If you make a copy of it, clear the real data and maybe replace with a few lines of dummy data, that should work.

And don't worry about how it is coded. We all had to start somewhere

[sam.eade]

I would need the back end, too. If you make a copy of it, clear the real data and maybe replace with a few lines of dummy data, that should work.

And don't worry about how it is coded. We all had to start somewhere

I think I may have stumbled on the solution completely accidentally haha! So when I went to delete the data to send the database over to you. I created a copy of the front end, deleted all the data and then had a panic moment because when I went back to the original it had deleted all of that as well! luckily I had backed it up buuuut what it made clear was that if you copy the front-end file, you are still drawing from the same back-end data but you do not have the problems of all the multiple users accessing the same DB.

So i think, a very simple solution, to what I thought was a difficult problem. Does that make sense? Thank you for your help though!

[sam.eade]

As a follow up query TG_W, you might be able to help. If i create multiple copies of the front-end and distribute them to the users. If they then edit their front-end copy, this doesn't automatically update the back-end. Do I need to sync this regularly? Do you know how to do that?

[TG_W]

You can have multiple instances of the front end tied to the same back end data. You can see where your front end is tied under 'External Data' > 'Linked Table Manager'. Since you already shared your front end, you would have only needed to copy the back end and delete the data for sharing, but great thing you had a back up! That growing pain was much less painful than it could have been. Personally, I lie to back up the back end weekly, and when I update the front end, I've moved to a long revision number now since I am building three modules that will be combined into one once complete, but it allows me to go back at any time ("Database Name R.0.0.000").

Any updates made to tables, even through forms, via a front end should be immediate. If they aren't updating immediately, you might have a different problem. If they are actually editing the database of the front end, that is a separate problem. To avoid that, you can make the front end ACCDE to hide the code but keep the programming.

[sam.eade]

You can have multiple instances of the front end tied to the same back end data. You can see where your front end is tied under 'External Data' > 'Linked Table Manager'. Since you already shared your front end, you would have only needed to copy the back end and delete the data for sharing, but great thing you had a back up! That growing pain was much less painful than it could have been. Personally, I lie to back up the back end weekly, and when I update the front end, I've moved to a long revision number now since I am building three modules that will be combined into one once complete, but it allows me to go back at any time ("Database Name R.0.0.000").

Any updates made to tables, even through forms, via a front end should be immediate. If they aren't updating immediately, you might have a different problem. If they are actually editing the database of the front end, that is a separate problem. To avoid that, you can make the front end ACCDE to hide the code but keep the programming.

Thanks Mate, explains a lot. Sorted everything now. Cheers for your help