Solved

Access XP keeps corrupting the program database. How to prevent this?

Posted on 2003-11-14
15
475 Views
Last Modified: 2009-12-16
Almost without exception Access XP will corrupt the program database after a day of programming. I need to compact my database every half hour (or hour tops).Things get more and more sluggish (the database size grows extreme also btw) and then after a while it will just break down. Access crashes and my database is broken. In many cases even beyond repair or up to th epoint where I need to import everything in a new database (and then I have to put the security back on).

This is really incredibly annoying. I have installed every update for Office XP that I could find, but to no avail. I have been searching the internet for months how to fix this, but I have gound nothing besides the Microsoft knowledge base nonsense which basically claims that all corrupt databases are caused by faulty networks or faulty harddisks. I have used (and still use) Access 97 and I have never had this kind of mess.I thought only Access 95 was such a lemon (which suffered the same crashing and loss of work duu to databse corruption)

I bought a whole new computer to try and fix this. Again no luck. It still crashes. I tried editing the forms/VB on the local disk or on a network drive. That made no difference either.

Oddly enough I have no problems at all with the data database (containing the tables) going corrupt. It's only when I'm editing the database containing the forms and VB that the whole lot bloats out of control and then after a few hours crashes.

Personally I think it has to do with use of the VB editor or with compiling or not compiling things. When I program in VB I have more problems (or at least they appear to cause problems quicker)

I assume the two problems are related:
1) exploding program database filesize
2) corruption of the program database file

Please, please, please help me get rid of this.
0
Comment
Question by:patrickl
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 2

Expert Comment

by:glennkerr
ID: 9749590
Here's a good resource for corrupt dbs....

http://www.granite.ab.ca/access/corruptmdbsymptoms.htm

Do you have more than one version loaded on your machine?
Which versions?
0
 
LVL 5

Author Comment

by:patrickl
ID: 9749642
Wow thats a whole list of possible symptoms. Incredible that something like that is not on the knowledgebase.

The machine runs Acces 97, Access 2000 and Access XP. I hope that's not the problem, because I really need them all.
0
 
LVL 5

Author Comment

by:patrickl
ID: 9749680
Actually the message I get is:

Microsoft Access has detected a corruption in this file. To try to repair the corruption, first make a backup copy of the file. .... If you are currently trying to repair this corruption then you will need to recreate this file or restore it from a previous backup.

Unfortunately I get this message during a repair operation and it seems all VB code is lost. I can still open querys, but on forms there is no VB left.
0
 
LVL 2

Expert Comment

by:glennkerr
ID: 9749886
There's a known issue with copy and pasting reports, forms in Access 2000 when you have AccessXP installed (vba332.dll version 3.3 I think).

I just tried looking for it, but couldn't find it.  Basically, under one scenario you copy the code into a text file, then import your report/forms into a new database, set the Has Module property to false, decompile, then add the modue from your text file & recompile
0
 
LVL 4

Expert Comment

by:inox
ID: 9751734

can you try the following:
seperate your db in an "Data-DB" and a "User DB". the DataDB consists only recorsets (Tables) and the  "User DB" has the rest (queries, Forms, Modules etc). The "UserDB" links all recordsets from the "Data-DB".

PS: I have no idea for the cause, I'm just interrested because I was convinced Access to be a quite robust database
What I mentioned before is just an idea (and not to much work) in the hope results help to go ahead
0
 
LVL 5

Author Comment

by:patrickl
ID: 9754658
@glenkerr

I'm not copying and pasting reports in Access 200. I'm using Access XP and I'm desinging reports and forms and I use the VB editor. As far as I know I cannot decompile in Access XP. If you know how I'd sure love to know since I miss that trick a lot. I

@inox

That's what I mean with "program database" and "data database". As I said the data database has no problems. I have problems with every program database that I maintain.


I can't beleive I'm the only one suffering from this problem. How bizar.  I experience crashes in every configuration that I have tried. I have several PC's with Windows 2000 and Windows XP. Some of these PC's are not mine so they would not have a completely different setup from what I have. But then again, probably not many people program a lot of VB in their Access apps.

Another symptom I have been getting since I switched to Windows XP is that Access crashes often when I try to "compact and repair" a database. I first need to close access, reopen and on ly then can I compact and repair. That doesn;t happen always, but often too. But I have that only on one PC, so I guess that's a more specific setup problem.

Maybe I should try a clean install with only Access XP. That would be terribly annoying since I really use all 3 versions of Access.
0
 
LVL 4

Expert Comment

by:inox
ID: 9755296

It won't be a help to you but we have hundreds of Access DBs also with extensive use of VBA code working for weeks without interruption never got experienced severe problems.

There seems to be some kind of programm fault. (I don't believe to  have a version problem since it happens on all Access versions) Some programming experience is necessary to find the fault reason. Maybe some questions will help:
- is the fault reproducable does the faultevent happen on specific functions/operations?
- are errorhandlers setup properly?
- Are object-instances always deleted?
- Are Accessobjects (Forms, reports etc) created, deleted or modified programatically
- Are specific libraries / ActiveX components used?

 
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Expert Comment

by:glennkerr
ID: 9756706
Good set of q's inox.  That's going to help.

As an aside - if Access is crashing when you select Compact/repair, then you most definately have a corruption problem (boy, I really shouldn't use the word definately here, but It's sounding like it).

If you have the same problem on all machines with the same database, then it points to the db.

If it's all db's that you have, I'm suspisious of it really being a corruption problem, unless you reuse a lot of the same forms or reports.

Do you ALWAYS get an error when you compact/repair?

If so, then try this...

Open a brand spanking new db, with nothing in it.  (I mean NOTHING!).
Try Compact and repair.  If there's an error here, then you probably have an installation/memory/HDD problem.
If successfull, then set up your table links to the data db.  Try Comp/Repair again.
Then start adding all the forms, reports etc. untill you get an error when you comp/repair.

The key is to be able to reproduce the error with 100% consistency.  When you can do that, then you can start from scratch, and add items on a new db untill you get the problem.
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9756935
Patrick,

this has solved similar problems for me before:


1. Make an empty database

2. import everything from the faulty database

3. test


Regards,
Sven
0
 
LVL 2

Expert Comment

by:glennkerr
ID: 9757088
I just want to clarify my point above - when you start adding the forms and reports, etc., you do it one at a time (or at least in groups) to try and narrow down what ones are causing problems (and are probably corrupt).
0
 
LVL 5

Author Comment

by:patrickl
ID: 9758276
The fact that I have a corruption problem is very clear to me yes. That's the point of my question "How to get rid of the corruptions".

Disclaimer: the caps words in the are following are meant to clear up not to offend.

The problem is not so much about how to deal with the database AFTER it's corrupted but what to do to PREVENT corruptions from occurring all the time.

Also I do not have any problems in RUNNING the applications (they run fine on tens of computers without a glitch). The problem occurs when PROGRAMMING them.

Reproducing the problem is kinda hard. I mean, it does happen every day, but it can take between 1 hour or 8 hours of programming for Access to crash. It's not a certain chain of events. As I detailed in the question:
- I edit the forms and VB code behid it for a couple of hours
- The database (holding the forms and VB and such) bloats to very large sizes (say from 4MB to 40MB)
- Access crashes
- Results will then vary
0
 
LVL 5

Author Comment

by:patrickl
ID: 9758324
damn, wasn't done when the previous comment got posted (guess I should not hit tab and then enter)

anyway, I was left at "Results wil then vary" By that I mean that the database can get corrupted in several ways:
- Compacting will crash Access (this doesn't happen always, but often enough to be a nuisance)
- Opening the database with several users will no longer be possible because it slows dow to a snails pace
- VB code will be unavailable completely (and access will give the error I mentioned above about needing to recreate the database from a backup)
- Most of the times a simple Compact+Repair fixes the problem

As far as reproducing goes, I think it has to do with editing VB modules (behind forms and reports mostly) and then compiling. It can take a while, but eventually Access will die if I use the VB editor enough. I'm pretty sure the problems are related to the VB editor. I remember something similar with Access 95 (luckily I managed to avoid that version since it was easily the worst Access version ever)

There are no special libraries in use. Well, maybe the DAO module is special and the fact that most applications are converted from Access 97.

To repair the broken databases the only option is to reimport everything. Otherwise the module errors just will remain. Compact+Repair then does not solve the problem. It's like the problems you could encounter in Access 97 that were solved with doing a Decompile. Since Access XP does not have the Decompile option anymore I guess that means it keeps piling on the VB problems up to a point where it breaks. Why they took away the Decompile function is beyond me!

Thanks for all the help thinking along with me. I still find it really weird that none of you have ever experienced problems like this. I have seen this behavior in at least 7 PC's with very different configurations and with dozens of Access applications (the most VB intensive being the most vulnerable).

Actually the only solution I have found is to clean up periodically with Compiling the project and then a Compact+Repair every half hour or so. That prevents the Access from crashing. If I take to long between clean-up's it's sure to crash and then often it will cause damage. (Beside in the half hour clean-up I make a back-up so I can go back to a lot of older versions)

Three solutions/workarounds I have been thinking about
- Automate the clean-up process (some external program that alerts me it's time to clean-up and then does it)
- Install a seperate partition (using a boot loader) with only Access XP and the bare essential software (and see if that helps)
- Stop using Access XP and switch to Visual Basic or Delphi/C++ Builder
0
 
LVL 5

Author Comment

by:patrickl
ID: 9758340
Another idea I was thinking about is to export the whole of the Access object definitions to some XML format (or something) and then recreate all objects from that file in a clean database. I kinda have the feeling that importing with Access actually can import corruption along with it. Does something exist already maybe?

I have a simple export function that I use to do quick text file comparison (Windiff or something) to be able to tell the difference between application versions (Total Access Detective can take so long)
0
 
LVL 2

Accepted Solution

by:
glennkerr earned 500 total points
ID: 9758935
Decompile - I read somewhere on the MS web that just changing the name of an access db causes a decompile, but I don't remember if that was 2000 or 2002.

If you want to save your db (just structure) in a text format, go here...
http://www.mvps.org/access/modules/mdl0045.htm

I had a db that caused Random errors (anyone of 15 different errors) on different machines, and couldn't find the problem.  However, a reboot solved the problem 90% of the time.  Over time, the problem got worse.  I was determined to find what exactly caused the problem (and was convinced it was  a corruption issue).  I was eventually able to reproduce the problem by selecting any order, then opening and closing a specific Work Order report 3 times, then the Invoice report.  It took a while, but allowed me to eventually fix the corruption problem.  In the end, I had to completely reproduce the 2 reports.  (which wasn't bad, since I could cut and paste all the text stuff, like recordsource, etc - but dont cut and paste controls, etc.).

Did you install Access in the correct order?  Access97 + updates, then Access2000 plus SR1a, SR3, then Access2002?
0
 
LVL 5

Author Comment

by:patrickl
ID: 9759736
Hey Glen, you are full of good links man! The undocumented SaveText method they use for the text backups is really cool! I would guess that using that to dump and recreate the database is a sure way to get rid of the corruption that accumulates in the database. Much like the Decompile function did for Access 97 and 2000.

BTW changing the database name will "clear the compiled state", but that's not the same as decompiling with the Decompile function.

Maybe the difference in experience that I have between Access 97 and Access XP is all a result of the lack of Decompile. Now that I think of it, decompiling helped me get out of a lot of problems similar to the ones I'm facing now. The lack of a decompile function in XP just means I can;t get rid of them and corruption will keep on occurring. In Access 97 I would decompile the database on every version I'd install. So it would always stay clean.

I'm not sure in what order I installed the Access versions, but I would assume that's the order I used yes.

So, I guess preventing corruption is like asking Microsoft (or myself for that matter) to write bugfree software. I guess I just have to learn to live with them. Periodically cleaning up (recreating) my program database will hopefully keep me from running into real harm though.

Thanks all for helping me out. I'll accept glenkerrs response as the answer.

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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

17 Experts available now in Live!

Get 1:1 Help Now