• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Access 2002 front-end application crashing due SubForms

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
Mr_N
Asked:
Mr_N
1 Solution
 
csk_73Commented:
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
 
Mr_NAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now