Access 2002 front-end application crashing due SubForms
Posted on 2008-10-09
I have an MS-Access database that was first created on Access 2000 a couple of years ago. Since then it has followed me from company to company and now exists in MS-Access 2002 (as the front-end application) with SQL-2005 as the backend database server. The front-end is an MDB file, connecting to the data via Lined Tables.
I have always encountered a strange habit for this application to crash for no apparent reason. Hasn't been too much of a problem whilst I was the main user. Unfortunately, as it is now about to be rolloed-out to several users (20+), I really need to fix the annoying habit of crashing.
I have long since believed it to be related to the large number of SubForms that are being used, but as I need them I cannot remove them.
Most Forms include TABs that often have a SubForm on each TAB (hence 3-4 SubForms per Form)
(each SubForm is a simple tabular affair, usually a read-only snapshot - but sometimes an editable dynaset)
The crash requires MS-Access to be restarted and usually happens when buried about 3-4 forms deep into the application (form nesting gets to a max of 5 in extreme situations, but normally 4 is the most).
On some of the busier forms I have just been to add new (temp) controls so that I could see the control count - on no form did this exceed 200.
When re-starting I may reboot the PC and will sometimes do a Compact & Repair. Neither seem to really help much. In fact, clicking through the screens more slowly seems to be the biggest aide to prevent crashes of this sort (???)
If I remove a couple of SubForms .....No Crash
...but I need them
I read a post in EE that suggested a couple of actions
1. Remove all Linked Tabled and then re-attach them (there are about 70 of them)
2. Create a new MDB and copy all Code_Forms_Queries in and recreate Links
...I've done both
It helped make the MDB a bit smaller, but still crashes
Does anyone know how I can track what is the cause of this problem
...also, how it could be fixed
I'd really appreciate any responses on this
Until recently the DB was MS-Access-2000, but since I had to re-create it, in (2) above, I made it MS-Access-2002 and the same thing happens.
Thanks for your time looking