Button to Compact & Repair Database (1 Viewer)

tcneo

Member
Local time
Tomorrow, 01:47
Joined
Dec 7, 2021
Messages
68
As i develop my database in Access, i notice the file size can double and then halve after I click on the Compact and Repair Database option.

But if i release my database as a standalone to my users, how can they compact & repair? Is there a need? Is there a way to include a command button with some macro or function that calls the Compact and Repair function for my users to use? Good idea or bad?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:47
Joined
May 7, 2009
Messages
19,169
there is an Option for Current database to Compact On Close.
 

AngelSpeaks

Active member
Local time
Today, 12:47
Joined
Oct 21, 2021
Messages
406
DBGuy has great blogs. The compact and close can be set using VBA.

 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 17:47
Joined
Sep 12, 2006
Messages
15,613
Personally, I wouldn't compact on close. I would want to take a back up of the database, and then do a compact manually.
 

isladogs

MVP / VIP
Local time
Today, 17:47
Joined
Jan 14, 2017
Messages
18,186
I would also advise against compact on close as it can at times be the cause of corruption..

Older versions of Access allowed compacting to be done using VBA but that functionality was removed in A2010.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 28, 2001
Messages
26,999
I'm a late walk-on to this one, but I would never automate a self-initiated C&R. You should ALWAYS make a copy before you do anything else, THEN do the C&R, then (perhaps) make a copy of the compacted DB to be the REAL backup. Then discard the first copy before you restore the DB with the C&R'd copy. However,...

But if i release my database as a standalone to my users,

I hope that is simply a turn of phrase, because we don't know what your users actually do. BUT if this is a shared DB, I would sincerely hope that this is a split DB with a distributed front-end and a shared back-end. If you are actually sharing a monolithic DB, you are inviting corruption due to file locking issues on your forms that are very nearly inevitable in a non-split DB.
 

GPGeorge

Grover Park George
Local time
Today, 10:47
Joined
Nov 25, 2004
Messages
1,776
There are alternatives to creating bloat in an accdb. I'm also going to take the optimistic route and assume you do, indeed, plan to deploy a properly split relational database application by placing a shared back end, with only the tables, in a network location, and giving each user their very own copy of the front end, with all of the forms, reports, queries and VBA. However, if that's not already your plan, I urge you to stop now, read up on properly split relational database applications in Access, and proceed appropriately.

Bloat is usually the result of doing a lot of work inside the accdb which involves things like appending data in bulk using insert queries or deleting records using delete queries and importing external data and so on. Access does not initiate deletion of objects and records used in temporary operations. They're marked internal as "deleted" but physically remain, taking up space, i.e. "bloat". Removing that bloat is the "Compact" part of "Compact & Repair".

In addition to the advice already given, you may want to look into creating a temporary "side end" accdb in which all of that data mongering goes on externally to the production front end accdb. That allows the temporary accdb to house the temporary tables and records. And that means you simply delete the auxiliary, or "side end" accdb when you are done with it. And that reduces bloat in the production accdbs.
 

AngelSpeaks

Active member
Local time
Today, 12:47
Joined
Oct 21, 2021
Messages
406
George, I have some questions about the side end. If the temp tables and/or temp database is gone how do you handle the FE not able to link to the table and queries and reports referencing columns that don't exist? Also when deploying the FE, how do you handle relinking to these tables? Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:47
Joined
Oct 29, 2018
Messages
21,358
Just one more link, in case it helps.

 

GPGeorge

Grover Park George
Local time
Today, 10:47
Joined
Nov 25, 2004
Messages
1,776
George, I have some questions about the side end. If the temp tables and/or temp database is gone how do you handle the FE not able to link to the table and queries and reports referencing columns that don't exist? Also when deploying the FE, how do you handle relinking to these tables? Thanks!
VBA creates the temp accdb, populates it with the necessary tables and then links to it each time the production FE starts. This ensures that the linked tables are always available.

I have an older demo on my website of an application which uses this method, although it wasn't the main focus of the demo itself.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:47
Joined
Feb 28, 2001
Messages
26,999
One of the simpler ways to do a "side end" DB is to make a master copy of that side end with all tables laid out, keys defined, etc. but no data. Keep it where you keep the master copies of the DB.

Use a script to launch your DB so that you can do automatic updates of the FE from the master area to your working area.
As part of the script, check for the existence of the working side end in the working area and, if present, delete it. Then copy the side end file to the working area. THEN (and only then) launch the front end. Linked tables link via fully qualified file names so if you just replace the side end with a new one BEFORE you open the FE, it will link with the new (empty) side end table.
 

tcneo

Member
Local time
Tomorrow, 01:47
Joined
Dec 7, 2021
Messages
68
There are alternatives to creating bloat in an accdb. I'm also going to take the optimistic route and assume you do, indeed, plan to deploy a properly split relational database application by placing a shared back end, with only the tables, in a network location, and giving each user their very own copy of the front end, with all of the forms, reports, queries and VBA. However, if that's not already your plan, I urge you to stop now, read up on properly split relational database applications in Access, and proceed appropriately.

Bloat is usually the result of doing a lot of work inside the accdb which involves things like appending data in bulk using insert queries or deleting records using delete queries and importing external data and so on. Access does not initiate deletion of objects and records used in temporary operations. They're marked internal as "deleted" but physically remain, taking up space, i.e. "bloat". Removing that bloat is the "Compact" part of "Compact & Repair".

In addition to the advice already given, you may want to look into creating a temporary "side end" accdb in which all of that data mongering goes on externally to the production front end accdb. That allows the temporary accdb to house the temporary tables and records. And that means you simply delete the auxiliary, or "side end" accdb when you are done with it. And that reduces bloat in the production accdbs.
yes, i think my file "bloats" after some time as it uses insert and delete queries to create, add and delete records.

as for splitting to front-end and back-end, i have seen videos on how to do it. but i have reservations as the shared drive (microsoft one drive) in my place is rather unstable. even saving a word or powerpoint file on the shared drive can result in save error and a resulting duplicate.

as such, my plan for now, is for my user to use the "monolithic" access file on their laptop.

i have not experienced a file-locking issue on forms of a non-split DB. i don't know what that is.
 
Last edited:

GPGeorge

Grover Park George
Local time
Today, 10:47
Joined
Nov 25, 2004
Messages
1,776
yes, i think my file "bloats" after some time as it uses insert and delete queries to create, add and delete records.

as for splitting to front-end and back-end, i have seen videos on how to do it. but i have reservations as the shared drive (microsoft one drive) in my place is rather unstable. even saving a word or powerpoint file on the shared drive can result in save error and a resulting duplicate.

as such, my plan for now, is for my user to use the "monolithic" access file on their laptop.

i have not experienced a file-locking issue on forms of a non-split DB. i don't know what that is.
Yikes, OneDrive? That is not a valid location for sharing Access. You know that from problems with Word and PowerPoint already, but it's even more of a problem for Access. Each user creates a local copy of the Access accdb file whenever they open it from OneDrive. That means when they go to save and close that accdb, it saves that altered accdb back over the top of the one on OneDrive. Either that overwrites other people's changes, or it corrupts the accdb for everyone. Bad juju in any event.

If the only shared location you have is OneDrive, Access by itself is a non-starter for any relational database application which is aimed at more than one user.

It is extremely difficult to manage shared data for more than one user unless you have a properly split accdb, with the data (i.e. accdb with tables only) stored in a network folder to which everyone has read/write/delete permissions. In fact, a long-deprecated feature called Replication was used in mdbs to get around that, but it was dropped years ago.

There are alternatives, though. One is to use SharePoint lists as the back end for your Access interface. This solution is suitable for relatively small and relatively simple relational database applications, but does support sharing for multiple users. Another is a server based database, such as SQL Server Express, which can also replace the shared accdb BE.

as such, my plan for now, is for my user to use the "monolithic" access file on their laptop.
If you have one user, and only one, then that would work, although it complicates your life trying to maintain and add enhancements.
I would urge you to split the accdb anyway, even if both reside on one laptop to be used by one and only one user. That way, when you add enhancements or fix bugs in the accdb FE you only replace it, without borking the data in the accdb BE.
 

GPGeorge

Grover Park George
Local time
Today, 10:47
Joined
Nov 25, 2004
Messages
1,776
it is Now possible, using MountainDuck.
So, are you part of the group that is successfully conducting a trial of that product? What progress and results have been determined?

MountainDuck is an additional piece of software that you must license in order to use it. If you must use OneDrive, then $39 for a single user might be viable. Discounts are available for volume licenses, so it might well be practical.
 

Users who are viewing this thread

Top Bottom