Database keeps corrupting

[Ben M]

I have an Access 2000-2003 database that is continually corrupting. Even if i
make a copy and open the copy on a single machine, just doing a single action
like opening a table or a form can corrupt the database.

I have tried compacting and repairing which doesn't help. The database is
quite a complex one but is not split into a front end and a back end. There
is a lot of code in the database. The database is about 6 years old and about
33mb. It is generally accessed by between 2 and 5 users using Access 2003 on
a XP OS.

Can anyone give me any help as to what i could do to make the database more
stable? At the moment, almost any design change seems to trigger it to
corrupt.

Ben

 

[Roger Carlson]

Look at these corruption links:
a.. Microsoft Access Corruption FAQ at
http://www.granite.ab.ca/access/corruptmdbs.htm
a.. Recovering from corruption
http://allenbrowne.com/ser-47.html
a.. Fix Corrupt Access Database v4.5
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=25

Have you tried creating a NEW database and importing all the objects from
the old to the new?
Have you tried splitting the database?
Have you tried decompiling the database? (you can find instructions here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=253)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Larry Linson]

MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm has much
information on multiuser Access database applications, including avoiding
corruption and performance. You will find several other sites, rich with
Access information, in the Resources list at
http://sp.ntpcug.org/accesssig/.

The immediate first step I would advise is to split your database into front
end or FE (queries, forms, reports, macros, and modules) with a copy on each
user's machine, and a back end or BE (tables, data, and relationships) on a
shared folder (because of the number of connections available, preferrably
on a server). Having multiple users logged in to the same front end or
monolithic database substantially increases the probability of corruption --
some people go for years without experiencing much or any corruption in that
environment, and then some minor change will trigger frequent corruptions.

Other steps may include creating a new empty database and importing each
object from the FE into a new FE, doing the same for the BE; simply updating
your version of Access with all current Service Packs, or moving to another
version of Access; using the SaveAsText and LoadFromText to eliminate the
possibility of "silent corruption" that is hidden some of the time and
occasionally manifests itself in the corruption you are seeing. But these
are all covered, and in detail, in the references above.

You will, no doubt, receive advice that "Jet is unsuitable" and that you
should upsize to SQL Server. In certain cases, that can help with the
problem, but it is no panacea or cure-all, and there are many approaches
that usually alleviate the problem with Jet, without investing in the time,
effort, and expertise necessary to install and maintain a server database.
Usually, in this newsgroup, such advice comes from a single poster, labeled
by many as "just a troll" because his posts lack technical depth and
substance.

Larry Linson
Microsoft Office Access MVP

 

[Ben M]

Thanks Larry and Roger for the information. It is very helpful. I have
already tried on several occasions creating a new database and importing the
objects from the old database into it. However the new database always
corrupts in the middle of the import process at different points.

I'll look into the other options you talked about and do some more research.
I do think splitting the database may not be practical as it is likely to
slow the database down so much. This database is used on a large University
Network, so I'm not sure how fast their network is.

Ben

 

[Paul Shapiro]

That's the indication that there are one or more corrupted objects. Try
importing subsets of the objects until you can directly identify the
corrupted ones. If you've already split the database, the tables are not the
issue in the FE. You could start by importing all the forms. If that works,
then try all the reports, etc. If any group fails, try again, importing half
the objects. Etc.

Once you identify the corrupted object(s), create a clean database and
import all the good objects. Then you can either import older copies of the
corrupted objects from backups before the corruption, or re-create them.

 

[Ben M]

Hi Paul,

Thanks very much for your reply. It was very helpful. I have located one of
the corruption issues as being in one of the tables (I'm not sure if this is
the only one!). Every time i open the table, the database corrupts. Do you
know of a way i can locate the corrupt data and get rid of it without the
need to open the table?

This is one of the main tables which i will not be able to replace from a
back up.

Ben

 

[Paul Shapiro]

You can see if any of Allen Browne's Corrupt Repair tips apply:
http://www.allenbrowne.com/tips.html

I guess you've already tried importing just that single table into a new db,
and it fails? You've also probably done a Compact and Repair, without any
improvement?

Other possibilities might be:
1. http://support.microsoft.com/kb/283849 offers some suggestions, including
exporting the table from Access in delimited text format and then
reimporting to a new table.
2. Try linking to that table from a new db. If you can open a recordset with
VBA, you might be able to loop through the records inserting them into a new
table in the linking db. When it fails, try restarting from a later record
to see if you can skip individual corrupted records.
3. You could see if another application can import the data from that table,
like Excel. Probably not, but you never know.

Last possibility is one of the commercial Access recovery services. They are
probably expensive, and I've never used any, but several have excellent
reputations. Search on google since I don't remember any names.

 

[Ben M]

Hi Paul,

Thanks for your response. I am currently using a make-table query to copy
the corrupted table into a new database. For some reason, this seems to be
working. I then delete the corrupted table from the original database and
import it back from the new database.

Don't know why but this seems to have fixed some of the problems. More than
one of the tables is corrupt so I'm going through all the corrupt tables.
There may be other corruption issues but this certainly seems to be resolving
some of them.

Ben

 

[Tony Toews [MVP]]

I'll look into the other options you talked about and do some more research.
I do think splitting the database may not be practical as it is likely to
slow the database down so much. This database is used on a large University
Network, so I'm not sure how fast their network is.

You have a rock and a hard place choice.

1) If you do not split the database
a) it will be next to impossible for you to do any development work on
the objects while others are in the database

b) The chances of corruption are greatly increased when people are
sharing the objects other than tables.

c) You will have weird and interesting problems.

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite
nicely.

2) If you do split the database yes performance will be slower.
However you can get around all those problems with varying amounts of
work. Some solutions are real simple such as opening a bound form at
all times.

Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

I do think splitting the database may not be practical as it is
likely to slow the database down so much.

And constant corruption doesn't slow things down?

Every Access application with more than one user should be split. NO
EXCEPTIONS.

Every Access application where the user needs to get updates to the
forms/reports from someone else should be split, even if it has only
one user. NO EXCEPTIONS.

Every replicated Access app should be split. NO EXCEPTIONS.

 

[Larry Linson]

Thanks for your responses. It certainly seems like you
both want me to split the database as the best course
of action.

Knowing these two people from years and years of interacting with them in
newsgroups, I can assure you that they don't think you should split "because
they want you to" -- they are advising you to split because not doing so can
cause you a great deal of stress, strife, and trauma. People may go for
years without problems, make some small (apparently-innocuous) change, and
<BANG> frequent corruption. Splitting also expedites "offline development"
and being able to replace the front-end without having an "exercise in
copying data" as part of the procedure. I caution, "You don't know when that
will cause Access to rise up and bite you in the tender places."

Larry Linson
Microsoft Office Access MVP

 

[Ben M]

hi Larry

Thanks for your response. I am continuing to have problems with my database.
I create two new databases, a backend and the front end.I import all the
tables into the back-end and all the other objects into the front end. If any
objects fail to import because they are corrupted I use the loadfromtext
command to create duplicates. I then have a brand new database both front-end
and back-end. However almost as soon as side start to alter any of the
objects or even open one of the forms it corrupts all over again. The message
I get is " Microsoft access has encountered a problem and needs to close".

I can't seem to get out of this problem. It will take me too long to
recreate the database from scratch so this is not a viable alternative, and
now I am not sure what to suggest to the client. If I cannot fix it they will
be unable to continue using the database.

They are using the XP operating system with service Pack 3 installed, and
office 2003 also with service Pack 3 installed. They also have the latest hot
fixes applied. I would be grateful if you or anyone else could suggest
anything that may help.

Ben

 

[Ben M]

I forgot to add that when I take a copy of the database home and open it
using Office 2007 on Windows vista, it works fine. It only seems to be
unstable in office 2003 on XP.

 

[John W. Vinson]

I forgot to add that when I take a copy of the database home and open it
using Office 2007 on Windows vista, it works fine. It only seems to be
unstable in office 2003 on XP.

You may need to completely uninstall Office on the offending machine and
reinstall it. The problem may not be with the database but with the instance
of Access.

 

[David W. Fenton]

I am continuing to have problems with my database.
I create two new databases, a backend and the front end.I import
all the
tables into the back-end and all the other objects into the front
end.

Try importing *everything* with LoadFromText. Otherwise, you can end
up silently importing corruption that Access doesn't detect during
the import process.

 

[David W. Fenton]

You may need to completely uninstall Office on the offending
machine and reinstall it. The problem may not be with the database
but with the instance of Access.

Or possibly a hard drive problem?

 

[Ben M]

hi John

Thank you for your comment. I don't think the problem is with the instance
of acces itself. the database corrupts on all machines with XP and office
2003.I even took the database home and tried it on my home machine and it
corrupted when I used it with XP and office 2003.

Ben

 

[Ben M]

hi David

Thanks for your suggestion. I'll give it a go.

Ben

 

[Ben M]

Conclusion

I've finally managed to sort out most of the corruption problems. I started
by deleting all the records in the tables that crashed the database when
opened. This didn't make any difference. I then went through the fields
deleting the fields to see which ones caused the crashing. I found out that
the crashing was being caused by some look-up fields within the table. Not
all the look-up fields were causing problems, but some of them were. I then
simply changed all the look-up fields that were causing problems to
non-look-up fields and this fixed the problem. I did this for each of the
tables that crashed on open.

I then noticed that the hotfix for look-up fields had not been installed. I
installed it, but it didn't make any difference to the tables crashing on
open. I still needed to remove the look-up fields and change them to
non-look-up fields. I had also, before that, installed service pack 3 for XP.
This also had not made any difference.

There is still some forms with corruption problems, and i am fixing these by
creating new forms and replacing them. I tried using the LoadFromText command
but this didn't stop them from crashing.

I have now split the database into a front end and a back end, and each user
has a copy of the front end on their local machine. I am using the autoFE
updater provided at http://www.granite.ab.ca/access/autofe.htm. Everything
seems to be working much better now.

Thank you all for your help.

Ben

www.benmango.co.uk

 

[John W. Vinson]

I found out that
the crashing was being caused by some look-up fields within the table.

That's VERY interesting, Ben. Another arrow (a sharp one!!) in the quiver for
those of us who dislike Lookup Fields...

Of course, as you now realize, lookups are never necessary; but this is just
another good reason to avoid using them entirely.