Database keeps corrupting

[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.

 

[Tony Toews [MVP]]

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.

But OTOH I have a client with many lookup fields in many of the 100
tables in the BE. The BE MDB is 200 Mb with 600K records in the main
table. No crashing of the database when opening tables.

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/