Giving the user the choice to perform a Compact&Reapair of the front-end db at scheduled time

Question

Hi,

I have a split database (front-end and back-end) in Access 2007-2016 that should run in Windows XP and later.

An Inno Setup script (WITHOUT administrator requirement) installs the front-end on the user system and the back-end on the user's computer or another on line resource.

I would like to give the user four different ways to perform a Compact And Repair.

1.  A command button on the front-end ribbon to CompactAndReapair the back-end database (easy, I thiink).
2. A choice in my options page, to perform the CompactAndReapair of the back-end database automatically at certain interval of time: a) close, b) every day, c) every week, d) every month, e) never  (not so difficult, I think, because I can always operate from inside the front end).
3. A service application to perform a CompactAndReapair of the front-end database at wish.
4. A choice in my options page, to perform the CompactAndReapair of the front-end database automatically at certain interval of time: a) close, b) every day, c) every week, d) every month, e) never (much more difficult).
   

Are the last two ways that are giving me some troubles.

I was thinking to:

• Build a small access database with the only purpose to check if my front-end db is open, if yes close it, operate a CompactAndReapair on it, if was opened reopen it.
• Allow the user to click on my tool access database at wish.
• Put in my Inno Setup script a run command to execute a Schtasks.exe to schedule (a default time) launch of my tool access database.
• Modifying my scheduled task with Schtasks every time the user change the option.

But Schtasks.exe can be executed only with administrator status, and I want that a normal user could change the frequency of the operation.
And besidethat,  it seems to me a little awkward solution.

Do you please have any suggestions to help me.

Thanks, Lauro

 

Answer 1

Hi Lauro,

You may be overthinking this. The front end doesn't really require a C&R unless you're using a lot of temp table operations. If so, you have two options to "C&R" the FE (Note: I put C&R in quotes because none of these solutions is actually going to do a C&R).

1. Use a side temp db for the temp data and avoid the bloat on the FE; hence, no need to C&R, or

2. Just replace the FE with a "fresh" copy of the master FE, which you can store somewhere on the user's computer.

Some developers prefer the second approach where each time the FE starts, it downloads a fresh copy of the FE. This also ensures the user is using the latest copy of the FE

Just my 2 cents...

Answer 2

Thank you very much for your very quick answer.

My db is very, very simple. It doesn't use any temporary table.

Maybe I should concentrate myself only on C&R of the BE db...

I could give the oppurtunity to replace the FE file with a new one every, say, month....

P.S.

Before doing a C&R of the BE, should I make a backup copy for security reasons?

Answer 3

Hi Lauro,

Developers who prefer to download a fresh copy of the FE swears the wait time is unnoticeable to the user and it automatically ensures the user is using the latest copy of the FE all the time. I will leave the choice up to you on when to refresh the FE. I personally wait until there is an update before downloading a fresh copy.

Backing up the BE prior to any major operation is always a good idea. Please keep in mind doing a C&R over the network is susceptible to corruption. Best practice is to perform the C&R on local machine.

In case you're interested, I do have a backup and C&R routine on my website.

Just my 2 cents...