Good evening Access community, I find myself here in desperation throwing myself upon your technical mercy!
Background
I've developed and maintained an Access Database for a small-medium sized printing company since 2009. The database is used to request the manufacture of stickers (decals) by part number by the stock control team, batch groups of these manufacture requests onto a job (each job given a job number) for manufacture and log the particulars of 10 or so manufacture processes on a job during the production of these stickers. The system shows which jobs are in each department, required by dates, job info and full history of all completed jobs.
The system is split into front-end / back-end with the data back-end on a shared drive on the network and a copy of the front end on each users machine on the network. There are typically 10-20 users on the system at any one time. The system was created on Access 2007 but upgraded to Office 365 a couple years ago. All users are now on Office 365.
The system comprises of:
- 35 Tables (826 fields total) - Back-End
- 300+ Queries - Front-end
- 100+ Forms - Front-end
- 50+ Reports - Front-end
- 50+ Macros - Front-end
There were about 1.2M records in the system.
I left the company about 5 years ago however given the bespoke nature of the system and its now critical role within the business I still maintain & develop the system when occasionally required after work or at weekends.
Issue.
Approximately 6 months ago the system began to crash (back-end) regularly. This now happens several times per day - 6 times today alone.
When the back-end crashes all users must close their front ends and a compact & repair needs to be performed on the back-end before people can get back on the system.
There does not seem to be a pattern to these crashes which would help pin point the cause.
One clue is that the night shift team which is a skeleton staff reports less frequent crashes which makes me think it could be linked to usage / load on the system.
Side Note: For a long time now the system back-end swells with usage to 500mb+ and slows until a compact & repair is performed. This was originally required a couple of times per year but eventually got to the point where this was needed several times per month.
Since this crashing was first reported I have endeavoured to fix the issue with little success.
To date I have:
- Reviewed back-end build.
- Deleted around half of the data (archived to a separate database)
- Reviewed the table structure, deleted any redundant or unneeded fields & tables.
- Total rebuild of the back-end, export all data to excel, cleanup and re-import to new back-end file linked to same front end.
- Review front-end form by form and remove / delete any items not required.
- Decompile & recompile front end.
- Split the 35 table back-end into 35 separate database back-ends all linked to front end. This allowed me to track which tables were crashing & BINGO! 98% of the time it was one table "TBL Jobs" which was crashing. Unfortunately this is my main table.
- Since TBL Jobs was identified as the culprit I have been moving it's fields to other tables where possible to reduce the interactions with this table until hopefully I can isolate the field and ultimately the process causing the issue.
- This process ongoing but is insanely laborious, done on a night time and the knock on effect is my major changes to the table structures are causing lots of bugs for the users of this live system.
- Coded most forms to auto close after a few minutes so the back-end tables are not being accessed unnecessarily if the system is not in use. (main menu form does not access any tables in the back-end)
All of the above has not improved the crash rate at all.
I would like to set up an error log which can help me pin point where the issue is coming from and ultimately allow me to fix this 10 year labour of love that is my Access magnum opus.
Suggestions welcome.
Thanks in advance
Ian