Executing DOS Commands from Access VBA

[Chaplain Doug]

Access 2003. I know about the Shell command. However, when I try

Shell "Copy C:\Program Files\Grading\Grading_be.mdb C:\Program
Files\Grading\Grading_be_backup.mdb"

I get a message saying "File not found," even though the source file does
exist as specified. However, when I use

Shell "XCopy C:\Program Files\Grading\Grading_be.mdb C:\Program
Files\Grading\Grading_be_backup.mdb"

I do not get an error, but the destination file does not exist in the
specified folder after the operation completes. What am I missing here?
Thanks.

 

[Barry Gilbert]

I don't think you can use Shell to execute any old dos command. It's
typically used on something that can be executed like an exe or bat file. To
copy a file, you could either use the FileSystemObject in VBA (declare a
reference to Microsoft Scripting Runtime) or put your copy command in a batch
file and run the batch file with the Shell function.

Barry

 

[Klatuu]

The problem is that Copy is not a program file. it is command in cmd.

Is there any specific reason you are doing this file copy with the Shell?
There is a VBA Statement cleverly named FileCopy that will do that for your:

Shell "Copy C:\Program Files\Grading\Grading_be.mdb C:\Program
Files\Grading\Grading_be_backup.mdb"

strSourceFile = "C:\Program Files\Grading\Grading_be.mdb"
strDestinatiionFile = "C:\Program Files\Grading\Grading_be_backup.mdb"

FileCopy strSourceFile strDestinationFile

 

[Granny Spitz via AccessMonster.com]

Shell "Copy C:\Program Files\Grading\Grading_be.mdb C:\Program
Files\Grading\Grading_be_backup.mdb"

I get a message saying "File not found," even though the source file does
exist as specified.

If you search for the file C:\Program on your computer you won't find it. It
*really* doesn't exist.

What am I missing here?

Correct syntax. Your current syntax is like this:

executable arg1 arg2 arg3 arg4

but it should be:

executable arg1 arg2

You see, a space separates each argument on the command line after the
executable file name. If you put spaces in your path or file name you've got
a problem because the arguments you want to pass to the executable won't be
passed correctly. They'll be passed as additional arguments that are split
up wherever there's a space. The simplest solution is to never use spaces.
Only use alphanumerics for paths and file names and you'll never have this
problem.

The harder way to fix this is to put quotation marks around bad paths and
file names, but it will probably take you several tries to get it right.
I've given up on ever taking that approach again. I recommend you use the
simplest solution hon. Get rid of those spaces.

 

[Douglas J. Steele]

While I agree with Klatuu that FileCopy would probably make more sense, part
of the problem is that you've got spaces in the file names.

See whether

Shell "XCopy ""C:\Program Files\Grading\Grading_be.mdb"" ""C:\Program
Files\Grading\Grading_be_backup.mdb"""

works any better.

 

[Chaplain Doug]

Thanks for the FileCopy tip. Will try it. God bless.

 

[bob]

my guess is that you are not properly handling spaces. for example,
"c:\program files\.." needs to be in quotes. Run the command at the DOS
level, then use it in shell.

an alternative is to create a batch file and use the shell command to run
it..

Bob

 

[Chaplain Doug]

Thank you all for the help. I am out of the ditch and back on the road now.
God bless.

 

[Tom Lake]

Access 2003. I know about the Shell command. However, when I try

Shell "Copy C:\Program Files\Grading\Grading_be.mdb C:\Program
Files\Grading\Grading_be_backup.mdb"

I get a message saying "File not found," even though the source file does
exist as specified. However, when I use

Shell "XCopy C:\Program Files\Grading\Grading_be.mdb C:\Program
Files\Grading\Grading_be_backup.mdb"

I do not get an error, but the destination file does not exist in the
specified folder after the operation completes. What am I missing here?
Thanks.

SHELL can only execute programs. COPY is a function of the command
interpreter, not an external program. If you write a batch file to do the
copy
SHELL would do it:

@echo off
COPY "%1" "%2"

save it as CPY.BAT then do this:

SHELL "CPY C:\Program Files\Grading\Grading_be.mdb C:\Program
Files\Grading\Grading_be_backup.mdb"

Tom lake