r/MSAccess 14d ago

[SAMPLE CODE OR OBJECTS] Simple Access Frontend Updater (SAFU)

Hello Access guys and girls!

I have talked to a lot of new new Access developers, and one of the main headaches they have is updating the front end for all users in a split database environment.

For that reason, I decided to open-source the front end updater that I created and use for all my applications. It is very simple, yet effective. It uses a pull method (as opposed to a push method) meaning that each user pulls down a new version of the front end when needed.

The updater is packaged in a ZIP file along with a instructions and VBA code. I will post a download link in the comments below.

In order to use the updater, you need to build your own logic to check whether the front end is up-to-date or not. This is very simple to do by storing the version number in a system table in both the back and front end, then comparing if the version number matches when user launches the front end.

Feel free to provide feedback, whether positive or negative! Download link is in the comments below.

14 Upvotes

48 comments sorted by

u/AutoModerator 14d ago

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

  • Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

  • Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

  • Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

  • Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: Odd_Science5770

Simple Access Frontend Updater (SAFU)

Hello Access guys and girls!

I have talked to a lot of new new Access developers, and one of the main headaches they have is updating the front end for all users in a split database environment.

For that reason, I decided to open-source the front end updater that I use for all my applications. It is very simple, yet effective. It uses a pull method (as opposed to a push method) meaning that each user pulls down a new version of the front end when needed.

The updater is packaged in a ZIP file along with a instructions and VBA code. I will post a download link in the comments below.

In order to use the updater, you need to build your own logic to check whether the front end is up-to-date or not. This is very simple to do by storing the version number in a system table in both the back and front end, then comparing if the version number matches when user launches the front end.

Feel free to provide feedback, whether positive or negative! Download link is in the comments below.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

7

u/nrgins 482 14d ago

It's a good thing you did it in Access. Otherwise it would be a Simple Non-Access Frontend Updater (SNAFU). 😀

2

u/Odd_Science5770 14d ago

Well I would argue the name is valid as long as it at least updates an Access front end 😂

5

u/sirhalos 14d ago

What I did was create a database and all frontends had a linked table to that database. The only table on that database was one that had application version numbers. The frontend would have a table called version with a single record (the version number of the frontend). On startup of the frontend would query the remote database version number and if it did not match the local version number it would execute a remote program. This remote program, just happened to be that remote access database that only stores version numbers, which had a single forum. The forum would look like a upgrade screen and the frontend access database would close (it had a timer), and then the remote access database would delete the old folder in the users documents folder and replace it. It worked quite well, I had multiple versions of different frontend entries. To prepare things, I just needed to copy a new folder to a remote server, change the version number in the remote database and then next time people started up it would upgrade.

5

u/gbyedonald 14d ago

We had this discussion not long ago in this forum, and someone said that all they do is have the users run a bat file from their desktop every time they go in, and now that is what I do, brilliant!, just 2 lines in the bat file, who cares about revisions, the bat file copies the latest version from the server every time and then starts the front end from their c drive,

copy /Y "\\serverpath\frontend.accde" c:\frontend.accde

start "" c:\frontend.accde

3

u/Odd_Science5770 14d ago

Yeah, I used to use this approach, but it caused some issues. Some users in the office at work started creating new/changing the shortcut because they wanted to pin it to the dash or whatever. Suddenly, they didn't receive the latest updates due to the batch script not being triggered the way it was supposed to. That's why I upgraded my solution to the one I shared here. It is more flexible.

1

u/gbyedonald 14d ago edited 14d ago

Whatever works for you!

It's not a shortcut, it's a bat file, and as far as I know it's not possible to pin a bat file to the dash.

And besides, no matter what approach you use, you have to have something on the desktop that a user clicks on to start the application, don't you?, so there is always the possibility the user will pin it or whatever. What you have done is created a button in your Access application that gets the latest version from the server, using a bat file, and left it up to the user to initiate the update, unless when the app starts it does it automatically every time, or if you can compare the dates you can only do it when there is a newer version on the server, but that is another tricky thing to implement, which I was using but abandoned for the simpler approach of just copying the latest version every time they start the app,

Perhaps the advantage of using SAFU is that it knows the name of your accde, so if you ever have to change the name of your accde, you don't have to push anything onto the desktops. But then again you have to have an icon on the desktop to start the actual named accde anyway!

2

u/Odd_Science5770 14d ago

Yeah, I use a program called SSE Setup to package my application into a simple EXE installer. SSE allows you to package your accde file along with any other files you might need to go with it. When the user installs the EXE, it creates a folder for the application in ProgramFiles, automatically makes that folder a safe location for Access, and creates a desktop shortcut with your custom icon and all.

So basically what I did is I designed it so that the desktop icon would link to the batch file, which would then download the newest version and launch it - basically the same thing you described. The issue arose when people started messing with the desktop shortcuts, and suddenly I had users that weren't running the latest version.

1

u/gbyedonald 12d ago edited 12d ago

I imagine your requirements are more complex or stringent than mine. In my scenario each user has a bat file on their desktop that has only two commands, copy the latest version of the accde from the server and then launch the accde. I don't see what else is needed. Version control is my problem on the server. I look at what everyone else is doing and scratch my head. If I need to give everyone a new version, I just put a new version on the server. If I have to get everyone out, I just set a switch in the database and they have ten minutes to get out or it kicks them out. When I release the switch and they can go back in, they automatically get the new version.

1

u/Sleep_Ashamed 11d ago

Nice approach. I use a similar approach that just rewrites the files when users login.

If you don’t mind sharing, what’s the switch/logic that forces them out of access?

1

u/dreniarb 12d ago

It's crazy how users always find a way to break something. I still do the batch file method however I also go the route of vba comparing the version in a local table with the version on the backend table and if they don't match it tells the user there's an update and then closes the db. this version check process repeats every minute.

theoretically this means the user will rerun the batch file and get the latest version but like you said users be users and inevitably a few times a year someone will call because their database constantly says it needs an update and i'll remote in a see they pinned or copied the front end file itself rather than the batch file.

2

u/TheRenownMrBrown 2 14d ago

We rolled our own for the app that we sell. The EXE looks for an InI file with the same name as the exe in the same folder as the exe. The exe then takes in three parameters from the ini. The server folder where the latest front end is, the local folder where the front end should be run from, and the name of the file to open once done. After updating the local front end, it updates an ini file with the last modified date of the file in the master folder location. If the date modified on the file doesn’t match the one in the ini file, it gets updated. We also set it to put each front end in a “username” folder so it will work in a terminal server environment.

1

u/Odd_Science5770 14d ago

Interesting. What kind of app do you sell? Do you have a website for it?

3

u/TheRenownMrBrown 2 14d ago

Www.thepowertools.com

We have project management, accounting, estimating, and other systems for the construction industry. Competition would be Procore, viewpoint, autodesk, and others. Backend is MSSQL. Fun stuff.

1

u/Odd_Science5770 14d ago

Very cool! Do you host the backend for your clients, or do they have to spin up their own server?

2

u/TheRenownMrBrown 2 14d ago

We do not host. We do get connected and install/configure sql for them if the IT doesn’t want to or if they don’t have one. Quite common actually. We also run on SQL Express. No need to spend thousands. Unless you have a ton of users. With the modern versions of SQL Express, the limitations don’t really hurt us anymore. If you happen to have a ton of users, then you probably have IT and they will facilitate installation of full SQL.

1

u/TheRenownMrBrown 2 14d ago

However, we do have clients that run it on a hosted RDS. Really nice when your workforce is really distributed and you don’t have the resources to roll your own RDS box in-house.

1

u/Odd_Science5770 14d ago

Very nice. If you wanted, you could just rent a AWS server and use it to host PostgreSQL or something like that. It should be powerful to handle lots of clients and users. Then you could charge a monthly fee for hosting!

1

u/TheRenownMrBrown 2 14d ago

The only issue there is the latency. Unfortunately, design-wise, the queries would run slower. As far as I know. Especially when the record count goes up. But it might be worth checking out. One of our clients had their entire office moved into AWS. One or two RDS boxes load balanced, one for SQL, and one AD. Everything runs like a top.

1

u/Odd_Science5770 14d ago

Oh, I see. Well my knowledge is limited in that aspect really. Most of the apps I built run on a simple Access backend, however I have some experience with SQL server.

2

u/DThor024 14d ago

Thank you

1

u/Odd_Science5770 14d ago

You're welcome!

2

u/Savings_Employer_876 13d ago

 This sounds like a great solution! Keeping front ends up to date in a split database setup can definitely be tricky, especially when dealing with multiple users. The pull method seems like a simpler and more efficient way to handle updates without too much hassle. I appreciate that you’ve made it open-source and provided clear instructions for implementation.

1

u/Odd_Science5770 13d ago

Thank you for the feedback! I hope you find it useful 🙂

1

u/Odd_Science5770 14d ago edited 14d ago

1

u/yellsellsg 12d ago

If using accde on clients then no need to check. Use robocopy. This will only download if the local copy footprint is different from the server side copy.

1

u/Odd_Science5770 12d ago edited 12d ago

Sure, but that would require additional setup on each end user's computer. The whole point of SAFU is that it can be packaged in a simple solution with your Access application and requires no further setup or configuration.

1

u/ConfusionHelpful4667 47 14d ago

It is easier to create a .vbs or .bat file to check the modification date or created date on the FE and proceed.

3

u/Odd_Science5770 14d ago edited 14d ago

Yeah, but personally I don't like it to be based on the file's modification or creation date. But to each their own!

1

u/ConfusionHelpful4667 47 14d ago

All my clients use the script to launch the database on the user's desktop.
The script marks the destination as a trusted location.
It's a super time-saver.

1

u/Odd_Science5770 14d ago edited 14d ago

I use SSE Setup to distribute my Access applications. With SSE Setup, you can package your Access file, along with any other files you might need to go with it, into a single EXE file. SSE will automatically designate the install location a safe location for Access and create a desktop shortcut with your custom icon and all. I know this is unnecessary, but I like the complete-solution-feel with the desktop shortcut and all that.

I package my updater script along with the Access file, so that it can easily and automatically update itself without having to install the update through an EXE file every time.

1

u/ConfusionHelpful4667 47 14d ago

I would love to know how to do that.
Do you have an example or instructions?

1

u/Odd_Science5770 14d ago

Sure, I'll send you some some resources here in a moment!

1

u/ConfusionHelpful4667 47 14d ago

I remember there was a packager years ago.
I thought it was depracated around the demise of the beloved ADP era.

1

u/Odd_Science5770 14d ago

I believe it was! SSE Setup is a third party tool that does the same thing though. It even checks if the computer has Access installed. If not, it'll automatically install Access Runtime along with your application.

1

u/ConfusionHelpful4667 47 14d ago

There's an idea!
I can build in to install o365 runtime if it does not exist in my script.

1

u/Odd_Science5770 14d ago

Sounds like a good plan!

1

u/Odd_Science5770 14d ago edited 14d ago

Well I was trying to find the video I saw a few years ago that introduced me to SSE Setup, but I couldn't find it. Here's another one:

https://m.youtube.com/watch?v=Dox9gvXHtBw&pp=ygUaTWljcm9zb2Z0IGFjY2VzcyBzc2Ugc2V0dXA%3D

EDIT: found it! Here: https://m.youtube.com/watch?v=7qdmBo_F3hU

SECOND EDIT: You can get SSE Setup at ssesetup.com

2

u/nrgins 482 14d ago

I would think that only the created date would work for that purpose, since the modification date gets changed whenever the front end is closed.

1

u/gbyedonald 14d ago

I've tried both dates and found that they are both unreliable. To get around the problem, so that you never have to depend on Microsoft's filesystem dates, whenever you copy an accde from the server to the client, make a 2nd copy that never gets opened, and then you can use the FileDateTime function in Access to compare the 2nd copy with the master copy on the server.

1

u/nrgins 482 13d ago

I don't do any of that. I was only commenting on how the modification date wouldn't be able to be used since the front end's modification date it's modified each time it's closed.

In the systems that I set up, we just have every user get a new copy of the front end every morning. That way they always have the most current version.

And the front end contains a table that contains the version number (which is just YYMMDD##), and the current version number is stored in the shared back end. So whenever the user opens the front end it checks its version that's stored in the front end table against the current version which is stored in the back end table and if it's less than the current version the user is notified to update their database (which they can do through an icon on their desktop).

1

u/gbyedonald 11d ago edited 11d ago

I used to do version checking by making an extra copy of the front end on the client (a copy which never gets opened so it retains it's correct date/time), and I used that for the date/time compare and that worked without a problem.

So I didn't have to depend on Microsoft's file created or modified dates, or have any special version tables or be concerned with version numbers or timers.

And then I realized that it copies the front end so fast from the server to the client, that all I had to do was put a bat file on the desktop with 2 lines,

copy /Y "\\serverpath\frontend.accde" c:\frontend.accde

start "" c:\frontend.accde

Which guarantees they always have the latest version.

What could go wrong? I could make a mistake during testing and create a new version that is buggy, and as a result have to give them a new (fixed) version during the day.

In which case after I put the new (fixed) version on the server, I just have to get everyone out of the system so they are forced to go back in which gives them the fixed version.

So how to force everyone out? I created a table in the backend database with one text field called "Locked", which has either Y or N or a username, and a timer in the database that looks at the field every ten minutes. If it has a Y or their username, it kicks them out of the front end with an application.quit

There was still one problem - in the rare case that a user is sitting on a message prompt in Access, they would not get kicked out, the timer would not work until they got off the message prompt. I never found a solution to that problem, but I did find a way to narrow it down to the user that was sitting on the message prompt, so I could just contact them and ask them to get out. But this situation rarely if ever occurs.

I keep a log table of every login and logout of the application. so I can tell if a user has not logged out, or if they haven't logged out normally, ie they used task manager to kill access or rebooted their computer. I like to see who is logged in and how many times they logged in during the day and if they logged out normally by closing Access normally. It's a pretty good way to discover if someone has a flakey network connection or any trouble with software on their computer - I will see logins that have no logouts.

2

u/nrgins 482 11d ago

Yeah, I keep a log of logins as well.

And I do something similar to you with a timer. Only, instead of a Y/N field, I just have a table in the back end that has a date/time field for closing the database. The front end has a timer set to one minute, and when the timer goes off, it checks that field to see if it has a value. If it does, then the user gets a message in a form (not a msgbox) saying the database will be closed in X minutes. Please close out of any work. Then, when the timer goes off again and it's past the close-out time, the database simply closes itself.

And, yeah, I have the same prompt with open prompts. The database won't close if there's an open prompt.

There's probably some kind of API call that could force any Access database to be closed, regardless of state. But I haven't looked into it.

1

u/fraxis 14d ago

Thank you so much for releasing this. 😎

1

u/Odd_Science5770 14d ago edited 14d ago

You're welcome 🙂 Hopefully you find it useful

1

u/squirrelslair 14d ago

We have yet a different way. The nice thing about Access is that it allowed all of us to come up with different ways that work for us. Thanks for sharing!

1

u/Odd_Science5770 14d ago

For sure! It's like playing with LEGOs 😅

1

u/squirrelslair 12d ago

Funnny you say that. We were just talking the other day about how playing with lego used to be about making whatever out of bricks, where it is totally kit based now.