Execute a BAT file in VBA/Macro

[Malcolmneill]

I am probably being dumb, but I can't execute a BAT file from within Access. The file will execute manually from any location correctly, I just can't make it work in either a macro or within VBA. I probably don't understand the way to do it. My code is simple, perhaps too simple:

Function Save_DB()
Dim RetVal As Long
MsgBox "saving DB", vbOKOnly

On Error Resume Next
RetVal = Shell("D:\Access DB\OCS Backup.bat", 1)

MsgBox "saved DB", vbOKOnly
End Function

The objective here is to simply copy the linked database file to a backup directory automatically as the user exits the application. The messages display so I know it's finding the function but the DOS command box never opens, there's a pause to stop it closing. Can someone advise me please.

 

[Minty]

What operating system are you on? Windows 10 is very "difficult" about calling certain things, batch files, particularity if they are stored in a root level directory, being one of them...

 

[Cronk]

I'd say the preferred method is scripting rather that a batch file. However try

RetVal = Shell("[COLOR="Red"]cmd.exe[/COLOR] D:\Access DB\OCS Backup.bat", 1)

 

[Malcolmneill]

Hi Minty and Cronk,

Thanks for the response. I've taken the opportunity to send one reply.

So Minty, are you referring to a VBA or SQL script? I am not sure how I could do that.

Cronk, your suggestion works to the extent that it opens the Command window put doesn't run the BAT file, it just opens the d:\documents folder and waits for input. I tried adding additional parameters and "" marks but didn't work.

What I am trying to do is to autocopy the database as the operator signs out of the application. The DB is external to the App. The reason is that a recent situation caused the work done by the operator to be "lost" due to the DB being accidentally overwritten before a scheduled backup was executed. My thought was If I could execute a DB Copy as part of the final "Exit" macro/VB module then this situation could not recur.

So if you have any suggestions as to how I can achieve this. either macros or VBA or SQL I would really appreciate it. As I said I am probably not familiar enough with all the options.

Regards
Malcolm