Compact Repair BE

[geno]

Hi,

I've read quite a few posts on compact and repair but need some clarification.
I have a split database; how often should you compact the FE? How often should you compact the BE? I would like to run a batch file through task manager to trigger the back end compact late at night so no one can open it.
The back end has a security password set; when I do a command line compact repair in task manager it starts to work then stops at the password input box. How can I add this password to a batch file so it can complete the compact and repair. I see through the posts that you should back up the DB first. Can someone help me with the batch file to include a back up then compact the DB?

Thanks

 

[Ari]

Hi

Show your code the batch file. What version of Access used? Depending on the version you can enable the compact and repair the "Access Options". Just then open and close the file. As this data is always good to include in routine security backup before you run compact and repair.

Through the "OpenDatabase" you get past the password.

In your code, I believe that the use of SendKeys revolve. example:

Dim objws As Object
Set objws = CreateObject("wscript.shell")
objws.SendKeys Password, True
objws.SendKeys "{ENTER}"
booResult = Application.CompactRepair(source,Target, True)

 

[geno]

Hi,

I'm not familiar with putting together batch files; I need help with the coding.
This is the command I'm testing with task scheduler:

"C:\Program Files (x86)\Microsoft Office\OFFICE11\MSACCESS.EXE"

with the arguments:

C:\Users\Geno\Desktop\2003\NewWebDb.mdb /compact

When I run a test I get stopped at the password dialog box. I thought it may be easier to use a batch file.

Can you help?

Thanks

 

[Ari]

Hi,

You can use a MDB file in the task scheduler. Download the sample file to test.

Open the module mod_backup and change the password in the following line:

objws.SendKeys "a1234", True 'password

WARNING: Make a backup before testing your database

Good study!

 

[geno]

I had to change the Call Sleep(300) to 3000. Works great.
Thanks for you help.

 

[Galaxiom]

how often should you compact the FE?

A well structured front end never needs compacting. Simply avoid the frequently implimented but demonstrably undesirable practice of writing temporary records to tables in the front end.

Temporary data should be written to a disposable local database neither in the front end nor back end. I like to call it the Side End.