Update Front End

[Kayleigh]

Hi,
I have a very effective piece of code which I've used in other systems successfully to update the front end to the latest version. I am finding that it will run the code to the point where it creates the batch file but its not actually running the batch file to delete the old file and replace with new version. Only if I manually run it will it work. Any ideas what I can change here?
(I was thinking of performing a check when the database is loading to find the batch file and run it but I don't think it will work because it opens the DB in a state that requires repair)
Code:

Option Compare Database
' global variable for path to original database location
Public g_strFilePath As String
' global variable for path to database to copy from
Public g_strCopyLocation As String


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

' sets the file name and location for the file to delete
strKillFile = g_strFilePath
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
' 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
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1
'Exit Sub
' runs the batch file
Shell TestFile

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



End Sub

 

[sxschech]

could be a timing issue. Can try putting DoEvents before the shell command, but may need more time than this would provide.

 

[Pat Hartman]

I think you're making this harder than it needs to be.
Create the batch file and save it on the server
Distribute a shortcut to each user to save on his desktop. The shortcut runs the bat file.
The bat file downloads the new version of the FE and opens it.

If you want to distribute a new FE during work hours, send out an email telling folks to shut down and reopen. If you want to do something fancier, you can use a table in the be that the menu checks each time it is activated and have it tell the user to shut down.

 

[Kayleigh]

I don't think that will work for me because each user's project path depends usually depends on the currentuser variable but some users have it installed locally so the path is not consistent for all users. Also I am often releasing new updates when bugs are detected, users request additional functionality etc so it would not be user-friendly to keep requesting users to run a batch file.
The code I use above would create the batch file which copies the master version to current project location and then automatically opens the new version without requiring user input. As I mentioned for some reason the batch file was not running automatically.

I tried the DoEvents solution but it caused an Automation error.

I am considering another approach - create the batch file, close the DB, then when the reopens the DB it would look for the batch file in the currentpath location and run it if it finds it, but maybe this is just the same as what I have already been doing?

 

[isladogs]

You may be interested in a slightly different approach that I use.
Instead of a batch file, when users click on a desktop shortcut they are actually running a starter app. It looks like a splash screen 'floating on the desktop'.
This checks the local version against the version on the network and downloads that if newer or just starts the local version before closing itself (the starter app).
This can even update the starter app itself if there is a newer version. The whole process is fast and seamless.

I posted my starter app somewhere on here around three years ago.
Alternatively look at my somewhat similar example app Web Version Checker - Mendip Data Systems

 

[Pat Hartman]

This is a sample of what a batch file looks for Citrix. RD would be similar.

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb

Even in this environment, the user needs a separate copy of the FE but the BE is always in the same place. Use the UNC path when you link to the BE from the FE so you avoid having to map a drive. If you have both local and remote users. That way the path will be the same for both.

When the batch file runs, the user's profile is used to fill in the variables.

so it would not be user-friendly to keep requesting users to run a batch file.

You're not asking them to run a bat file. You are asking them to click on a shortcut. Unless they know how to open the shortcut and examine what it is doing, they would have no clue that it was running a bat file. And just how are you distributing your changes if you are not doing it this way? Please don't tell me that you are all working from the same FE and you are changing it on the fly while the users are working?????

 

[Kayleigh]

NO! The FE is updated off-site and then master version is updated. The version number is changed so when DB is opened it compares current version number to master version and will update if inconsistent.
Does your suggestion mean that I will always have to copy a shortcut onto every desktop whenever there is an update? I can do it but it will be a bother...

 

[isladogs]

Does your suggestion mean ...

Whose suggestion?
With my approach, you won't have to copy anything manually. Not will you need to do so with any other approach based on a batch file

 

[Pat Hartman]

Does your suggestion mean that I will always have to copy a shortcut onto every desktop whenever there is an update? I can do it but it will be a bother...

No. If you are using Citrix or RD, you can have the shortcut loaded each time someone logs into their desktop. In the case where a Desktop is not instantiated but only the app is opened directly, the IT folks again take care of this. Think of "adding the shortcut" as something that happens automatically when you log into your PC. You give the shortcut (or the .bat file) to the IT people and they will know what to do with it.

Distributing the shortcut is a once and done proposition just as it is for your LAN users.

 

[Kayleigh]

I understand. What I didn't mention is that the environment where the system is being used don't have an on-site IT team so it is impractical to rely on them to copy the shortcut to each desktop. In the past I have just logged into the server and created the group policy to create the shortcut for the DB but I don't want to have to do this every time.

I think @isladogs may be the way forward. I've downloaded your web version checker to take a look.
I also looked at an option here which is similar to what I've been doing already.

Problem with both examples is that they won't run on my version of Access. Were they made for 32-bit?

 

[isladogs]

All my examples run in both 32-bit and 64-bit. That example was created in A2010.

 

[sonic8]

Shell TestFile

Does Shell actually run a batch file directly? I would try to invoke CMD and pass the batch file name as an argument.

 

[Pat Hartman]

but I don't want to have to do this every time.

Every time? What are you talking about? You set up the group policy and it runs when the user logs in. It is a ONE time event for you.

If you don't want to use the batch file, that's fine but I think you are out of excuses for why running a saved batch file with arguments won't work or why it is more trouble. What you are doing is more trouble. You are trying to build a custom batch file to run every time but that is not necessary.

Used the starter app suggested by Colin if that makes you feel better. Just don't continue on your current path.

 

[bastanu]

Another free starter app to examine:

FBA DB Launcher - MS Access front-end deployment tool - ForestByteApps

ForestByteApps - A place for Microsoft Access code samples and utilities to make your life easier. Free Microsoft Access tools and sample VBA code

forestbyte.com

Cheers,

 

[Pat Hartman]

OR use the competing starter app suggested by bastanu since this must be a contest.

 

[isladogs]

I see these suggestions as providing alternative options for the OP to consider....certainly not a contest!

 

[bastanu]

Same here but maybe Pat with her experience sees it otherwise....

 

[Pat Hartman]

I wasn't referring to your original suggestion. Colin I was referring to the suggestion for the second version of a starter app when we have no idea if the OP is even interested in something other than a batch file. So far all the OP has done is argue with me about why he thinks my version of a batch file won't work for him. He still thinks his situation is somehow unique and requires separate batch files for each user. Maybe he doesn't understand what %USERPROFILE% does in a batch file.

 

[Jason Lee Hayes]

You may be interested in a slightly different approach that I use.
Instead of a batch file, when users click on a desktop shortcut they are actually running a starter app. It looks like a splash screen 'floating on the desktop'.
This checks the local version against the version on the network and downloads that if newer or just starts the local version before closing itself (the starter app).
This can even update the starter app itself if there is a newer version. The whole process is fast and seamless.

I posted my starter app somewhere on here around three years ago.
Alternatively look at my somewhat similar example app Web Version Checker - Mendip Data Systems

Never really liked the batch file approach; wrote my own application in AutoIT a few years ago which is self contained only to find your example which is even better... Perfect.... Thanks for sharing...

 

[Kayleigh]

I am not deliberately arguing with the suggestions in the forum here! I just found the method I used already worked efficiently for my purpose. Not keen on the approach of running a shortcut every time user logs in OR of running starter app because this would mean user is ALWAYS copying a new version when opening app - this is not necessary and would put off the user if they have to wait 30 seconds - 1 minute every time they open the app. Updates are rolled out often but not daily so I see it as overkill to always have to copy the latest version when its likely they are already on the latest version.

@sonic8 can you elaborate how I can invoke the CMD for the shell file??

I just find it so weird that this code works perfectly in other environments but here it would not function effectively. Maybe its a file permissions thing?