Batch file to upgrade version of an MS database prior to opening it (Microsoft Access 2016 and Micro-CodePudding

I have a front end called PMD_FE.accdb with a table called tbl_Version. It only contains a unique field called "VERSION." PMD_FE.accdb, lives locally on everyone's machine at C:\users\public\PMDTools\PMD_FE.accdb IN PMD_FE.accdb, there's also a link to the same table TBL_VERSION, but this is the master copy which exists on the server. The name of that linked table is tbl_Version_Latest. I'm trying to write a batch script that will check tbl_Version_Latest and compare it with tbl_Version, if they match, the batch script simply launches PMD_FE.accdb from the Local Home: C:\Users\Public\PMDTools\PMD_FE.accdb. If tbl_Version_Latest is different, it downloads the file from the server which is S:\PMDTools\04_FrontEnd\PMD\PMD_FE.accdb, to C:\Users\Public\PMDTools\ prior to launching it. I have 3 public subroutines in PMD_FE.accdb:

Public Sub DownloadLatestVersion()
 dim SharedAppHome as string
 SharedAppHome = "S:\PMDTools\04_frontEnd\PMD\"

    Shell "xcopy " & SharedAppHome & " " & CurrentProject.Path & "\PMD_FE.accdb /Y"
    
    Debug.Print Shell("echo %errorlevel%", vbNormalFocus)

End Sub

Public Sub GetLocalVersionNumber()
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tbl_Version")

    rs.MoveLast
    wsh.PopUp rs![VERSION]

    Set rs = Nothing
    Set db = Nothing
End Sub



Public Sub GetLatestVersionNumber()
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("TBL_VERSION_LATEST", dbOpenDynaset, dbSeeChanges)

    rs.MoveLast
    wsh.PopUp rs![VERSION], 0, "Latest Version", vbOKOnly

    Set rs = Nothing
    Set db = Nothing
End Sub

The batch script "LaunchPMD.bat" seems straightforward,

@echo off

rem Set the local file path
set localFile=C:\Users\Public\PMDTools\PMD_FE.accdb

rem Check if the local file exists
if not exist %localFile% (
    rem If the local file does not exist, download the latest version from the server
    echo Downloading latest version
    "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" %localFile% /x DownloadLatestVersion
) else (
    rem If the local file does exist, compare the version numbers of the local and latest versions
    for /f "tokens=2 delims==" %%a in ('"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" %localFile% /x GetLatestVersionNumber') do set latestVersion=%%a
    for /f "tokens=2 delims==" %%a in ('"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" %localFile% /x GetLocalVersionNumber') do set localVersion=%%a

    rem If the latest version is newer than the local version, download it
    if "%localVersion%" LSS "%latestVersion%" (
        echo New version found: %latestVersion%
        msaccess.exe %localFile% /x DownloadLatestVersion
    )
)

rem Open the local file
echo Opening %localFile%
start "" %localFile%

but even after multiple re-writes I can't get it to work. There are no errors when I run the batch script in the cmd window. The only error thrown at the very end is this:

'GetLatestVersionNumber' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

CodePudding user response：

@echo off

rem Set the local file and access executable locations
set "localFile=C:\Users\Public\PMDTools\PMD_FE.accdb"
SET "access=C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"

set "latestVersion=z"
set "localVersion= "

rem Check whether the local file exists
if exist "%localFile%" (
  for /f "tokens=2 delims==" %%b in ('"