Solved

Access 2002 front-end application crashing due SubForms

Posted on 2008-10-09
4
263 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

624 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