Hi Richard.

Your end-users (i.e., the ones using the runtime version of Access) should not be doing Compact & Repair. This should be something that you, as a developer, do on your full version of Access when and if needed.

I will not give any of my end-users the ability to come back and repair.

Sami is correct.

So you're saying that an Access database isn't reliable or stable enough that an end user can't press a button?? I mean, that's all I do, is press the button, and nothing else. Or am I missing something about this?

Because, my end users won't be my clients. They'll be customers. And they won't even be my customers, as some platform will be selling it, not me (hopefully). I'm not trying to be a custodian to a bunch of end users.

Now, I'm fine with removing from the ribbon the Compact & Repair option if that's what I got to do. I, personally, like having it there as whenever I switch it over from accdb to accde mode, like Rich says in his video, I only have to hit "data tools" and compact & repair to reset the database, and not exit and come back in.

I've hit that button probably a million times in the development of this database and I haven't seen anything about it that requires any expertise on my end to make sure it does what its suppose to do. Seriously, if it don't need to be there then I don't want to put it there.

Leaning on you guys to tell me what to do. But seriously, so far it's just pressing a button and for the most part, nothing happens. How bad can it be for an end user to do that? Thanks.

Your end-users are going to have a front end with all their forms, reports, and VBA code in it. That's going to be an ACCDE file, and they're probably going to be running the runtime with that. The back-end database you can leave as an ACCDB file because even if you do encrypt it or create an ACCDE out of it, they can still get in and look at the tables, the data, and the structure.

So my suggestion in this case is to leave the back-end table as an ACCDB file. That's the only one you have to worry about compacting and repairing anyway to get rid of all that empty space. You don't need to compact your front end on a regular basis unless you've got temporary tables in it, which I don't recommend you do if this is your issue.

Now here's the thing: you can compact and repair using the runtime. But only from VBA. You can't get into the GUI version of it inside of Access. And I've got a video that will teach you how to do that. So just designate one person in that office as the administrator and tell them: "Hey, once a week, go in, compact and repair the database using this button. If it's going to be running 24/7, just set up a timer to have it fire off whenever Sunday mornings at 3:00 a.m."

See: Compact VBA

I guess I threw us all into a confusion with my OP because the video, Compact VBA, is perfect for what I'm trying to do. My db is just meant to be a simple retail package like a home inventory thing. That Compactor Template you mentioned in the video looks pretty interesting to me too. And thanks for saying the front-end didn't need compacting, I wasn't sure about that. Thanks much for those videos, they are lifesavers!!

Yeah, and even if you're in a situation where you want to release an update, you would just compact and repair that update (which is your front-end) before deploying it, so everyone would get a freshly compacted front-end anyway. But generally, the back-end is where you keep the tables, and that's the only thing that needs to be compacted.

This is going to be in QQ39.

What is a QQ39?

Quick Queries 39 - click on the link.

Oh okay! I know what that is, thanks! :-)