Solved

Access database form keeps corrupting!

Posted on 2007-12-04
15
534 Views
Last Modified: 2013-11-28
Hi, hopefully someone can help with this.
At our company we have an Access 2003 database which was written by myself.
Recently, I have been having numerous problems with one of the forms in particular.
This form contains several live fields, 2 listboxes bound to a query, and a subform linked by the main ID of the form (Order Number) showing data from another table.
The problem I have is that when the form is opened, a record is found using the filter, and the form is then closed, I am unable to ever open the form again.
There is no error message when the form is opened, unless I try to open it in design view. Then I get the following message "There isn't enough memory to perform this operation, close unneeded programs and try operation again". Also once I received another error saying "No current record" 3021.
The form has worked ok up until recently, and there has been no change to it.
I have tried Compact and Repair, decompiling and recompiling the VBA code, commenting out ALL of the vba code from the form itself (ruling out any VBA errors). And nothing has helped.
I can restore the form from a backup of the database, but it will corrupt again once the above occurs.
0
Comment
Question by:chrismanncalgavin
[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
  • 8
  • 4
  • 3
15 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 20402099
Hi chrismanncalgavin,

By any chance did you install SP3?


Gary
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 20402106
Gary,

Yes I did install SP3 on all machines at the company.
Also may I add, I have Office 2007 installed on my computer and am now using that to edit the Access 2003 database.
As all the other machines run Office 2003 could this possibly cause an issue? I have not converted it into Access 2007 format.
0
 
LVL 46

Accepted Solution

by:
tbsgadi earned 250 total points
ID: 20402140
SP3 was a tragedy for Access ...especially where I was concerned.
Microsoft is in the process of building a hotfix for the problems that I found.
Office 2007 doesn't have the problems (Crashing) that SP3 does.
0
Industry Leaders: 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!

 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 20402161
Can anyone tell me a list of the bugs introduced in SP3 and categorically say that this is what is causing my problem?

I can try another PC with Office 2003 SP2 and see if that works.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20402189
see this link
http://support.microsoft.com/kb/923618

scroll down to Known issues

you may have to uninstall office and reinstall up to Sp2
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 20402477
Ok, just to update have tried the following:

-Using a PC with Office 2003 SP3, uninstalled ALL of Office. Rebooted. Installed Office 2003 from CD (Full).
-Copied the non-working database across and tried to open the form which crashes on opening, still does not work.
-Copied the database with "working" form, before crashing. As soon as I filter to a record, and close the form saving record, the form will no longer open. As before!
-Run compact and repair, recompiled VBA code on both a broken and non broken version of the database using Office 2003 without any service packs. No joy.
-Delete user profile and log on as a fresh username, repeat above.

So does this rule out SP3? Or has SP3 permenantly made changes to the database MDB file that can not be removed even on a non SP3 machine?
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 20402503
Have you not got backups from pre SP3 days?
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 20402511
Yes I do have some backups from then. Will try that.
The issue is that the tables are going to have new data in the live version of the database.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 20402610
you can try this
1.decompile the corrupted db
    http://www.granite.ab.ca/access/decompile.htm

2. Debug>compile
3. Create a blank db, import all objects except the corrupted form
4. import the form from the backup
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 20402769
Thanks for that capricorn1.
Have tried as you suggested. Instead now when I load the problematic form, I get
"Access has encountered an error and needs to close. Send error report" error.
Also importing all objects from the previous database is not as easy as it seems, and it gives an error during the import process (an undefined error, which is totally useless!).
I have a custom toolbar defined, custom startup settings etc which dont get transferred either.

So the problem still remains at present.
I really need to find out if SP3 will actually make irreversible changes to the MDB file?
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 20404479
Doing more research it looks like SP3 has caused no END of problems! Wish I hadn't installed it on our 20ish computers.

I have now created a NEW blank database with Office 2003 Service Pack 2, imported all objects from the corrupt database MINUS the form in question.
I then copied the form from a backed up version of the database prior to installing SP3 and made any modifications to the form that were needed.
Then I recreated the custom toolbar, reset the startup settings to restrict toolbars, recompiled VBA code and Compacted and Repaired the database.

This seems to be working so far, and I have tested the newly created DB on a PC running SP3.
Could it be that because I developed the database on SP3, this caused problems? Do I still need to go around all 20 machines and roll back to Service Pack 2?

So much unnecessary stress.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20404531
<Could it be that because I developed the database on SP3, this caused problems?>

This is a great possibility..

<Do I still need to go around all 20 machines and roll back to Service Pack 2?>

If necessary

0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 20404952
What I did until I received a temporary hotfix from Microsoft (I'm not allows to pass it on) was that I copied the Msaccess.exe from SP2.
It's not perfect, but certainly better than SP3 & a LOT easier than reinstalling 20 machines.

Gary
0
 
LVL 8

Author Comment

by:chrismanncalgavin
ID: 20410062
Thanks for all the comments so far.

I get the idea that I will need to go back to SP2 on all Office 2003 machines.

I still wish to find out for sure if the corruption in the original database was CAUSED by developing on my SP3 machine, or by a user running the database.
Has anyone else had this same issue? The known issues don't seem to list anything similar.
0
 
LVL 8

Author Closing Comment

by:chrismanncalgavin
ID: 31412550
Thanks for all the input. 2 contributions helped in the end, and I have just about finished reinstalling every machine with Office 2003 SP2 after creating a new DB and importing all objects, then recreating the corrupt form.
Ok so far, touch wood.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

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.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

628 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