Locked lesson.

Upgrade

Quick reference

Compact and Repair

Use Compact and Repair to repair and fix database issues. 

When to use

We use Compact and Repair to remove unwanted code, wasted space, orphaned objects, and other items that are taking up space and making the database larger than it should be. 

Instructions

Compact and Repair

Over time, a database can become bloated and broken. When we are working in tables and deleting records, the space isn't always automatically reclaimed by Access. This can lead to lots of unnecessary pieces of code hanging around in the .accdb file. This can cause the database to grow larger and sometimes break. 

It's important we are aware of the tool available in Access to help with this. 

We can use Compact and Repair to fix our database. 

Access will normally prompt us to fix errors. However, that doesn't always happen. So, it's a good idea to set up a repair schedule to catch issues early. It's good to set this up as part of a regular maintenance schedule.

To compact and repair the database we must open it for exclusive use.  

  • From the File tab, click Open. 
  • Browse to the folder where the database is located. 
  • Select the database file and click the drop-down next to Open. 
  • Select Open Exclusive. 
  • From the File tab, select the Info page and click Compact and Repair. 

Compact on Close

We can set Access to run a compact every time we close the application. 

  • Click the File tab and select Options. 
  • From the Current Database page, click Compact on Close.
Login to download
  • 00:04 Over time your Access database can become what we refer to as bloated or
  • 00:10 broken in some way.
  • 00:11 Now more often than not, Access can repair itself, but
  • 00:15 sometimes that's not always the case.
  • 00:19 So what causes bloat in an Access database?
  • 00:23 Well, when we're working in tables and maybe doing things like deleting records,
  • 00:28 the space isn't always automatically reclaimed by Access.
  • 00:32 There are also lots of pieces of code and various other things that Access uses when
  • 00:37 it's working on the database that doesn't get deleted and
  • 00:40 just ends up hanging around in the ACT db file.
  • 00:43 And all of these things cause your database to get bigger and bigger.
  • 00:48 And if you're working on a very large database as it is,
  • 00:51 your database can become unnecessarily bloated and sometimes it can break.
  • 00:56 So it's always good to know some options that we have when it comes to not only
  • 01:01 trying to prevent these bloating issues but
  • 01:04 also repairing the database if the worst happens.
  • 01:07 For this, we're going to use the Compact & Repair option.
  • 01:11 And we find that underneath File in the Info tab, it's this one just here.
  • 01:17 So there are really two parts to this button, the first part is the compact part
  • 01:22 which helps us remove unwanted space, and the second part is repairing the database.
  • 01:29 Now your database can become corrupt at any point in time.
  • 01:32 And it might be that as soon as a corruption occurs,
  • 01:35 Access will recognize it and prompt you to fix it.
  • 01:39 But that isn't always the case, and I've definitely had it where I've had
  • 01:43 a corruption in my database I haven't realized because I haven't received any
  • 01:47 kind of prompt and there have been no obvious issues when I've been working in
  • 01:51 the database, and then eventually it's just all crashed and broken.
  • 01:55 So it's a good idea to set up a repair schedule to ensure
  • 01:59 that you're catching everything that needs to be fixed along the way.
  • 02:03 And I would generally set up this as part of a general maintenance schedule.
  • 02:08 Now once again, when you're doing a compact and
  • 02:11 repair, you have to make sure that you have the database open for exclusive use.
  • 02:15 So, I'm going to close down this file and
  • 02:19 we're going to reopen in the same way as we did previously.
  • 02:25 Select the file, click the drop down, open exclusive.
  • 02:30 Now we can go into File and Info, and I'm going to choose Compact & Repair Database.
  • 02:36 And it really is as simple as that.
  • 02:39 Now, as I mentioned, you might want to do this weekly or bi weekly or
  • 02:43 maybe at the end of every day.
  • 02:45 But what you could also do is choose a setting in Access options that
  • 02:50 will compact the database every time you close.
  • 02:54 So if we jump up to File and go down to Options, in this first group here,
  • 02:59 the Application Options, we have a Compact on Close option.
  • 03:04 So I recommend that you turn this on so
  • 03:06 that every time you close your database down it's going to run that compact.
  • 03:10 And that should help you with regards to any issues going forward.

Lesson notes are only available for subscribers.

Gift this course