Access - Common db Corruption Causes

Can someone please advise on what some of the most common causes of a db corruption?

Thanks
exp vgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SylvainDrapeauCommented:
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 !
0
exp vgAuthor 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.
0
SylvainDrapeauCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

exp vgAuthor 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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@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.
0
exp vgAuthor 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.
0
SylvainDrapeauCommented:
@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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<@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.
0
exp vgAuthor 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.
0
Dale FyeCommented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
exp vgAuthor 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.
0
exp vgAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.