Access 2010 - Compact and Repair RRS feed

  • Question

  • When I Compact and Repair a Access 2010 database the new file (named Database1) is smaller than the original and the original database remains the same size.   I would think the Database1 file would be a backup and the original would be compacted.   Is this the other way around? 

    Thanks, Daryrl

    Wednesday, September 9, 2020 11:28 AM

All replies

  • When you initiate Compact and Repair from inside the database itself, Access creates a new compacted database (usually) named Database1.accdb.

    If all goes well, it then closes the current database, deletes it, renames the new database to the name of the original database and opens it. So it looks like you're in the original database, but in fact it is a new one.

    But if Access for some reason cannot delete the original database because of a permissions conflict, you get stuck with the original uncompacted database and a new compacted database.

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Wednesday, September 9, 2020 11:47 AM
  • Thanks.  Thats actually what i did.

    One particular VBS script is crashing with a error message code that states "the database format is unknown"  (not exact wording).   However, the database is still useable and has been for weeks.  But we can't run the program that sync's 2 locations database togeather.  This has been running for a decade.

    Any suggestions where to start?  My Access and DB skills are a little rusty.

    Thanks again, Darryl 

    Wednesday, September 9, 2020 12:01 PM
  • I hope that someone else can help you with that.

    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Wednesday, September 9, 2020 2:12 PM
  • It would help to know what the script does and what the EXACT error message says.

    I suspect it is that ' the database is in an unrecognised format' which can happen when a C&R fails.

    Try decompiling the database. This will remove any corrupted code. Run a backup first...just in case

    For details on decompiling, see: http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

    If that doesn't solve the issue, try importing all database objects into a new blank database

    • Edited by isladogs52 Wednesday, September 9, 2020 5:18 PM
    Wednesday, September 9, 2020 5:17 PM
  • Hi ACEEng,

    Recent updates might be the cause. Strange things happened.

    Access can create a backup for unknown reason. I can understand when manually doing C&R of a shared folder but not on a FE accDE doing a C&R causing multiple backups.

    In my opinion, I think that the Fe accDe is doing a repair automatically on it's own but was not able to delete the backup(Database1).

    As far as I know, the shared folder have only Read and Write permissions but not Delete. So Access itself would not be able to delete the backup. However, if you do a C&R on the BE manually, you'll not have issues.

    Friday, September 11, 2020 4:37 AM