Solved

Access 2002 front-end application crashing due SubForms

Posted on 2008-10-09
4
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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