Access keeps erroring out on one table (MS 2002 SR-1)

Topic

New Reply

I have made a CRM database, a couple of days ago it started to act weird when I was doing searches through the forms. I tried to do compact and repair, but it keeps failing out. I tried to import it into a new dB, but it kept failing out on the one table, named contacts. I have tried to copy the information to excel, to print to export but it keeps erroring out. Is there any hope of trying to repair it? I have worked on the dB and the information for over a year and would hate to lose it.

Reply | Quote

Replies

You haven’t told us what “erroring out” means. Are you getting an error message? If so what is it?

Here is one thing you might try, since it sometimes works when you can’t import a corrupt table. First try importing just the table *structure*, no data, for that table. If that works, try linking to that old table. If you can successfully link the old table, you should be able to do an append query to copy all the records from the old linked table to the new empty copy. You may find that some of the records themselves are bad and this is what is causing the problem. By the way, are there memo fields in this table? If so, try copying only the non-memo fields first. Table corruption most often occurs in memo fields, so you may lose the data in a memo field in some of the records, but that is better than losing the whole table.

Reply | Quote

Charlotte,
Thanks for the quick response. I COULD KISS YOU!!!!! I’ll be darned, it appended. Wow. BTW- The error was ‘Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience.’

THANK-YOU!!!! I really was starting to dispair! I hope you have a wonderful weekend! (Mine looks much better now)
Ninette

Reply | Quote

Glad it worked for you.

Reply | Quote

In all probability, the Contacts table has become corrupt.

Microsoft has a tool that sometimes can repair tables that the built-in Compact and Repair cannot handle. See Jet compact utility is available in download center. It’s worth a try, but don’t get your hopes up too high. If that doesn’t work, but if you are still able to open the table, you can try to find out which records are corrupt – if you browse through the records, you’ll probably get an error message at some point. You could then try to copy the records before the corruptes ones into a new table, and the ones below them too. You’ll lose at least one record this way, but that is not to be helped.
If that doesn’t work either, going back to the most recent non-corrupt backup copy is the best you can do. If you don’t have backups, you will have to reconstruct the table and its records manually, and start making frequent backups once you have the database in working order again.

By the way, is your database split into a frontend and backend? It reduces the chance of corruption, and if corruption occurs despite that, it’ll probably only affect the frontend or the backend, not both, so repairing the damage is less work. See Why Split a Database? on WendellB’s website.

Reply | Quote

Hans, thanks for the great advice. It is not split, but I should look into it. As for backups… I have been reminded once again of how important it is to do them. (I get lazy on it)

Also, thanks for the quick response. I really didn’t expect anything this quick since it was a weekend!

Speaking of which, I hope yours is wonderful.

Reply | Quote

In addition to the excellent advice you’ve already received, there are a couple of other things you might consider in order to maximize the reliability of your application. The first is that there is a SP2 available from Microsoft for Office XP/2002 that you might want to apply, as it includes some fixes for issues in Access 2002. The second has to do with the database format you are using. In 2002 you can choose to use either the 2000 or 2002 format – we recommend you use the 2000 format rather than the 2002 format as the 2000 format seems a bit less likely to corrupt. That advise applies primarily to front-end type databases that contain forms, reports and code.

Reply | Quote