Want help to find out how a crashed Access front-end completely hung file server containing back-end

[Ohhdyr]

Hi all,

I suppose this will need some background info. This is bugging me as I don't fully understand what happened and I hope somebody may be able to either shed light on what it was, or at least provide some idea as to what it might have been. I understand the basics of access, vba and databases but am by no means a developer or professional.



I work for a large organisation that favors paying for as little as they can . I don't care if they payed me for this extra work I was doing, it would just make my job easier if I could get it done. However, with tight IT security and not having an interest in any other solutions, I was stuck with what we have. I then looked into building a new access database which would combine the archaic system we currently have in place at our offices which uses multiple access databases that had been progressively developed over the last 15 years.

However, they were built very inefficiently where there are multiple tables containing the same information, but for different devices. We perform preventative maintenance on these devices and any issues that are raised during their use that have to be fixed, require a Jobcard to be created to track its progress. Instead of a jobcards table that can be filtered by device ID, it is a device specific jobcard table. So there are something like 24 jobcard tables (one for each device)... Along with it is also duplicated forms, queries, reports etc. for each table. A bit of a nightmare should you want to make iterative improvements.

To give you an idea, below is roughly what each device has. Now multiply this by 24:

• Jobcard Table - 9 queries - 9 forms - 9 reports
• Usage Table - 13 queries - 4 forms - 4 reports
• Software Table - 5 queries - 9 forms - 2 reports

I wanted to improve this by bringing them together to create a singular table for each system (Jobcards table, Usage table etc.) and filter by device ID in each. By keeping the underlying structure of the tables, this allowed me to be able to bring all the data in the old system across.

Now, to the event.

I was nearing the end of my "development" of the database where I was testing the speed of access to the back-end which was located on a network drive with multiple users. I had done everything I could to speed up the access database with my limited knowledge. I had myself and 3 other users accessing the database (each with our own front-end). As they were beta testing the functionality and speed, I was fixing the front-end issues on my version. I had a form open and was stepping through some VBA code I had created to see where an error was. When I pressed F8 to step through an opening of a recordset, my front-end crashed (bearing in mind I had completed this a couple times before with no issue). Within 10 seconds, the three other users got the same error stating the back-end file could not be located. I force closed my database as it wasn't responding and they closed theirs. I then went to try access the network drive and couldn't. At this point my mind is running overdrive wondering what happened as this network drive was used by other departments in the company too and I thought I had tanked the server completely. Fortunately, about 15 mins later the server became available again and the back-end was still there with the lock file still next to it. Its almost as if the front-end sent of a bunch of requests to the back-end and the server couldn't handle them all.

Now I know its not very specific information regarding the code, as I don't have access to it at the moment as I am out of the office, but I can't help wanting to find out what it was. Strictly speaking my job is not to create or manage the access databases we use, however it is a personal project I was working on to improve our departments use of the databases as it would allow me to implement improvements and provide better oversight. I would love to be able to setup an SQL server and move away from an Access back-end, but that's just not possible. The only other solution would be SharePoint lists.

This happened a couple weeks ago and ever since I have been hesitant to continue development with the server as I don't want this to happen again.

If anybody is able to impart some of their wisdom as to what I had done wrong it would be greatly appreciated.

[CJ_London]

If you couldn’t see the server, sounds like an intermittent network connection issue. Are you connecting via a hard wire or using WiFi?

to prevent the problem happening again, you need to provide some code to check you have a valid connection every time you want to interact with the BE