Compacting removes indices???

[Les Isaacs]

Hi All

We have a split access2010 application in which the BE is 1.6Gb compacted. It reached 1.8Gb recently, then I compacted it and it returned to 1.6Gb-ish, but some of the FE functionality then virtually ceased to run. Some (very stressful:banghead hours later I discovered that some of the table indices were missing, and when I re-added them the FE functionality returned to normal.

Is it a known thing that compacting can have that effect? Obviously I can see the logic of removing indices to free up space, but with such potentially significant performance consequences I'm amazed that compacting doesn't include an explicit warning about the possibility.

Or is it just my ignorance - does everyone else know about this?

Grateful for any thoughts.
Les
PS
Before anyone asks - yes we are planning to upsize the BE to sql express!!

 

[gemma-the-husky]

compacting shouldn't remove indices.

the only thing I can thing is that the compact partially failed because of lack of space, say, and did not replace the indices.

compacting actually works by generating a new dbs, and then deleting/renaming the old one to the new one, if all goes well.

Do you still have a copy of the old dbs, before compact, with the indexes intact?

 

[Les Isaacs]

Hi Dave
Thanks for the speedy reply!
Yes - I do have a copy of the pre-compacted accdb with the indices (or is it indexes ) intact.
I reckon you're right about the compact having partially failed: and I reckon Gates should be warning the user to check indexes after a compact has partially failed
Cheers
Les