Update Front End

[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

 

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

 

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

 

[Sun_Force]

I've seen so many threads on how to update FE. And anyone who suggests using a bat file, uses COPY command to copy the new version.

I just wanted to say COPY command is dead. It's a very long time since Microsoft introduced ROBOCOPY.
ROBOCOPY is multi threaded, hence faster. Has logging capabilities, You can use it to keep backups by not using /mir. You can copy folder and file time stamps to the original. It has much more options, Etc etc.

And above all, it has a progress meter that shows copying (in this case updating) progress. At least the user faces a progressing percentage instead of an hourglass mouse icon.

 

[isladogs]

Robocopy does indeed have all of those benefits but it is still well over 20 years old and based on DOS.

I suggest using an API to do this.
Copying will be MUCH faster & you will get progress shown similar to that below (depending on your Windows version)

 

[Sun_Force]

I suggest using an API to do this.

I've never liked APIs. Mostly because I don't understand them and I can't write them. I don't feel comfortable copying and using something that I don't understand.
I leave it to pros & geniuses like you or others. I prefer to keep my limits and work with a bat that I can understand and modify to my needs.

I also assume you need to run the API from a strater. It's another thing I can't get used to. I think it's two step more than what's needed.
Launch starter. Launch your app. Close starter.

And since each step takes a little while, why should I go the longer route when there's a shorter one?

And I really don't find any privileges using a starter for updating FE rather than using a bat.

 

[Emily Jones]

A 20 year old program sounds good to me. Thoroughly tested. Like Joel Spolsky says about old software- "It doesn’t acquire bugs just by sitting around on your hard drive."

That's an unusual use of the acronym "API". I thought it was short for "Application Programming Interface". Not quite sure what application is being programmed here. It's hard to describe a short routine of a few lines, to copy a file over, as an "application".

 

[The_Doc_Man]

Like Joel Spolsky says about old software- "It doesn’t acquire bugs just by sitting around on your hard drive."

Unfortunately, Joel is incorrect. Old software acquires bugs by not being updated when the O/S is forced by circumstances to embrace new features that modify the meaning or even availability of certain options. That which stands still can be passed by. Particularly since at some point, MS drop-kicked the concept of backwards compatibility out into the trash heap. (And has done it again with Windows 11, which won't run on every machine.)

 

[Emily Jones]

Unfortunately, Joel is incorrect. Old software acquires bugs by not being updated when the O/S is forced by circumstances to embrace new features that modify the meaning or even availability of certain options. That which stands still can be passed by. Particularly since at some point, MS drop-kicked the concept of backwards compatibility out into the trash heap. (And has done it again with Windows 11, which won't run on every machine.)

Yes, you are absolutely correct. And to be honest, I quoted Joel Spolsky out of context, naughty me. Still I've found his writing on software development fascinating, including that article about Netscape's rewrite (this forum won't let me post hyperlinks).

 

[Sun_Force]

Keep in mind that we are copying ONE file and it is rarely a large one at that so I'm not sure you would even see a progress meter but thanks for reminding me.

I do see it. The FE is a huge one and it's being updated from a domain controller to each client.

 

[isladogs]

I've never liked APIs. Mostly because I don't understand them and I can't write them. I don't feel comfortable copying and using something that I don't understand.
I leave it to pros & geniuses like you or others. I prefer to keep my limits and work with a bat that I can understand and modify to my needs.

I also assume you need to run the API from a strater. It's another thing I can't get used to. I think it's two step more than what's needed.
Launch starter. Launch your app. Close starter.

And since each step takes a little while, why should I go the longer route when there's a shorter one?

And I really don't find any privileges using a starter for updating FE rather than using a bat.

All valid points but the advantages are significant.
1. Applying the API to your code is VERY simple. Basically its 'plug & play'
2. In my opinion, it looks better than using a command window. Users will be familiar with the Windows file copy dialog & progress bar
3. The progress bar still runs even if only one file is copied as it works on % of total file size copied to date (not the number of files copied)
4. File copying is VERY fast. Typically my application upgrade folder contains a number of files and in some cases can be 130 MB or more in total:

The API checks and only copies files which are newer (unless a forced update is used - in which case ALL files are copied).
Even if all files are downloaded, it takes just a few seconds
5. After updating files, the starter app closes and the main app is opened - both happen automatically

Here is a short (17 sec) unedited video of this being used in real time:

 

[Sun_Force]

The download is still only ever one file. It is not a bulk file transfer.

Not in my case. I have to copy a whole folder. The FE for sure, a set of icons, images, logos that are used in forms/reports, plus a handful set of excel files.
If I change the structure of a query, the pivot tables and graphs in several Excel files should be updated. Or if I add/change a logo in a report, the image should be downloaded to the client personal folder.
I can not run the risk of when a user tries to show a graph in a meeting, facing some errors or wrong results or a report can not be printed because the logo is different or doesn't exists.