Access - Common db Corruption Causes

exp vg
exp vg used Ask the Experts™
on
Can someone please advise on what some of the most common causes of a db corruption?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sorry for the link dump, but no point in re-writing what has already been written :

Specific for MS Access : http://allenbrowne.com/ser-25-01.html

Hope this helps !

Author

Commented:
I read through - thank you for the link. Further clarification is appreciated on whether a db can be corrupt file - without realizing it - until final shut down - that may affect prior functionality without realizing it. That is - queries, macros, etc. - might not have been optimal for a file.

I do a lot of file transfer - so the origin may have begin with this.
Do you mean if the DB can become corrupt while using it but you don't notice it until you close and open it again ?

About your last comment, if you copy a database while it's being used (the file is open), the copy might be corrupted because it wasn't synced, but the original should be ok, unless your copy operation force close it.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Exactly - can it become corrupt while using it - so some of the results are flawed - but I did not realize this - until I could not open the db at all.

I do save versions - but none of them worked.

Thanks.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
@exp vg

Some common causes:

1. Using a JET/ACE database over a WAN - 95% of the time, a WAN does not meet the network needs for the database engine.

2. Using a JET/ACE database over a wireless connection - Wireless is subject to dropouts and high latency at times.

3. Open a JET/ACE database with something other than Access, like a word processor.

4. Anti-Virus software that scans and attempts a "fix" of the file.

5. Network issues - NIC's with bad drivers (not so common now), NIC's with diag or power modes that cause the NIC to disconnect from the network, bad cable, router, or switch, etc.

6. Inactivity timeout on mapped drives when a DB is accessed over a network.

Those are the most common.

Jim.

Author

Commented:
Thank you JIm. I am using a work computer - so I would think that a lot of protection issues are in place. However, I reached a point where I was not able to open the db - it initially hesitated while opening - but then I could not open this.

It seems corruption can happen even when there is a work computer.

To confirm - if the db had been corrupt for a while - is it possible that underlying queries/calculations were affected - but I was not aware.
@Jim Dettman : Thanks, and sorry about that.

@exp vg : You have to remember that a database opened for writing (in use) cannot be copied, so version saving while the database is in use cannot be done, not with Access at least. This should not crash the database, but the copy will be unusable. A corrupt database could theorically be useable and give flawed results but it is unlikely. If you dump garbage to a database, chances are low that you will only change values while leaving the structure untouched. I don't think a corrupt database could go without notice, as the queries would start giving errors. A database usually becomes corrupt at the time the files are closed (synced) so that's why you don't notice until you open it again.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<To confirm - if the db had been corrupt for a while - is it possible that underlying queries/calculations were affected - but I was not aware. >>

 Possible, but not common.  Usually corruption causes things not to work and it is fairly obvious (you get errors).

 <<However, I reached a point where I was not able to open the db - it initially hesitated while opening - but then I could not open this.>>

 If this is a database in MDB format, you can try using JETCOMP.EXE, which is a standalone utility that does a compact and repair without opening the DB normally as Access would.

 To open a database, Access depends on the Database Header Page being sound.  If it's not, it will not be able to open the database.    JETCOMP.EXE does not try to log into the database, but works on it directly.

 A backup though is usually the best recovery.

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<@Jim Dettman : Thanks, and sorry about that.>>

 No problem at all.  Just something that EE has changed policy on and we are slowly spreading the word as it comes up.

Jim.

Author

Commented:
Thanks Jim

So - if I am using the db - that eventually became corrupt - will all the objects created in it be sound? I did get errors - but I took them as formatting errors in what I was creating - but from what you were telling me - this may have actually been a warning.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
just to pile on a little.

When I'm working on a database, one of my standard procedures is to open the latest version rename it with an extension that includes the current date in format:

path\mydb_2015-09-11.accdb

Then I do all of my new work in this database.  When I am ready to deliver a new version of the database, I simply drop the date file extension.  This way, I can go back as far as I need to to find a working version of the front end.  I also do daily backups of my BE databases, so that I can go back as far as I need to to find a good backup.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
So - if I am using the db - that eventually became corrupt - will all the objects created in it be sound? I did get errors - but I took them as formatting errors in what I was creating - but from what you were telling me - this may have actually been a warning.

 This is difficult to answer because once inside the database file, there are many different areas.   Corruption may be limited to a single table or index, but if it is one of the system tables that keep track of everything, then you will see wide spread problems right away.

 or the corruption may be in the VBA project file, which is also stored in the database file, so you would only see problems when you use things that have code associated with them.

 When thinking about the database file, keep in mind that it is not only the data, but also the structure that houses the data and information about that data (metadata) that is stored there.  So depending on where the corruption is and how much of it there is, you can go from having a problem with a single record in one table, to not being able to use the database at all.

Jim.

Author

Commented:
Thanks Jim - this is what I have been seeing. I do not want to take up more of your time.

And unfortunately - what I have learned is that it does not matter if it is on a work computer either - which I thought would be more secure.

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial