Batch file for updating local frontend Access database copy (2 Viewers)

[Sheridan]

I wrote a batch file for the first time. Hoping for some feedback.

This retrieves a copy of the latest frontend database and copies it to the user's C drive basically. I've tested it a fair amount in different scenarios and it seems to work.

The batch file is stored on a network drive. Users start it from a shortcut on their machine. I have another shorter batch file that just launches the local frontend copy. Shortcuts to both batch files are copied onto the user's machine if they're not already there. These shortcuts can also be pinned to the user's taskbar since I made them with "cmd /c".

(This updater batch file can also function as a first time installer, although I do have to manually add the new folder as a trusted location in Access after running the batch file.)

([...] in file paths are redactions for confidentiality reasons)

@echo off

REM Change mblVersionNumber to current MBL frontend version number in format. Must match exactly version number used in the Frontend filename stored on Z drive
set mblVersionNumber=v25.5

REM Checks for network drive connection. If not connected, prompts user and closes
if not exist "\\[...]\Data" ( 
    echo Please connect to Z drive first!
    echo Window will close after 5 seconds...
    timeout /t 5 /nobreak >nul
    EXIT
)

REM Checks if Access is open. Closes Access if open
tasklist /fi "ImageName eq MSAccess.exe" /fo csv 2>NUL | find /I "MSAccess.exe">NUL

if "%ERRORLEVEL%"=="0"  (
    echo Closing Access.
    taskkill /im MSAccess.exe
    echo Please wait 10 seconds...
    timeout /t 10 /nobreak >nul
    ) else (
    echo Access is not open
    )

REM Creates MBL Database folder on user's computer if not found
if not exist "%userprofile%\MBL Database" (
    echo MBL folder does not exist. Creating MBL Database Folder...
    md "%userprofile%\MBL Database"
    ) else (
        echo MBL Database folder exists.
    )
  
REM Deletes any and all frontend copies on user's machine
del /q "%userprofile%\MBL Database\MBL Frontend*.accdb"

REM Copies current frontend database to user's machine 
robocopy "\\[...]\Data\Current\[...]\Census Database and Tools" "%userprofile%\MBL Database" "MBL Frontend %mblVersionNumber%.accdb" /mt /z

if not exist "%userprofile%\MBL Database\MBL Database Updater Shortcut.lnk" (
    robocopy "\\[...]\Data\Current\[...]\Census Database and Tools\Updater" "%userprofile%\MBL Database" "MBL Database Updater Shortcut.lnk" /mt /z
)

if not exist "%userprofile%\MBL Database\MBL Frontend Launcher Shortcut.lnk" (
    robocopy "\\[...]\Data\Current\[...]\Census Database and Tools\Launcher" "%userprofile%\MBL Database" "MBL Frontend Launcher Shortcut.lnk" /mt /z
)
  
REM Changes current directory to MBL Database folder on user's machine because apparently ren doesn't work with %userprofile%
cd /D "%userprofile%\MBL Database"

REM Removes version number from local copy of frontend. This is done to prevent breaking any shortcuts to the local frontend copy the user may have made previously
ren "MBL Frontend *.accdb" "MBL Frontend.accdb"

set mblLocalPath="%userprofile%\MBL Database\MBL Frontend.accdb"

REM Starts Access with local copy of frontend in runtime mode
echo Launching MBL Access database
Start MSAccess.exe /runtime %mblLocalPath%

EXIT

 

[Pat Hartman]

I have another shorter batch file that just launches the local frontend copy.

You need to remove this shortcut. You NEVER want the user to choose which version of the FE to run. Your shortcut should always control that. The overhead of downloading a fresh copy each time the user runs the shortcut is miniscule and it has the added advantage of always opening a freshly compiled FE. That means that it has no bloat AND the first time the user runs each querydef, it will be recompiled and a new execution plan will be calculated and saved which means that you always end up with the most efficient execution plan for the current state of the BE. Of course if you look down on querydefs and don't use them, each time you run your embedded SQL, it will be recompiled and a brand new execution plan will be created but there is no where to save it. Luckily, Access fixed the bloat issue that used to be associated with calculating execution plans and the process is pretty quick anyway so you'd need to be running the same query thousands of times to see delay.

Also, for safety, you should have a version table in the FE and another in the BE. When the FE opens, the first thing it should do is to compare the two tables and ensure the versions match.

 

[Pat Hartman]

(This updater batch file can also function as a first time installer, although I do have to manually add the new folder as a trusted location in Access after running the batch file.)

You can have the batch file do this for you. No need to do it manually.

My batch file is only 4 lines of code with no error checking. The messages are clear enough that the user knows to report the error. There is no error thrown if you create an existing folder and there is no error if you overlay an existing file with one of the same name. Therefore, the only errors would be not finding the master copy of the FE or a network error.

 

[Gasman]

I always preferred to download a new copy ONLY when one existed.

Horses for courses.

 

[Pat Hartman]

I always preferred to download a new copy ONLY when one existed.

I know that seems like it is more efficient but always downloading a FRESH copy has advantages. See prev post.

 

[Sheridan]

You need to remove this shortcut. You NEVER want the user to choose which version of the FE to run. Your shortcut should always control that. The overhead of downloading a fresh copy each time the user runs the shortcut is miniscule and it has the added advantage of always opening a freshly compiled FE. That means that it has no bloat AND the first time the user runs each querydef, it will be recompiled and a new execution plan will be calculated and saved which means that you always end up with the most efficient execution plan for the current state of the BE. Of course if you look down on querydefs and don't use them, each time you run your embedded SQL, it will be recompiled and a brand new execution plan will be created but there is no where to save it. Luckily, Access fixed the bloat issue that used to be associated with calculating execution plans and the process is pretty quick anyway so you'd need to be running the same query thousands of times to see delay.

Also, for safety, you should have a version table in the FE and another in the BE. When the FE opens, the first thing it should do is to compare the two tables and ensure the versions match.

That makes sense. I'll remove the second shortcut. Thanks!

I did implement a version table also. I have some VBA that runs on the timer event with the default form ("switchboard") to compare version numbers between FE and BE. If it detects the FE is out of date, it prompts the user with a modal dialog; when they click 'OK' it shuts down Access.

Private Sub Form_Timer()
    Dim varRetVal
    Dim feFrontendVersionNumber As Double
    Dim beFrontendVersionNumber As Double
    
    On Error GoTo ErrHandler
    
    varRetVal = DLookup("SystemUP", "ztblSystemStatus")
    
    feFrontendVersionNumber = DLookup("FrontendVersion", "ztblFrontendStatus")
    beFrontendVersionNumber = DLookup("FrontendVersion", "ztblSystemStatus")
    
    If Nz(varRetVal, 0) = 0 Then
        DoCmd.OpenForm "frmSystemExit", acNormal
        ElseIf Nz(feFrontendVersionNumber, 0) < Nz(beFrontendVersionNumber, 0) Then
            DoCmd.OpenForm "frmOldVersionSystemExit", acNormal
    End If
    
ErrExit:
    Exit Sub
    
ErrHandler:
    MsgBox "Error is " & Err.Description & " in " & Me.NAME
    LogError "Form_Timer", Err.Number, Err.Description
    Resume ErrExit
    
End Sub

 

[Sheridan]

You can have the batch file do this for you. No need to do it manually.

You can add a folder to Access's trusted locations with a batch file? How?? That would be incredible for me. I might not have to log into a user's machine at all to set them up if that were possible.

 

[AHeyne]

In a domain you can add trusted locations also via group polices (some of our customers do that).

 

[Pat Hartman]

I did implement a version table also. I have some VBA that runs on the timer event with the default form ("switchboard") to compare version numbers between FE and BE. If it detects the FE is out of date, it prompts the user with a modal dialog; when they click 'OK' it shuts down Access.

The Timer event is not the correct event to use. Use the Open event of the first form that loads. There is no need to check versions repeatedly once the app verifies the connection.

If you make new versions and upload them to the master folder mid-day, you need to inform your users and remind them to shut down and reopen the app to get the new version. NONE of these mid-day versions will ever contain schema changes so unless you are fixing a critical bug, there is probably no urgency to get the user to switch to the new version. It will happen naturally the next time he opens the app.

The Timer event runs in the background and it is constantly ticking like an annoying Grandfather clock. The ticks take time and when the event fires, it interrupts what the application is currently doing. I don't know what would happen if some other form was waiting for a response from the server after trying to save and the timer interrupted the code. Maybe, Access is smart enough to recognize that some other form is awaiting a response and wait to interrupt until the response is received. If the Switchboard is not always open, then the timer is useless anyway so just use the Open event to check versions. If you insist on doing it your way, then use the Activate event of the Switchboard. That should run when the focus returns to the Switchboard after doing something else.

Here is a link to some videos and an application that shows how events work. Please watch at least one of the videos before playing with the application.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[Pat Hartman]

You can add a folder to Access's trusted locations with a batch file?

You create a file that adds the folder to the registry and then add a line in the batch file to run it. This one you might need to check for existence. I don't handle trusted locations this way. I rely on the IT department to create the correct entry using their domain policies.

 

[Richard Rost]

You don’t necessarily need to go down the batch file route to handle front-end updates. A common pattern I use is to split the logic into two Access databases. The first one is just a small “launcher” file. That’s the one your users actually open. All it does is check the back-end (or a network location) to see if the main front-end is up to date, and if not, it replaces the old copy with the new version. Once it confirms everything is current, it opens the real front-end database.

That approach keeps the code simple, avoids relying on external scripts, and works entirely inside Access. In practice it’s usually easier to maintain than trying to write and debug batch files for every workstation. And the "main" database can even update the updater.

LLAP
RR

 

[Pat Hartman]

In practice it’s usually easier to maintain than trying to write and debug batch files for every workstation. And the "main" database can even update the updater.

You don't write a separate batch file for each workstation. The path to the master copy should be UNC notation to avoid drive letter conflicts. The batch file is stored on the server and run from a shortcut on each workstation. Once the batch file is written and tested, it never needs changing unless the path to the master FE changes and then you only need to change the copy on the server.

I am not arguing against the loader db per se but if you don't know VBA, you'll have an easier time with the batch file (as long as you use the simplest version necessary which is 4 lines of code) even if you find a loader you can use. I don't recommend using code you don't understand.

 

[Richard Rost]

You don't write a separate batch file for each workstation. The path to the master copy should be UNC notation to avoid drive letter conflicts. The batch file is stored on the server and run from a shortcut on each workstation. Once the batch file is written and tested, it never needs changing unless the path to the master FE changes and then you only need to change the copy on the server.

I am not arguing against the loader db per se but if you don't know VBA, you'll have an easier time with the batch file even if you find a loader you can use. I don't recommend using code you don't understand.

That’s an excellent point, and I agree completely about not using code you don’t understand. A batch file can certainly work fine once it’s set up with the proper UNC paths. I was just offering another alternative with a loader database, which can handle updates from inside Access itself. Both approaches are valid depending on what someone is comfortable with.

 

[Pat Hartman]

Both approaches are valid depending on what someone is comfortable with.

We are in agreement

 

[DickyP]

Not a new idea - it was our way of rolling out updates in Access 2 days in the early 1990s.

We kept an .ini file on the server with the current version number and if your FE was not the latest version you got message telling you to run the batch file and closed your FE.

 

[BlueSpruce]

I wrote a batch file for the first time. Hoping for some feedback.

This retrieves a copy of the latest frontend database and copies it to the user's C drive basically. I've tested it a fair amount in different scenarios and it seems to work.

The batch file is stored on a network drive. Users start it from a shortcut on their machine. I have another shorter batch file that just launches the local frontend copy. Shortcuts to both batch files are copied onto the user's machine if they're not already there. These shortcuts can also be pinned to the user's taskbar since I made them with "cmd /c". . .

We've been using desktop shortcuts that run a simple batch script for years with no issues. It copies the master FE that's in a CurrentVersion folder on the server to each user's FE_Copy folder, overwriting the previous FE_Copy, verifies that it was correctly recorded, and launches it.

CD C:\

COPY S:\Databases\Clinic\Users\CurrentVersion\FE_Master.accdb "C:\FE_Copy\FE_Copy.accdb /Y /Z /V

"C:\FE_Copy\FE_Copy.accdb"

EXIT /b

 

[arnelgp]

as suggested in post #13, you can use an MS Access Loader (or startup db) that wll check if there is new version of your FE somewhere
and copy it (if it is the latest), then close the loader and start your normal Access app.

there is such db on post #4 on this thread:

autoupdate Front End

Hello, this is my front end, file path in server: \\ABCbrickyard\PQRShared\Central\EVS\EVS_Inventory\FEbkup\FE_Evs.accdb My question is how can we make it as soon as the users open their front end, it will automatically update by copying it from the server and replace their front end. I am as...

www.access-programmers.co.uk

 

[Pat Hartman]

The issue with relying on the loader to check the version is that you need to duplicate at least some of the code in your FE because if that pesky user simply navigates to his downloaded FE and opens it, all your validation is bypassed.

 

[Gasman]

The issue with relying on the loader to check the version is that you need to duplicate at least some of the code in your FE because if that pesky user simply navigates to his downloaded FE and opens it, all your validation is bypassed.

So the builtin version checker is useful after all?

 

[Pat Hartman]

So the builtin version checker is useful after all?

That is where it belongs