Solved

Access 2002 front-end application crashing due SubForms

Posted on 2008-10-09
4
258 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:Mr_N
4 Comments
 
LVL 9

Accepted Solution

by:
csk_73 earned 500 total points
ID: 22677743
Have you tried to use an ADP project (Access Data Project) it's more or less the same but you work directly with sql server tables, queries are SQLServer views, you have a new tab for stored procedures ...

Maybe it can help you if the problem is in the linked tables...

But I think that the problem will be in the nested subforms
0
 

Author Comment

by:Mr_N
ID: 22678022
Yes, I like the idea of an ADP, but that represents considerable changes for me to complete right now
...and some learning as I'm unfamiliar with ADP's ie
- will all my RunSQL code have to change to use syntax more appropriate to MS-SQL rather than MS-Access etc
- I would need to rewrite many action queries as Stored Procedures
- I would need to upgrade MS-Access to talk to a MS-SQL-2005 database (MS-SQL-2000 expected by Access-2002 SP2)
...I did make a basic stab at taking this approach, but found there were a few factors fighting against me

An ADP will be what I use for my next project ;-)

Meanwhile...
As you say, it's more likley to be a problem related to the Nested Forms and use of SubForms that is the cause
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question