Split Database | Compact and Repair

[mrmmickle1]

I was hoping to link a simple button on a form to a macro that will compact and repair my split database:



I have the Forms and Queries at this location: M:\Science\
Front End Filename:PendingLog_v1.accdb
Backend Here: M:\Science\Backend
Backend Filename: PendingLog_v1.accdc

Is there a simple Macro I can link to my form that will accomplish this? I was looking online and found a few different answers. I wasn't sure if these applied to split databases though.... I was just wondering what the best way to go about this would be?

I appreciate you taking the time to view my inquiry.

Will this work or do I need something more robust?

Application.SetOption "Auto compact", True

[ranman256]

all you need do is click the toolbar, database tools, compact database
thats 2 clicks. You need less?

[nick404]

I do not know any vba for C & R behind a button, perhaps someone else does. However, you can set vba to do it automatically.

 Application.SetOption "Auto Compact", True

This will compact it on close, otherwise you can simply check that box in Office Button > Access Options > Current Database> Compact on Close
I typically employ this to keep a db running smoother in the long run.

You can manually C & R a database either when the db is open or not:
If you want to perform the CR on the db that it open go to upper left corner click the Office Menu button and select "Manage" and click 'Compact and Repair Database'. If the database is not open just run access (no database) and do the same thing, however you will be prompted to browse for the db you want to C & R.

[mrmmickle1]

ranman256,

Thanks for the response. I'll just use the 2 clicks Sorry for the trouble.

nick404,

Thanks for the additional info.

[nick404]

Glad to help!

Since you are using a split db the user's front ends will have to be compacted manually by them or if there isn't too many you could go around and do it for them. In that case I would recommend setting the Compact on Close on for the users so they do not have to worry about db bloat in the future. Front ends do not bloat as quickly as back ends (since they don't house the physical data) but they still can bloat.

For the back end you will have to manually do that. I would do it when no one is using the db since I don't really know if/how that might affect stored data. I would imagine you would lose any data someone might be trying to enter at the time of the C & R.