Front End Auto Update (1 Viewer)

Tieval · May 19, 2017

I have a back-end located on a server at z:\database\be.accdb (for example) and users copy the latest version of the front-end to a random location on their local drive.

The latest version of the front-end is stored on the server at z:\database\front\fe.accdb and historically users were asked by e-mail to update their copy when necessary.

I recently added a check for updates by putting a version number in tables on both the front-end and back-end:

Code:

Public Function VersionIs()
'Version check on start-up
If (DSum("VNo", "tblVersFE") = DSum("VNo", "tblVersBE")) Then DoCmd.OpenForm "Welcome"
If (DSum("VNo", "tblVersFE") <> DSum("VNo", "tblVersBE")) Then MsgBox "Please Install New Version" & vbCrLf & "Current Version Out of Date", , "Warning"
End Function

This made the database load if the version was correct or ask the user to manually update but I now want to take it a step further.

How can I change the second if statement so that it detects where the front-end resides, closes the database, copies the latest version from z:\database\front\fe.accdb, overwrites the original in the local place and starts the database?

Any help would be greatly appreciated.

Ranman256 · May 19, 2017

You can't overwrite the old version if the user is in it. If they are not in it,it can't run the copy.

The way i did, ALL users have the FE in the same folder, "c:\app\fe.mdb"
Then I send an email to update, they click the link and it copies.

Tieval · May 19, 2017

What I was thinking is running another database in the same front-end location say updater.accdb so you could open it from the original, close the original, do the copying, close the updater and load the front end again.

Minty · May 19, 2017

This writes a batch file to the current application path and closes the current db, copies the new one over and opens it.

It's not my code found it somewhere on the net and modified it a bit to suit.

Code:

Public Sub UpdateFrontEnd()

Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String
Dim strBackUp As String
Dim strTarget As String
Dim sBak As String
Dim iLeng As Integer
dim g_strFilePath As String
dim g_strCopyLocation As String


g_strFilePath = CurrentProject.path
g_strCopyLocation = DLookup("DBUpdatePath", "A systemTableWithThe Path") 

[COLOR="DarkOliveGreen"]'Set up check for mdb or accdb - makes the code universal even after mdb > [/COLOR]accdb upgrade.
If Right(CurrentProject.Name, 3) = "mdb" Then
    sBak = "_bak.mdb"
    iLeng = 4
Else
    sBak = "_bak.accdb"
    iLeng = 6
End If

' sets the file name and location for the file to delete
strKillFile = g_strFilePath & "\" & CurrentProject.Name
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
' sets the file name of the backup file to create
strBackUp = strKillFile
strBackUp = Left(strBackUp, (Len(strKillFile) - iLeng)) & sBak
strTarget = Left(CurrentProject.Name, (Len(CurrentProject.Name) - iLeng)) & sBak
Debug.Print strBackUp
' sets the file name of the batch file to create
TestFile = CurrentProject.path & "\UpdateDbFE.cmd"
' sets the restart file name
strRestart = """" & strKillFile & """"
' creates the batch file        -- The 10 after the ping -n is the delay for it to copy needs to be this for large front ends
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "if exist """ & strBackUp & """ del """ & strBackUp & """"
Print #1, ""
Print #1, "ECHO Copying backup file"
Print #1, "Echo Off"
Print #1, "ping -n 14 127.0.0.1 > nul"
Print #1, ""
Print #1, ":Fileready1"
Print #1, "Ren """ & strKillFile & """ """ & strTarget & """"
Print #1, "ECHO Copying New file"
Print #1, "Echo Off"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, ":checkfilecreation"
Print #1, "REM Now that a .mov file has been added to the folder check to see if the file has "
Print #1, "REM completed the conversion or copying to the folder before starting "
Print #1, ""
Print #1, "CLS"
Print #1, ""
Print #1, "for %%I in (""" & strKillFile & """) do ( (call ) >>%%I ) 2>nul && (cls && set b=%%I && @echo !b! is completed and transfer is ready && GOTO :fileready"
Print #1, ") || (cls && echo %%I is still being created"
Print #1, ")"
Print #1, ""
Print #1, ":ContinueCheck"
Print #1, "REM Go back to checkfilecreation module"
Print #1, "GOTO :checkfilecreation"
Print #1, ""
Print #1, ":Fileready"
Print #1, "Echo CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1

Call SetOption("Auto Compact", False)
'Exit Sub
' runs the batch file
Shell TestFile

'closes the current version and runs the batch file
DoCmd.Quit

Frothingslosh · May 19, 2017

Easiest solution is to create a permanent batch file that kills the existing local copy, then downloads the most recent front end from the network. You can add it checks to see if a block file exists (to stop the batch from executing) and anything else you might want to do with a batch file while you're at it. At the end, have it open the application.

Save that batch file on the network (probably in the same folder as the front end), and then distribute to your users a shortcut pointed at the BATCH file.

If you want to do it entirely within Access, you can look at Minty's code or a complete version I posted HERE some years ago. (Disclaimer: the linked code is a modified version of something Bob Larson posted years before that.)

Tieval · May 19, 2017

Many thanks Minty, will take a good look

The_Doc_Man · May 19, 2017

There are MANY articles on this subject if you use the SEARCH feature of the forum, and some of them will include the batch-file code. Try that search just for snorts & giggles.

Gasman · May 19, 2017

I use Bob Larsons autoupdating utility which does the pretty much the same as Minty's code.

Minty · May 19, 2017

My code could well have been an adapted version of Bob's - If it obviously was I will happily add his name to it.

The only real modifications I've made are to try and detect that the copy has completed. If you have a large front end app and a slow network you may find that original code tries to open the database before the copy has completed.

I know this shouldn't happen but can assure you that it does, but only when the batch file is called from another application! It took me ages to figure what was happening.

Frothingslosh · May 19, 2017

Odd.

We have a few thousand people where I work and haven't had that happen yet. It's good to know about it, though, in case it does.

Minty · May 19, 2017

It's only likely of your FE is larger than 40-50Mb , that's roughly the size when we started getting the issue.

If you run the batch file directly from the file explorer / command prompt it never does it.

The_Doc_Man · May 19, 2017

I usually take the approach to copy the new FE to a different name, then rename the old one to another name, then rename the new copy to the right name, then delete the old copy, then launch. Perhaps it is only a matter of allowing for a little extra timing, but this method allows me to have a potentially usable copy present if something goes wrong. But I agree that using the auto-update batch procedure as the target of the app icon is the right way to go if you want to enforce an auto-update.

isladogs · May 20, 2017

Very similar in my case also...

I use a separate updater utility (splash screen) which runs when users click the desktop shortcut.
This compares the local & network versions and copies files from the network if the network version is newer (date modified) or has a higher version number.
Total size of files copied can be around 150MB each time.
However, file copying is fast as it uses Windows APIs

In addition the updater checks whether there is a newer version of itself on the network.
If so, it copies it to the local drive as e.g. SDAUpdater_NEW.accdb

The main program file then opens and the updater closes
The main program checks if SDAUpdater_NEW.accdb exists and, if so, renames it as SDAUpdater.accdb overwriting the original which has by now closed.

In over 5 years on 200+ client PCs, this has only failed once.
However as a backup the splash screen contains a 'Force Update' button so users can copy all files again in an emergency

Tieval · May 22, 2017

Still playing, will comment on completion (hopefully), many thanks all

AndrewS · Apr 24, 2018

Minty said:

Code:

 "for %%I in (""" & strKillFile & """) do [COLOR="Orange"]( (call ) >>%%I ) 2>nul && (cls && set b=%%I && @echo !b![/COLOR] is completed and transfer is ready && GOTO :fileready"

Thanks for posting this, Minty.

I know it's a year on, and my question isn't strictly Access related, but for my general education can you explain what's happening in the above line of the batch file?

I don't like using code that I don't understand, as I can't really troubleshoot it when something goes wrong, and I don't follow the bit I've coloured orange above.

Minty · Apr 25, 2018

Now you are asking

I picked that up from another forum, as a way of checking that the copy process had completed.

When I read about it, it made perfect sense, and was pretty neat solution to the issue I was having with the copy not completing before trying to open the destination file. I'm afraid I can't remember the actual Ninja manoeuvres of the cmd string though. Sorry.

AndrewS · Apr 25, 2018

Ha! the arcane mysteries of DOS command lines ...

It does seem to work nicely though

I made one change to the penultimate line of the batch file writing procedure to

Code:

      Print #1, "START /I " & """MSAccess.exe"" " & Restart & " /runtime"

to open it in runtime mode (i.e. without ribbon, access to code etc)

Front End Auto Update (1 Viewer)

Tieval

Still Clueless

Ranman256

Well-known member

Tieval

Still Clueless

Minty

AWF VIP

Frothingslosh

Premier Pale Stale Ale

Tieval

Still Clueless

The_Doc_Man

Immoderate Moderator

Gasman

Enthusiastic Amateur

Attachments

Minty

AWF VIP

Frothingslosh

Premier Pale Stale Ale

Minty

AWF VIP

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

Attachments

Tieval

Still Clueless

AndrewS

Registered User.

Minty

AWF VIP

AndrewS

Registered User.

Similar threads

Users who are viewing this thread