Access appears to stop responding unexpectedly

[Paulie G]

Hi,
I've searched the forum thoroughly but can't find anyone else that's experienced a similar problem.
I have a database split into FE/BE with the BE stored on a network drive and the FE on the local hard disk.
My problem is that when using one of the forms (which happens to be the busiest form, with 2 subforms and 4 more subforms on a tab control), Access unexpectedly freezes when you click on a button control or navigate to a different field on the form. Most of the time, Access just hangs but occasionally it will close down without giving any error messages. When it does hang, Access is showing as 'Responding' in Task Manager.
When I was developing the database just on my machine, I wasn't experiencing any problems like this, it's only been since I've been testing it over the network.
I have compiled the code, and I corrected the only error that was returned. I have compacted and repaired the front and back end numerous times.
I initially thought that it was a performance issue, but the unexpected shutting down of Access leads me to believe that it is something else.

Has anyone else experienced similar problems? I would very much appreciate some suggestions as to how I can rectify this problem.

I am using Access 2003, but I'm afraid that I don't know much about the workstation specs (other than they are all using Windows XP).

Many thanks,
Paul

 

[RuralGuy]

You could try importing your db into a new db. This might eliminate some corruption. There is also the /decompile command that you can try. Do you have a timer event running anywhere?

 

[Paulie G]

Hi RuralGuy, thanks for your reply. Well, I tried a decompile today, followed by the compiling of my code and compacting and repairing a few times, but that didn't appear to solve the problem. There are no timer events running anywhere I have been tearing my hair out all day with this, but I think that I have identified when the problem happens. There is the main form, a subform that runs a query based on the main form's record, and a further subform that runs a query based on the record selected on the first subform. When I navigate to a different record on this second subform, Access shows 'Calculating' on the status bar, I can't do anything in Access, and then it quits without warning. There is also a tab control with 4 more subforms, 3 take their record source from the main form, and the 4th takes it from the second subform (the problematic one). Sometimes the same problem will occur when navigating between records within the subforms on the tab control. Does this additional information help identify where the problem may be? I'm inclined to believe that it may be something to do with my queries or possibly the database structure, since the other forms in the database, although slow to load, otherwise work relatively well. Again, thanks for any help that comes my way.

 

[RuralGuy]

I think it is time to post enough of your db so we can look at it and do some troubleshooting. Remove any sensitive data and zip it up before attaching to a post.

 

[xBug]

make a log file...

 

[Paulie G]

Thanks for the responses. Alas, the file is too big to attach! So I've uploaded the .zip file to http://rapidshare.com/files/262890029/New_Folder.zip.html If you do get a chance to take a look, that would be fantastic. As I mentioned, it runs ok when the back and front end are on the local drive, but is problematic with the back end on the network. @ xBug - I've not come across that procedure before. How do I go about creating a log file? Thanks again for you help, Paul

 

[RuralGuy]

Were you going to share an Administrator name and password? You can PM me if you want.

 

[RuralGuy]

Nevermind...I got it.

 

[RuralGuy]

Which form has the most problems? Have you tried putting your workgroup file (.mdw) on each local machine instead of on the shared PC?

 

[RuralGuy]

Could it be the Search form by chance? Have you implemented any of Tony's suggestions from this web site? Especially the persistant connection.

 

[RuralGuy]

BTW, nice complicated, polished system you have there. I don't understand it all yet but I can see a lot of work went into the development.

 

[RuralGuy]

I'm also going to strongly recomment that all of your bound forms be bound to *queries* of the tables rather than directly to the tables. There is no penalty and in fact when you create a query, Access determines a strategy for returning the recordset and saves that along with the SQL for the query so there is a slight speed advantage.

 

[RuralGuy]

Well I put the BackEnd and the WorkGroup file on another PC and hooked up everything. Of course I do not have the network traffic you probably have but no problems so far. This may sound dumb but one of the speed up suggestions is putting the network share as close to the root as possible. Believe it or not, it can make a difference. Yours seems to be 4 or 5 levels deep. Just a suggestion.

 

[Paulie G]

Hi RG, Thanks a massive amount for taking a look at this for me, I really appreciate your time. The problem form is the frm_Cases. When a suspect is selected on the frm_CS_and_Suspects_Subform, the frm_SO_and_Offences_Subform shows the offences for that suspect in this case (for the benefit of anyone reading this, the database is supposed to be a tool for Police Officers working on making decisions on what to do with suspects brought into custody). On the form, a case can have multiple suspects who in turn may be accused of multiple different offences. When I switch between the different offences on this subform is when I find Access stops responding. The Search form, although slow to load initially, works just fine. From the sounds of it, there is some work that I could do structurally to improve performance. I will give your suggestions a try and report back. Also, thanks for your positive feedback! This is my first attempt at creating anything in Access, so I'm hopeful that I can rectify the problems and avoid them in my next attempt. Thanks, Paul

 

[RuralGuy]

Are you saying just select different records in the frm_SO_and_Offences_Subform causes the problem? I've selected record 3 (case numner 3) SurName - TYUI and there are 7 offences in your sample. I've gone through and select each record with the cursor and the nav buttons with no ill effect. I'm working off of two machines right now with the mdw file on the remote PC.

 

[Paulie G]

RG - yes, that's exactly when the problem occurs. The fact that you're not encountering the same problem suggests that it's a network issue. Have you made no changes at all? (other than putting the files close to the root on the network) I will endeavour to do some more testing today. I will also implement the suggestions that you have made. Assuming that it is a network issue, are there any additional steps that I could take to make this less of an issue? There will be 4-8 concurrent users across 4 locations, so it is fundamental that it can operate over the network. Our network can be tempremental at times, but generally the speed is good, so I'm hopeful that a solution can be found to make this database stable to use. Thanks again so much!

 

[RuralGuy]

When you split the db and have the BackEnd only available over a network it stresses all of the performance areas. You might also look into using a WHERE clause on your RecordSources rather than just putting a filter on the form. Anything you can do to limit or minimize the network traffic will improve performance.

 

[Paulie G]

RG, An interesting development today - when I took the original database that I was working on (very first backup copy - about 90% of the tables and 5 forms, including the Cases form) and tried it out over the network, I had no problems at all. Of course, when I tried my latest version again I encountered the same problem. This leads me to believe that it is not the network at all, but definitely something in my form design or code that is causing Access to crash. I am especially baffled by the fact that you were able to get it working without any problems on your network. This leads me to believe that there is simply something very inefficient that is causing Access to time out, as opposed to something fundamentally flawed in the structure or code. Unfortunately, I wasn't able to devote as much time to this today as I would have liked, but I'm going to spend the day on it tomorrow and hopefully crack it. I'll be back again soon. Thanks RG.

 

[RuralGuy]

Getting closer. I've found that IE8 causes major problems on my PC's and have taken them all back to IE7. It even affects Access. Definately keep me in the loop if you can please.

 

[Paulie G]

The Eureka moment - it was an unbound text box using a DMAX expression that was causing the problem. RG - this is in the top right corner of the Cases form. I read somewhere else about someone that had a similar problem and put it down to an unbound text box. Lo, I removed the text box from mine and now Access is no longer crashing! I'm not sure why it was causing such a problem, since it was looking at a table with fewer than 100 records. Unfortunately, I can't obtain the data that I wanted to show using a query since this locks it as read only, so I am looking at alternatives, possibly a one-field subform. At this stage, I do believe that there are more elegant ways to implement what I have tried to achieve here, but I'm finding that it's a steep learning curve with Access. It's frustrating when something that should work in theory doesn't for what seems like an inexplicable reason, but I suppose this principle could be applied to many things beyond Access... Thanks for your time RG, it's been very much appreciated. I will still seek to implement your suggestions to improve performance. If I have any further comments regarding this problem I will post back here again. Many thanks, Paul