Update Front End

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

 

[Pat Hartman]

this would mean user is ALWAYS copying a new version when opening app

That is actually the point. There are a couple of advantages
1. The FE files are not very large so the time to download them on a LAN is miniscule. Using Citrix or RD, there is no download so the LAN lag isn't a problem. The file is copied from one directory/drive to another on the same Server.
2. the FE should always be distributed with the querydefs "uncompiled", they are "compiled" the first time they execute and a new access plan is created. This means that the query will always be optimized for the current state of the BE. If you reuse the same FE day after day, the query's execution plans can become inefficient given the current state of the BE. Otherwise, you should coordinate the C&R of the BE and distribute new FE that have been freshly compacted also so they can take advantage of the new BE statistics. Obviously, if your BE is small or doesn't grow/shrink rapidly, then almost nothing you do matters. I frequently create apps with multi-million row tables and so to me, it matters. Once I find a better, more efficient way to do something, I use that method for every new app. I don't sit around and think about whether it won't ever matter if I use the less efficient method.
2. Bloat - is eliminated as a problem even if you use poor practices and run make table or Append/Delete queries in the FE. so the FE NEVER has to be compacted.
4. Corruption -since a new copy is loaded each day, clean copies of everything are always used. If the power goes out or the network locks up, the user can just close the app and when he reopens it with the shortcut, he gets a fresh copy so those two events which frequently cause corruption, don't affect FE's distributed using my method.
5. A lot of users leave the app open all day rather than opening and closing it so they would rarely be downloading a new copy more than once or twice per day - morning and after lunch if they power down.
6. You could add a condition statement to check the date of the local copy. If it is < the date of the Server version, copy, otherwise don't. Then the batch file works just like the starter app but you lose advantages 1-5.

 

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

 

[NauticalGent]

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

To answer this question, there is really no advantage in this case. I use VBS for my autoupdater and converted some of it to PS and although it took a little less code, VBS was MUCH quicker.

 

[theDBguy]

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

Also, I could be wrong, but doesn't using PowerShell require admin rights? If so, would all your users be able to use it?

 

[Emily Jones]

Also, I could be wrong, but doesn't using PowerShell require admin rights? If so, would all your users be able to use it?

Ahh, that's a thought. A quick google reveals that yes, admin rights are needed, but there are various workarounds, depending what the sysadmins have done. But if there's no particular advantage to Powershell compared to a good old fashioned batch file, I'll stick with .bat

Thanks.

 

[Kayleigh]

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?

I currently use version numbering to check the user's FE is latest copy - when application opens so it would run the FE Updater at that stage when no critical functions are running so it would not be dangerous to close at that point. If you want to force an update whilst they are working, you can put a force shutdown procedure in place which will run as soon as the timeout flag is up.
More info on this can be found here: http://www.accessmvp.com/JConrad/accessjunkie/kickoff.html

 

[Pat Hartman]

If you use my example Citrix batch file posted earlier, you will get an error message because it tries to delete the local copy of FE which is open. Therefore, the batch file will fail. If you want to add a proper error message to the batch, that would work. Funny, in all the years I've been using this, no one has complained. Apparently if they've run the shortcut when the app was open, the error message was clear enough.

You can keep FE versions in sync by using a table in the FE and in the BE both have the latest FE version and they should always be equal or the app will close.

 

[so10070]

I use this and it works now for several years

                    Open BatchFile For Output As #1
                        Print #1, "@Echo Off"
                        Print #1, "ECHO Delete old version"
                        Print #1, ""
                        Print #1, "ping 127.0.0.1 -n 5 -w 1000 > nul"
                        Print #1, "Del """ & LocalFile & """"
                        Print #1, ""
                        Print #1, "ECHO New version is copied ..."
                        Print #1, "Copy /Y """ & MasterFilePad & """ """ & LokaalFile & """"
                        Print #1, ""
                        Print #1, "ECHO Application restarts ..."
                        Print #1, "Start /I " & """MSAccess.exe"" " & Restart
                        Close #1
                        
                    'run Batchfile
                    Shell BatchFile

 

[Emily Jones]

If you use my example Citrix batch file posted earlier, you will get an error message because it tries to delete the local copy of FE which is open. Therefore, the batch file will fail. If you want to add a proper error message to the batch, that would work. Funny, in all the years I've been using this, no one has complained. Apparently if they've run the shortcut when the app was open, the error message was clear enough.

You can keep FE versions in sync by using a table in the FE and in the BE both have the latest FE version and they should always be equal or the app will close.

Presumably you mean a table in the FE on the workstation and also in the update copy of the FE? I usually use the term BE to refer to the shared data file.

Assuming that's what you mean, yes, that's what I've been doing. Using a VB6 "updater" that I wrote. It's OK, but to be honest I don't really think I need anything that "sophisticated". If I did, I'd just pay for the FMS thing. The batch file approach seems simpler, and I don't have to deal with Windows thinking my executable is a virus or whatever. So long as it can't overwrite the open file, that's fine. And yes, I don't think it would be a problem very often anyway. At the moment I always tell them to close and reopen the database if I do an update during working hours.

Thanks.

 

[The_Doc_Man]

The original complaint has already been answered twice with the correct answer. When you create a batch file to replace the active DB version and execute it, the file to be deleted is currently in use and therefore is delete-locked by the Windows File System. You CANNOT delete an open file. (See, for example, all of the problems with .LDB or .ACCDB files when a user hasn't disconnected correctly.) It can be "marked for delete" but it cannot be deleted until its current lock owner exits. So the "self-replacing script from the DB itself" approach can never work with immediate timing. Only with deferred timing at best.

A lot of the objections seem to be that the users have various locations where they can put the FE file. Who cares? That is location "." in a file path. If your users have different drive letter mappings, again... who cares? Use URS paths to get to the BE file and BE folder. Then a script is no big deal.

 

[Pat Hartman]

No, I mean a FE table and a BE table. But it is more to ensure that the FE is connected to the correct version of the BE. Although, you can use it for both if you keep two columns. One for FE version and the other for BE version. They are checked when the FE opens. I always release a new FE when I make changes to the BE, otherwise, the BE changes would never be used.

 

[Emily Jones]

No, I mean a FE table and a BE table. But it is more to ensure that the FE is connected to the correct version of the BE. Although, you can use it for both if you keep two columns. One for FE version and the other for BE version. They are checked when the FE opens. I always release a new FE when I make changes to the BE, otherwise, the BE changes would never be used.

I see, thanks. I rarely change the BE file. Or at least a lot less often than the FE. And there's only ever one BE file.

I have a FE file on each user's machine (or now in their Documents folder, in a folder redirect on the server) and on the Server there's an "update" folder in which I place my latest version of the FE.

The update .exe I wrote checks a version number in a table in each, and drags the new copy across, if they don't match. I think that's a fairly common way of distributing a new FE file, yes? Which is why I so wrongly assumed that's what you meant. Apologies for the misunderstanding.

My way sort of works, but there is a bit of maintenance overhead to having an exe run. Such as UAC warnings, and anti-virus programs flagging the .exe. If the program did a lot more things, like Total Access Startup does, then I might keep my updater and add features. But as it's such a simple thing I want to achieve, then a plain batch file seems like a better idea.

Thanks for your help.

 

[Emily Jones]

The original complaint has already been answered twice with the correct answer. When you create a batch file to replace the active DB version and execute it, the file to be deleted is currently in use and therefore is delete-locked by the Windows File System. You CANNOT delete an open file. (See, for example, all of the problems with .LDB or .ACCDB files when a user hasn't disconnected correctly.) It can be "marked for delete" but it cannot be deleted until its current lock owner exits. So the "self-replacing script from the DB itself" approach can never work with immediate timing. Only with deferred timing at best.

A lot of the objections seem to be that the users have various locations where they can put the FE file. Who cares? That is location "." in a file path. If your users have different drive letter mappings, again... who cares? Use URS paths to get to the BE file and BE folder. Then a script is no big deal.

Thanks very much for you help. I'm sorry if it gets tedious reading the same answer more than once.

 

[The_Doc_Man]

In turn, I'm sorry if my direct style seems a bit brusque, but I'm treading the line between incomplete answers and long-winded answers. (The folks here will attest to my occasional long-winded answers.) So it may come out harsher than is intended.

 

[NauticalGent]

(The folks here will attest to my occasional long-winded answers.)

You? Long winded? Surely you jest...