Solved

Access database form keeps corrupting!

Posted on 2007-12-04
15
514 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
  • 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
 
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 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now