Results 1 to 10 of 10
  1. #1
    Rudas1988 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    3

    Question Back End Crashing. Is there a way to log / view crash report? - Please Help.

    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

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    when be crashes and is 'fixed' by a compact/repair, what is the be file size before the repair?

    There are many reasons why a db bloats
    - constant delete/recreating object such as tables
    - constant deletion/appending records rather than performing updates
    - storing files/images (objects of any kind in an OLE type field) instead of text network links to them
    - row level record locking

    Is there an error message when the be crashes (define "crash")?
    Rebuilding data in Excel and importing back into Access can introduce issues as the data types can get messed up. Perhaps not so much as text vs numbers but what should be integers or booleans coming back as longs, for example

    In order to have an error log, you'd have to have an error and I don't see where you revealed one. To me, a crash is a complete closing of the app with no error message.
    Last edited by Micron; 01-16-2019 at 02:45 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Would the company be willing to migrate the BE to SQL Server?
    Doing so would probably help identify the cause(s) of the problem and would definitely make the system more stable in the future
    It would take time to manage the conversion but should save you a lot of time in the future.

    You mention users being on Access 365. Are users getting the 'unrecognised format' error? See https://www.devhut.net/2018/06/13/ac...gnized-format/
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm seeing this a lot lately - pretty sure there's really no such thing as Access 365 - it's Access for Office 365. The 365 subscription gets you automatic Office updates, which can impact the way Access operates. Not sure if it also will include Access version updates. I've seen posts referring to the use of Access 2019.
    I have Office 365 and my Access version is 2016.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Micron
    Yes its part of Office 365 but easier to just say Access 365.
    If you are allowing regular updates then its now equivalent to Access 2019 retail plus any later updates e.g. dark colour scheme

    MS have further confused matters by retaining the version 16.0 numbering from 2016 to 2019 (presumably because of 365)
    I believe A2019 retail was equivalent to A365 build 16.0.10827.20138

    However, nowadays you see the version number e.g. 1901 (year/month) and build number e.g.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	35 
Size:	4.3 KB 
ID:	36972

    If anyone is interested I have done an Access/Office 365/Windows version checker - see http://www.mendipdatasystems.co.uk/a...eck/4594488974
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Corruption is often incremental - before it goes bang there can be some small, even insignificant, things going wrong so it is possible that a compact and repair does not completely fix a corruption. It might draw it back from collapse, but leave the original corruption 'seed' in place to grow again. You could try compacting more than once, but suspect it won't solve the problem.

    You have identified which table is at issue so I suggest export the data to a .csv file (not excel as you tried), create a new table from scratch and reimport the data. Either use vba and transfertext or the export/import options on the ribbon.

    Other thing to ask is are all your users hardwired to the backend server? - surprising how many users now have laptops and connect wirelessly without thinking about it. Do your users have direct access to the backend? i.e. they are not messing around with it without your knowledge?

    with regards an error log - as Micron says that is too late. I would suggest instead that in the open, close, before update/insert and on delete events for each form that interacts with the table update a log table with the username, formname, action and date/time, plus the PK for the record affected (if new or multiple records then perhaps use some other identifier). This will tell you who took the last action and what they did (or tried to do). Bear in mind that it might be person A who 'sets up' the corruption, person B then triggers it. You might want to put the log table in a separate backend. Reading the log you should see a user open the form, take some actions then close the form.

    Otherwise I'm with Colin, consider migrating the backend to sql server (express is free)

  7. #7
    Rudas1988 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    3
    Thanks for the feedback so far team.

    The error message the users get is "Unrecognised database format 'Z:\IT\ACCESS JOBSHEET DATABASE\DATA\TBL Jobs.accdb' "

    At this point we use the connection test form I built shown below to check which tables / back ends have went pop. Notice the "#Error" on TBL Jobs.

    I've also included a screenshot of the data folder showing file sizes after the last crash. Once compact and repair was performed the TBL Jobs file was 1,192KB. Note the "TBL Jobs Archive" is larger with many many more records does not have this issue.

    Once the "crash" (is this the correct terminology?) occurs all the users need to close their systems so we can perform the compact and repair.

    When the TBL Jobs file is opened we get the following message:

    "Microsoft Access has detected that this database is in an inconsistent state, and will attempt to recover the database......"


    To answer some of your other queries:
    Micron
    - There are no repetitive creation of objects.
    - There are some append queries
    - No images are stored as OLE objects - only a link to a file location
    - I'm not sure what low level record locking is but I will research
    - Access Version: 1808 (Build 10730.20264)


    Ridders52
    - The company are looking to eventually replace this system (within 12 months if optimistic) so not much resource for investment for SQL server. My time is also limited as I work full time in the construction industry now.
    - Yes, that unrecognised database format error occurs when we have the crash problem

    Ajax
    - The TBL Jobs table only contains the live jobs, when a job is completed it is removed from the TBL Jobs and put into TBL Jobs Archive. Which is to say that after about 2-3 months any data (and errors) in the table should have been cycled / removed. Will this get around the problem you're referring to? I will try the export to .CSV if this is not the case.
    - The users all have access to the back end files however I would not imagine there would be sufficient knowledge or inclination to mess with the data - I'm aware that may be very naive of me to assume.
    - Wireless connection. Most users have PCs connected to the network via CAT5 cables, however there are a few laptops connecting wirelessly, some desktop PCs using wireless dongles and also and entire other building across the road from the main production factory which has a line of sight data link to allow Site B to access the network. This is where the account managers (who request the stickers) work. Would this kind of setup cause an issue?

    Thanks again for your help so far, if you have any additional questions or need any more information to better assist I am happy to provide.

    Click image for larger version. 

Name:	IMG_2623.jpg 
Views:	23 
Size:	290.5 KB 
ID:	37066Click image for larger version. 

Name:	IMG_2624.jpg 
Views:	23 
Size:	288.0 KB 
ID:	37067

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Your problem is almost certainly caused by the wireless connections. If you can’t invest In sql server, consider the free express version

  9. #9
    Rudas1988 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    3
    Thanks for the update.

    We are trying to get everyone off the wifi network and on to a wired connection. Most people have been moved but it doesn't seem to be making a difference.

    Any other ideas?

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    nope - most people is not all people, so whilst you still have some on wireless the problem remains

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access crashing when trying to run report
    By griztko in forum Reports
    Replies: 2
    Last Post: 06-05-2018, 04:10 PM
  2. Replies: 3
    Last Post: 08-18-2014, 09:19 AM
  3. Replies: 5
    Last Post: 12-03-2013, 01:25 PM
  4. Replies: 1
    Last Post: 05-19-2011, 10:55 PM
  5. PivotTable View Crash
    By EDEd in forum Access
    Replies: 5
    Last Post: 01-31-2011, 12:58 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums