Access 2000 - How to Compact a MDE with a DOS Batch File

[Tony_VBACoder]

I am trying to write a DOS Batch File that will compact/repair an Access 2000
MDE file (I have referred to the following KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209207). The 2 step
process of my batch file is to:
1) Check for the existence of the .LDB file and delete it if it exists
2) Compact the MDE file

I have created a Scheduled Task in Windows 2000 Server that will run this
Batch file each day at 6am.

My batch file code is listed at the end of this post. The problem I am
having is, after the Delete code runs, my compact code does not execute.
However, if I go into my Scheduled Task and change my "Run" command from my
Batch file to the following text "C:\MyFolder\MyApp.mde /compact", the
compact process runs fine.

Can someone tell me what I am doing wrong in my batch file?

---- BATCH FILE CODE BELOW ---------------------------

REM: 1st - check if the file exists before deleting it.
IF EXIST c:\MyFolder\MyApp.ldb del c:\MyFolder\MyApp.ldb

REM: Now run the Compact/Repair on the MDE file
C:\Program Files\Microsoft Office\Office\MSACCESS.EXE C:\MyFolder\MyApp.mde
/compact

 

[Douglas J Steele]

I suspect that nothing actually happens with "C:\MyFolder\MyApp.mde
/compact" as the command, since /compact is a switch for the msaccess.exe
program.

You can't just delete the LDB file. If it exists, that means that, as far as
Access is concerned, someone else is using the database, and you can't
compact the database unless you have exclusive access to it.

 

[salex]

Syntax is wrong !
After "/compact", you must SPECIFY the new database name (temp.mde);
Then, DELETE old database and RENAME new data base (temp.mde).

"Tony_VBACoder" a scris:

 

[Tony_VBACoder]

Douglas/Salex:

A couple of things:

1- Doug - as for deleting that file, in my case, I am able to delete the
file, because I know for a fact no one is in the database - this is a nightly
scheduled job that runs and no one ever uses that database. We run the
scheduled job with command line switches, and for some unknown reason when it
finishes, it leaves the .ldb file there. Every morning I have been going in
manually and deleting the .ldb file and then compacting the database. Which
is why I am trying to come up with a BATCH file to do this automatically.

2- Salex - according the KB article: "If you omit a target file name
following the /compact option, the file is compacted to the original name and
folder". Based on that, I presume you don't have to specify a target, which
is what I am attempting to do in my BATCH file.

3 - Doug - as I stated in my post, if I change my Task Run command to
"C:\MyFolder\MyApp.mde /compact" it works just fine, so this tells me that
the compact method works just fine.

So again...why is it when I change this to "C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE C:\MyFolder\MyApp.mde /compact", it does not work.
Doug, I am using the MSACCESS.EXE program to do the compact.

 

[Douglas J. Steele]

You need to have quotes when you've got a file name with spaces in it.

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
C:\MyFolder\MyApp.mde /compact

Note that you really SHOULD compact to a second file, even if you delete the
original file and rename the new file after your compact. If something
should go wrong during the compact, your copy of the database would likely
be hosed...

I'm very surprised that the compact works without the reference to
msaccess.exe. Are you sure it's actually compacting?

 

[Tony_VBACoder]

Doug thank you...it was the quotes that fixed it.