Help with my batch file VBA Code

[Waddy]

Hello I have some code that I kindly found online that creates a batch file which closes the current file, deletes the current file and downloads another one from a location and reopens. All works great, however it leaves the batch file on the place were you run this code, my case the desktop after it is done.

Is there a way of changing this code below to delete the batch file when complete or have it placed elsewhere than the place it is opened. I hope it makes sense

I was told if I move the placing of the code it would not create the batch file, if so were would I move it.

Thank you as always for your help.

Below is the code:

Option Compare Database
Public Function CheckFrontEnd() As Integer
' ************************************************************
' Created by : Scott L Prince
' Parameters : None
' Result : Determines if backend can be reached, and if front end is the current version.
' Returns : 0 - Misc Error
' : 1 - No current version found in Version Manager file
' : 2 - Front end being run from master location
' : 3 - Master file path not found in Version Manager file
' : 999 - Front end current
' Date : 5-30-14
' Remarks : Based on previously-existing code by Bob Larson posted at StackOverflow
' Changes :
' ************************************************************
Dim FrontEndVersion As String 'Front end version number
Dim MasterVersion As String 'Master version number
Dim MasterPath As String 'Location of the master FE file
Dim BatchPath As String 'Location of the batch file that does the actual update

'Determine master version number.
MasterVersion = DLookup("fe_version_number", "tbl-version_fe_master")

'Determine if the database containing the version information can be accessed.
Select Case MasterVersion
Case "" 'Master version number cannot be found, or backend/version manager is missing.

CheckFrontEnd = 1

Case Else 'Version data found.

'Look up the path for the master file location.
MasterPath = DLookup("s_masterlocation", "tbl-version_master_location")

'Determine if the master file is being run rather than a local copy.
If Nz(MasterPath, "") = "" Then

'No master path was found. Return error value.
CheckFrontEnd = 3

ElseIf MasterPath = CurrentProject.Path Then

'The actual master file is the one being executed.
CheckFrontEnd = 2

Else

'Master file path found and is not being run. Determine the version number of the active front end.
FrontEndVersion = DLookup("fe_version_number", "tbl-fe_version")

'Compare the version number in the front end to the master version number.
Select Case (FrontEndVersion = MasterVersion)

Case True 'Version numbers match.

'Return "OKAY" result.
CheckFrontEnd = 999

Case False 'Version numbers do not match.

'Create the path for the batch file used to update the front end.
BatchPath = CurrentProject.Path & "\UpdateDbFE.cmd"

'Check for an already-existing BatchPath, and kill it if it exists.
If Dir(BatchPath) <> "" Then Kill BatchPath

'Notify the user that the application will update.
MsgBox "UPDATE REQUIRED" & vbCrLf & vbCrLf & _
"Your Athena is not the latest version." & vbCrLf & vbCrLf & _
"This needs to be updated. The program will now close and then will reopen automatically with the new version, this make take up to a 2 minites so please be patient.", _
vbCritical

'Execute 'UpdateFrontEnd'.
UpdateFrontEnd CurrentProject.Path & "" & CurrentProject.Name, MasterPath

End Select
End If
End Select

End Function

Private Sub UpdateFrontEnd(ByVal LocalFilePath As String, _
ByVal MasterFileFolder As String)

Dim BatchFile As String
Dim MasterFilePath As String
Dim Restart As String

'Set the file name and location for the file to copy
MasterFilePath = MasterFileFolder & "" & CurrentProject.Name
'Set the file name of the batch file to create
BatchFile = CurrentProject.Path & "\UpdateDbFE.cmd"
'Set the restart file name
Restart = """" & LocalFilePath & """"

'Create the batch file
Open BatchFile For Output As #1
Print #1, "@Echo Off"
Print #1, "ECHO Deleting old file..."
Print #1, ""
Print #1, "ping 127.0.0.1 -n 5 -w 1000 > nul"
Print #1, ""
Print #1, "Del """ & LocalFilePath & """"
Print #1, ""
Print #1, "ECHO Copying new file..."
Print #1, "Copy /Y """ & MasterFilePath & """ """ & LocalFilePath & """"
Print #1, ""
Print #1, "ECHO Starting Microsoft Access..."
Print #1, "START /I " & """MSAccess.exe"" " & Restart
Close #1

'Run the batch file
Shell BatchFile

'Close the current application so batch file can execute.
DoCmd.Quit
End Sub

 

[theDBguy]

Hi. All you can do is delete the batch file when the app reopens. For example:

If Dir(CurrentProject.Path & "\UpdatDbFE.cmd")<>"" Then
    Kill CurrentProject.Path & "\UpdateDbFE.cmd"
End If

 

[June7]

Please post code within CODE tags to retain indentation and readability.

No, this code cannot delete the batch file because the application closes so the batch file can execute.

Would need code in some event that runs when application opens that will delete the batch file. Could be an AutoExec macro that calls a VBA function or code behind a form that opens by default.

 

[gemma-the-husky]

given a file name you have 3 basic commands.
You ought to be able to do what you want with these, I should think

filecopy oldfile as newfile 'copy a file
name oldfile as newfile 'rename ie move a file (may copy if used to a different drive letter - not sure off hand)
kill filename

 

[Waddy]

Thanks for this, I have put this code in the access form that loads up, it did come back with no errors but the file remains.

What could I be doing wrong?

Hi. All you can do is delete the batch file when the app reopens. For example:

If Dir(CurrentProject.Path & "\UpdatDbFE.cmd")<>"" Then
    Kill CurrentProject.Path & "\UpdateDbFE.cmd"
End If

 

[gemma-the-husky]

The first line says updat.
The delete says update

 

[Waddy]

Thank you eagle eye, how did I not see this

It Worked!!!!!

Thank you all

 

[gemma-the-husky]

we often do it this way, to avoid such typos.

dim target as string
target = CurrentProject.Path & "\UpdateDbFE.cmd"

If Dir(target)<>"" Then
    Kill target
End If

you can also make the command more informative

If Dir(target)<>"" Then
    Kill target
    msgbox target & " deleted"
else
    msgbox target & " was not found"
End If

or if you don't care - as the file MAY not actually exist.

onerror resume next
Kill target

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Sorry for the typo. I guess I was hal awake when I wrote it. Good luck with your project.

 

[deletedT]

Sorry for the typo. I guess I was hal awake when I wrote it.

Do you mean half awake?

 

[mjdemaris]

Forgive the late post on an older thread:
I have been using this code from Scott for a while, and am working on a new project, using the same code, but slightly modified. Only now, the batch file does not delete the local FE file. I believe I have a thread with this code posted somewhere...I'll hunt around for it and paste the link here, or if I can't find it, I'll attach or paste the code.

Either way, I'm looking for ideas that would prevent the batch file from deleting the local file. Everything else in the batch file seems to run fine.

 

[mjdemaris]

Ah, here is the thread
https://www.access-programmers.co.u...ion-of-front-end-updater.305926/#post-1631178

It turns out, the delete command wasn't working back then either, lol. Well, it must have gotten pushed to the very back burner. It's on the front burner now!

Turns out the front burner was getting pretty hot, and @theDBguy 's comment on the above mentioned thread proved correct, I simply added the "ping" command back into the batch file, et voila! Problem solved, at least in the first test. (I don't recall why I removed it way back when...)