Cannot compact and repair a database

[Tammy]

Hello,

We are using Vista and Access 2007. The problem is related to a 2003
database. I have a user who could no longer input information into her
database. This was due to the size being over 2GB. She had never done a
compact and repair on the database, so we tried to run it - this is where the
problem started. When the compact and repair first started, we got the
following message:

"Cannot open database ". It may not be a database that your application
recognizes, or the file may be corrupt."

We clicked OK, and received the same message again. We clicked OK, and it
appeared that the compact and repair process started. The status bar said
"Compacting. Press and hold ESC to stop." During the process, we could see
the "hurry up and wait" circle spinning (used to be the hour glass) so we
knew something was being done. After a while, we received the following
message:

"Not enough space on disk." We clicked OK, the database reappeared, but
nothing had been done - actually, the size of the database increased after
this.

I tried this on another computer and the same things happened, except on the
second computer I did not get the "Not enough space on disk." message -
Access appeared to be compacting, and I left it overnight. When I got into
the office Access had stopped responding.

Has anyone run into the same problem? Any suggestions on how I can get this
database to compact and repair? The database can still retrieve information
and we can still view all the objects, just can't enter anymore information
due to the size, which I know will be reduced if we could just run compact
and repair.

Thanks.

 

[Douglas J. Steele]

2 Gb is a hard-and-fast file maximum, so it's possible that you may not be
able to compact it.

Try creating a new database and importing all of the objects from the old
one.

 

[Tammy]

Thanks so much for the suggestion, Doug. I appreciate it!

 

[George Nicholson]

Maybe I know the answer to this but have forgotten: does Access compact
tables while importing? Would the OP be advised to Import all tables &
relationships first, compact, then import all other objects?

 

[Douglas J. Steele]

Shouldn't be required (but nothing wrong with it).

Remember that the point of compacting is to reclaim space that's marked as
unused in the database. When you import a table, all that's going to get
imported is what's recognized as being in the table.

 

[Tammy]

Hi Doug,

I just wanted to follow-up on the results of your suggestion. Thanks so
much, after some effort on Access' part, I was able to copy all the objects
into another database, and run a full compact and repair.

Thanks, again, for your great suggestion!

Tammy