Updating recordset based on Access linked table runs too slow when entries are simultaneous.
Access front-endslows down when the time entry is made by at-least 3 simultaneous user/computer, the maximum number would be 6 computers at a time. Employees enter their time into a split Access database application, in which the front-end is located on each PC.User opens the entry form that is bound to a linked table -tblHours, that is filtered for that employee and the day They select a project and type their hours into unbound text boxes at the header, the [Add Hours] button adds the new entry then calls the private sub CalculateHours , which updates the tblHours again, through a recordset, which is filtered for that employee and the current week, result is less than 100 records. CurrentDB.OpenRecordset("Select * From tblHours Where EmplID='abc' and DatePart('ww',now())=DatePart('ww',[WorkDate]);") The goal of the function is to loop through all entries for that week; If particular entry hits the overtime. Divides hours to regular and overtime duplicates the current entry andinserts overtime portion of the entered hours, changes the hours field to regular portion of the entered hours, Then continue looping, to change (update) next entry Everything works perfect, it takes few seconds to add new entry and to calculate hours, pay amount etc., until when the app is opened by other users simultaneously at the lunch time or and of the day. The performance of the front-end reduces drastically - up to 30 sec- and personnel in the shop lines up in front to the computer for being able to login and enter their times. My reasonong is that tblTimes is used by the form as well as within the recordset, so it gets several hits at a time. So, my plan is; to use a local temp table instead of tblTimes get all entries for that employee and the week into it, have the same CalculateHours function looping through records in the temp table, then (1) use update query for the existing records in tblTimes, (2) use insert query to add new entries into actual tblTimes done. I developed a timesheet before, but didn't experience that before, I am open to any suggestions / recommendations, thank youin advance.
