Input data by multiple users in the same time (1 Viewer)

Costin_Olaru

New member
Local time
Today, 16:51
Joined
Aug 7, 2020
Messages
4
Hi,
I built a database, I split it (front-end and back-end) and now I am facing errors when multiple users attempt to enter data in the same time.
Data are entered into a table thru a form. I have some code when form is loading in order to load it empty (DoCmd.GoToRecord, ,acNewRec). Basically, when multiple user open the respective form, they are allowed to edit the same record (the New Record), but only the first user who hit the save button succeed in saving data while other users receive error messages and their data are not saved (and they have to start from the beginning).
Could you please help me with a solution to this?
Thank you,
Costin
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:51
Joined
Oct 29, 2018
Messages
13,720
Hi Costin. Welcome to AWF!

If you are talking about multiple users updating the same record at the same time, then that is problematic. The best solution is to avoid that situation. Why do more than one person update the same record at the same time? What is your database about?
 

Costin_Olaru

New member
Local time
Today, 16:51
Joined
Aug 7, 2020
Messages
4
Thank you.
Yes, I am looking for a solution to avoid this situation.
I wrote some code into Before Update field in the Event section of the Form "telling" to MS Access to go to the first available line in the table and save the data there, but with no success.
My database is collecting mileage data form users.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:51
Joined
Sep 21, 2011
Messages
7,478
Are they using the same FE?
You should not need to tell Access to do that, it does it automatically?
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:51
Joined
Sep 21, 2011
Messages
7,478
Big NO NO. :)
They should have their own copy on their computer.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:51
Joined
Sep 21, 2011
Messages
7,478
Just try it with 2 users at first.?
Also allow Access to decide where it will store the record.
 

Micron

AWF VIP
Local time
Today, 09:51
Joined
Oct 20, 2018
Messages
3,471
I don't think the shared fe is the reason, notwithstanding that sharing one fe is not advised. I think it's the "go to the first available line in the table" that's the issue, whatever that means. Just let Access handle the new record for each person automatically. It will do that regardless if you're sharing one fe or not. Also, you might have to set Locks option to none if it already isn't. You might be locking an entire set of records as soon as one user starts something.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:51
Joined
Feb 19, 2013
Messages
12,759
I have some code when form is loading in order to load it empty (DoCmd.GoToRecord, ,acNewRec).
be aware this does not load it empty - it simply goes to the 'last' i.e. 'new' record. You are still loading the entire table or whatever you have in your form recordsource.
 

Micron

AWF VIP
Local time
Today, 09:51
Joined
Oct 20, 2018
Messages
3,471
Good point, CJ. I should have mentioned that when I implied that the underlying record(s) might be locked. Obviously that cannot happen if there are no records, but as you rightly pointed out, there most likely are records. Only an empty table would make that not true in this case.
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:51
Joined
Sep 21, 2011
Messages
7,478
I don't think the shared fe is the reason, notwithstanding that sharing one fe is not advised. I think it's the "go to the first available line in the table" that's the issue, whatever that means. Just let Access handle the new record for each person automatically. It will do that regardless if you're sharing one fe or not. Also, you might have to set Locks option to none if it already isn't. You might be locking an entire set of records as soon as one user starts something.
I didn't think so either. Who knows what that code is doing. :)
 

missinglinq

AWF VIP
Local time
Today, 09:51
Joined
Jun 20, 2003
Messages
6,399
Current problem aside...you absolutely must have a copy of the Front End on each user's PC.

Having multiple users sharing a single Front End, on a network drive, is the sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior!

Being in forced retirement, I spend 8-10 hours a day here and on other Access forums/newsgroups, and over the past 15 years have seen literally dozens and dozens of reports of split databases, using links to a single Front End, causing these kinds of problems, as well as massive data loss! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

Linq ;0)>
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 28, 2001
Messages
18,715
I'll add a comment, but with the warning that there are two schools of thought on this one.

My best results came when I (a) had COPIES of the FE on each user's machine - and that is not controversial, and (b) instead of basing the form directly on a table, I based it on a query that exactly matched the fields of the table. That allowed me to "browse" the table using a query that had an ORDER BY clause to cause records to appear in a particular sort order. The reason this MIGHT be considered in the least controversial is that even if you didn't impose an order, I STILL think you do better on a single-table query because of the implications of having a query drive the process. The mechanisms for query update vs. table update are slightly different. I have seen a few folks on the forum who disagree with this. My advice is TRY IT. I've never had trouble with it.
 

missinglinq

AWF VIP
Local time
Today, 09:51
Joined
Jun 20, 2003
Messages
6,399
Truth be, a lot of us do the same thing. In addition to easily doing data manipulation within a Query...it adds a second layer of protection, a sort of buffer, between the user and the Table...always a good thing!

Linq ;0)>
 

Users who are viewing this thread

Top Bottom