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.

 

[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

 

[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

 

[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.

 

[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,

 

[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....

 

[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?

 

[isladogs]

@JasonH
Thanks. Its a simplified version of my starter app and has worked well for years. I've never liked the batch file approach either although it works fine as well.

@Krayna
I really don't understand why you think either approach requires copying a new file or shortcut on each occasion. It isn't essential.
Some developers do use that approach to ensure users have a 'clean' copy of the FE each time but that isn't an approach that I use.

 

[sonic8]

@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?

You call CMD and pass on the file name of the script as a command argument. Untested example:

Shell Environ("COMSPEC") & " /c """ & TestFile & """"

You should also investigate your suspicions regard permission problems. Maybe the admins disabled CMD for normal users altogether which would explain the problem you see.

 

[gemma-the-husky]

I am not 100% sure what you are doing, but if you manually run a batch file to overwrite a access dbs with a different version, but with the same name that's not a problem.

If you try to do that from the database itself it will fail, as the database will be in use, and cannot be deleted. Is that what you are trying to do?

 

[sonic8]

If you try to do that from the database itself it will fail, as the database will be in use, and cannot be deleted. Is that what you are trying to do?

Look at the code in the first post.
The code creates a batch file that is trying to overwrite the database itself in a loop until it succeeds. That will indeed fail while the database is still open, but it will succeed once the database is closed.

 

[Emily Jones]

I am about to write, er, something, to do this very thing. Updating the front end.

History:

I wrote my own FE updater in VB6, years ago. It's OK, but keeps tripping up over UAC warnings, which is annoying for the users. Also the users are now doing a lot more remote work, via RDP onto the server running RDS, so now the FE needs to be in each user's Documents folder, which are in a redirected folder on the server.

A simple batch file seems like a straightforward option. I like the idea of the users always having the latest version. The FE file is only 7MB, so the copying won't take any time worth worrying about, especially as it's happening on the same machine.

Two questions:

1. Would Powershell give me any advantages over a .bat file?

2. How do I cope with the case where a user tries to run my batch/Powershell file but the Access FE is already open, on their "machine" (real or virtual). At the moment if I want to update the FE, during work hours, I tell the users to close and reopen. As far as I can tell there isn't a way to force close Access - and maybe it's too dangerous anyway, in terms of potential corruption. But is there a way, in the case I've suggested, of at least opening a prompt, asking the user to close the database?

Thanks.

 

[arnelgp]

1. i don't see any advantages.
2. you need to ensure that your db will close first (you write code), before it is copied over.

there is another technique aside from bat file.
autoupdate Front End | Access World Forums (access-programmers.co.uk)