Share via

Running a batch file using the Shell Command Access 2010 vba

Anonymous
2012-07-29T06:53:58+00:00

Hi.  I have a simple batch file that deletes the current backup copy of the be of the database and then makes a new backup copy of the back end file of the database.  The batch file runs perfectly when run from windows explorer.  The front end, back end and batch file are all currently in the same folder of a mapped network drive T:.

@Echo off

cmd /c echo F | del "MyFile - be BACKUP.accdb"

cmd /c echo F | xcopy "MyFile - be.accdb" "MyFile - be BACKUP.accdb"

In the database i've tried creating a Shell command to run the batch file as follows:

Private Sub CopyBackEnd_Click()

On Error GoTo ERROR

    Shell "COPYBackEnd.bat"

    DoEvents

Exit Sub

ERROR:

    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

End Sub

I've read as much as i could find about this and have also tried these variations (i also had a go at including the path as the UNC still with no luck):

' Call Shell(Environ$("COMSPEC") & " /c COPYBackEnd.bat", vbNormalFocus)

'Shell "T:\MyPath\COPYBackEnd.bat"

I'm not getting any error message when i run any of the code, the form flashes very briefly and appears to have been successful but the file hasn't been copied.

Can anyone please help me - when i run the batch file from the folder it does work correctly.

Many thanks for any light you can shine.

Cheers, Barb

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2012-08-10T00:55:51+00:00

Try...

Shell "cmd /c ""T:\MyPath\COPYBackEnd.bat"", vbHide"

...though I would not want this trying to run while the database is open as I could see it causing corruption.  Might be better to use Windows Scheduler/

Was this answer helpful?

9 people found this answer helpful.
0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-11-13T21:30:33+00:00

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2012-08-10T01:00:26+00:00

    Along the exact same lines as Gina (Shell is your friend) I use a simply reusable function:

    http://www.devhut.net/2010/09/22/vba-run-execute-a-file/

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2012-08-10T13:12:15+00:00

    You're welcome. glad to help!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2012-08-10T05:58:35+00:00

    Thank you so much Gina - that works perfectly!

    Was this answer helpful?

    0 comments