Input data by multiple users in the same time

[Costin_Olaru]

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]

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]

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]

Are they using the same FE?
You should not need to tell Access to do that, it does it automatically?

 

[Costin_Olaru]

Are they using the same FE?

Yes, the same fe

 

[Gasman]

Big NO NO.
They should have their own copy on their computer.

 

[Costin_Olaru]

Big NO NO.
They should have their own copy on their computer.

OK, thanks, I'll try it

 

[Gasman]

Just try it with 2 users at first.?
Also allow Access to decide where it will store the record.

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

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]

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]

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]

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]

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)>