Access XP keeps corrupting the program database. How to prevent this?
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.
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.
ASKER
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.
The machine runs Acces 97, Access 2000 and Access XP. I hope that's not the problem, because I really need them all.
ASKER
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.
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.
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
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
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
ASKER
@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.
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.
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?
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.
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.
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
this has solved similar problems for me before:
1. Make an empty database
2. import everything from the faulty database
3. test
Regards,
Sven
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).
ASKER
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
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
ASKER
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
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
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
http://www.granite.ab.ca/access/corruptmdbsymptoms.htm
Do you have more than one version loaded on your machine?
Which versions?