Module doesn't seem to be creating/running batch file

[breezett93]

I am implementing the front end auto-update solution by Frothingslosh that is from a thread on here I can't link to.

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 ""
 
            CheckFrontEnd = 1
 
        Case Else
 
              '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
 
                          'Return "OKAY" result.
                        CheckFrontEnd = 999
 
                    Case False
 
                       '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 program is not the latest version." & vbCrLf & vbCrLf & _
                        "The front-end needs to be updated. The program will now close and then should reopen automatically.", _
                        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

I am unable to add breakpoints. I guess that's not allowed in a module?? So, it's difficult to see what's really happening.

The first function works well. I get the alert that a new update is needed. The program closes to begin the update.

In the second function, the batch file does get created. However, the old version does not get deleted. The icon just moves to a different spot on the screen (alternating between one space down and one space up). Also, none of the three echo prompts in the batch file show up on screen.

 

[Gasman]

You can use breakpoints in a module?

 

[Ranman256]

yes you can add breakpoints in any code.
select a word, press F9, or click the LEFT BAR on the line.
(you cant put break on DIMs or labels.)

You may want to add Error trap to see where things go wrong:

...
on error goto ErrTrap

      'Set the file name and location for the file to copy
    MasterFilePath = MasterFileFolder & "\" & CurrentProject.Name
exit function

ErrTrap:
msgbox err,,err.descrition
exit function

 

[breezett93]

yes you can add breakpoints in any code.
select a word, press F9, or click the LEFT BAR on the line.
(you cant put break on DIMs or labels.)

You may want to add Error trap to see where things go wrong:

...
on error goto ErrTrap

      'Set the file name and location for the file to copy
    MasterFilePath = MasterFileFolder & "\" & CurrentProject.Name
exit function

ErrTrap:
msgbox err,,err.descrition
exit function

I added the error trap, and no errors were triggered. So, I guess the batch file itself is not starting?

 

[Isaac]

Nothing against Bob Larson, as I'm sure he was brilliant and I'll admit I used that exact same code for years, until I started to question half the stuff that is in there (the batch / cmd file, I mean), and started writing my own VBScripts instead. VBScript is much more like "plain English" than cmd line stuff, IMHO, if you're coming to both of them as a beginner.

What I'm saying is maybe all that stuff about pinging a local gateway, and 1000, and nul, and assuming "msaccess.exe" will work for all office installs - maybe it just doesn't work for everyone. I think it didn't work for me in some environment or another.

In my opinion you'd be better off changing the UpdateFrontEnd sub to simply Create and Shell out:
1) wait 5-8 seconds
2) delete the old db
3) copy the new db
4) reopen Access by using Shell with the path of the database, don't worry about specifying the access exe.

5) after Shell to that vbscript, exit access.

Another option is to just close Access and check out your batch file that it created. Double click it, what happens? If it just runs and disappears, I (personally) would abandon it in favor of writing your own script .. unless you are skilled in troubleshooting cmd files..

 

[breezett93]

Nothing against Bob Larson, as I'm sure he was brilliant and I'll admit I used that exact same code for years, until I started to question half the stuff that is in there (the batch / cmd file, I mean), and started writing my own VBScripts instead. VBScript is much more like "plain English" than cmd line stuff, IMHO, if you're coming to both of them as a beginner.

What I'm saying is maybe all that stuff about pinging a local gateway, and 1000, and nul, and assuming "msaccess.exe" will work for all office installs - maybe it just doesn't work for everyone. I think it didn't work for me in some environment or another.

In my opinion you'd be better off changing the UpdateFrontEnd sub to simply Create and Shell out:
1) wait 5-8 seconds
2) delete the old db
3) copy the new db
4) reopen Access by using Shell with the path of the database, don't worry about specifying the access exe.

5) after Shell to that vbscript, exit access.

Another option is to just close Access and check out your batch file that it created. Double click it, what happens? If it just runs and disappears, I (personally) would abandon it in favor of writing your own script .. unless you are skilled in troubleshooting cmd files..

Manually starting the batch file after it's created works. Old version is deleted and replaced by the current version. Then the current version opens.
Is there a different way to call the batch file after creating it?

 

[Isaac]

Manually starting the batch file after it's created works. Old version is deleted and replaced by the current version. Then the current version opens.
Is there a different way to call the batch file after creating it?

Leaving the batch file exactly where it was originally created (presumably as: CurrentProject.path & "\UpdateDbFE.cmd"), and using an Access FE file in that same folder, try just running this code, see if it even appears to work:

Sub Test
Dim BatchFile As String
BatchFile = CurrentProject.Path & "\UpdateDbFE.cmd"
Shell BatchFile
End Sub

 

[breezett93]

Sorry for the delay, but it randomly started working. I've tested it over and over just in case; still working. So, I'm going to tentatively wrap up this thread. Thank you all for your help.