Compacting an Access database

[Bill]

Hello,

I have an access 2003 database that crashes when I try and compact it. I
open it Access, select Tools->Database Utilities->Compact and Repair
Database. I then get a dialog telling me that access encountered a problem
and asking me to send the details to Microsoft.

There is no indication of what the problem is... Anyone have any ideas?

Thanks,
Bill

 

[John W. Vinson]

Hello,

I have an access 2003 database that crashes when I try and compact it. I
open it Access, select Tools->Database Utilities->Compact and Repair
Database. I then get a dialog telling me that access encountered a problem
and asking me to send the details to Microsoft.

There is no indication of what the problem is... Anyone have any ideas?

Thanks,
Bill

You may well have some corruption. Try creating a brand-new database; use
File... Get External Data... Import to import everything.

You may need to reset workgroup security if you've set it.

 

[Bill]

Thank you.

I created a blank database and tried importing the data as you suggested. I
get an error on one of the tables saying the record is too large. The
suggestion given is to redefine the table structure to remove fields.
However the database has been in use for more than 1 year (compacting
everyday) with this structure so I can't believe that the structure is at
fault. It is more likely that there is data coruption on one or more rows.
How can I determine which row(s) have the the problem?

Bill

 

[John W. Vinson]

Thank you.

I created a blank database and tried importing the data as you suggested. I
get an error on one of the tables saying the record is too large. The
suggestion given is to redefine the table structure to remove fields.
However the database has been in use for more than 1 year (compacting
everyday) with this structure so I can't believe that the structure is at
fault. It is more likely that there is data coruption on one or more rows.
How can I determine which row(s) have the the problem?

aha! That's probably the problem then. The "Record Too Large" error is really
insidious. You can create and use a table with (say) 20 Text fields each
defined as 255 bytes, and it will work fine; but if you at some point add a
record with more than 4000 bytes (2000 characters if you use Unicode), you'll
get this error. It could be that such a record was somehow added, or that some
disk or communication error damamged the record so that it appears to have
more than 4000 bytes.

I can't think of any non-brute force way to find the problem - how big is this
table? Could you perhaps just open it as a datasheet and scroll down looking
for flaky-looking records?