Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2002 front-end application crashing due SubForms

Posted on 2008-10-09
4
Medium Priority
?
268 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…
Suggested Courses

886 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