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.
