Front End Distribution Using SQL Server as Back End

[magic_sponge]

Hi there - I hope I'm posting this in the right place.

I'm not an Access expert, but I know enough to get by. I have an Access FE which I use to collect data from five or six members of my company. We use Dropbox here, so I set it up using a SQL server backend, and then emailed copies of the FE to the individual users to save local copies.

This works ok, but I want to find a way that means I don't have to keep emailing them updates. We're constantly updating, tweaking, adding and I want to a) reduce the amount of email traffic and b) always know they're using the most up to date version.

I played around with the idea of saving the FE in a Dropbox folder which would then automatically 'save a copy' to their local machine so they never actually use the version saved on Dropbox, but it seems from looking around this isn't really how Access works.

Essentially, I'm just looking for a way to make sure the most recent version of the FE is used without using the email method if possible. Any suggestions?

Thank you in advance.

 

[KitaYama]

Search for "Update Front End".
There are a lot of ways.

Personally I prefer @isladogs 's method.

Update Front End

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

www.access-programmers.co.uk

And here is his sample file

Update Front End

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

www.access-programmers.co.uk

There's a back draw to using starter (for us). It opens multiple instances of the same database if the user double click the starter several time.
We had to add a piece of code to check if an instance is already opened or not.

There is also a solution to use a batch file.

 

[Ranman256]

I have a list of user IPs.
when I click a button, the code copies the new FE to their folders.

at another site, each user has a personal network folder to copy to.
Everyone gets updated.

 

[KitaYama]

I have a list of user IPs.
when I click a button, the code copies the new FE to their folders.

at another site, each user has a personal network folder to copy to.
Everyone gets updated.

Interesting. Just out of curisity...
Is it reliable?
You update your master FE and with a button it's copied to their folders. If a PC is down or not switched on, won't receive the update.
When the user turns on the PC, does the FE check for new version? What happens when it can't find the updated version because the PC was not on?

 

[theDBguy]

@magic_sponge Welcome to AWF!

Are all users connected to the same LAN? If so, can place the latest FE in a shared folder. You can then use a starter app (an Access db, a batch file, or a vb script) to download a copy of the master FE for the user every time they run the starter app.

 

[JMongi]

When the user turns on the PC, does the FE check for new version?

That's the more reliable way. Launching the application should usually check for the latest version of the FE as part of its launch parameters.

You can then use a starter app (an Access db, a batch file, or a vb script) to download a copy of the master FE for the user every time they run the starter app.

I wonder if it's possible to roll that code into the FE itself, running some code between closing the FE (triggered by the availability of a new FE version) and closing the access application. Using a starter script is probably easier, but it's got me curious.

 

[Gasman]

I used to use Bob Larsen's code.

Free Access Tools | BTAB Development

FRONT-END AUTO-UPDATE ENABLING TOOL (Works for all versions 2000 and up) Microsoft Access Front-End Auto-Update Enabling Tool - allows you to quickly add auto-updating to your front-ends (PLEASE NOTE: This tool is primarily meant for networked users that have direct access to the backend...

btabdevelopment.com

Vlad on here also has his own updater.

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

 

[magic_sponge]

@magic_sponge Welcome to AWF!

Are all users connected to the same LAN? If so, can place the latest FE in a shared folder. You can then use a starter app (an Access db, a batch file, or a vb script) to download a copy of the master FE for the user every time they run the starter app.

Thanks for the welcome!

The users are all remote users connected via WiFi. I'm not very clued up on this sort of thing. I was thinking of doing something similar in a Dropbox folder, but I couldn't work out how. But that's essentially exactly what I wanted to do.

 

[KitaYama]

That's the more reliable way. Launching the application should usually check for the latest version of the FE as part of its launch parameters.

Yes, I know it's reliable. The problem is according to @Ranman256 method, even if the FE checks for the new version, it can not download or use the new version. Because at the time of distribution the PC has been off line.

 

[theDBguy]

I wonder if it's possible to roll that code into the FE itself, running some code between closing the FE (triggered by the availability of a new FE version) and closing the access application. Using a starter script is probably easier, but it's got me curious.

Yes, that is possible. As @Gasman pointed out Bob's update utility does exactly that. The FE opens, checks if there's an update, creates a batch file, closes itself, runs the batch file, downloads the new FE, and opens the new FE.

 

[theDBguy]

Thanks for the welcome!

The users are all remote users connected via WiFi. I'm not very clued up on this sort of thing. I was thinking of doing something similar in a Dropbox folder, but I couldn't work out how. But that's essentially exactly what I wanted to do.

I don't think using Dropbox itself would work, because it's a two-way sync. Your copy of the latest FE in the cloud could get overwritten by an older copy from the user's machine.

If all your users are remote, the next best thing I could think of is to host the master FE in your own web file server. You can add code to the FE to check the online server for updates and download it to replace itself.

 

[JMongi]

I don't think using Dropbox itself would work, because it's a two-way sync. Your copy of the latest FE in the cloud could get overwritten by an older copy from the user's machine.

If all your users are remote, the next best thing I could think of is to host the master FE in your own web file server. You can add code to the FE to check the online server for updates and download it to replace itself.

I think (it's been awhile) you could use dropbox but not directly (for the sync reasons you stated). Dropbox would merely be the pipes to get the FE to the local user. You would have some hoops to jump through, but the idea would be to copy the file off of dropbox and manage it that way. In that case, there is no way an "older" version would get reversed sync to dropbox. You should be able to control that. But again, it's been awhile. I've shared files I in no way wanted the user to mess with on dropbox, but they could download a copy to their local machine and mess with it without affecting my dropbox file.

 

[JMongi]

Ok, I've seen a few of these threads cropping up recently (and many more in the past). There are always a wide variety of suggestions on methods and details. However, I've seen very few high level descriptions of the process. This high level description might assist those less familiar to be able to better anyalyze the various specific methods that are discussed and decide which ones best match their needs and network environment.

Definitions
Backend (BE) - The main table structure (schema) that stores the data in your database. Stored in ONE location accessible via a network.
Frontend (FE) - The forms, reports, etc that are used to input or retrieve data from the BE. The FE is stored in MANY locations locally on each users computer.

Goal
To distribute an updated FE to all users so that they use the correct FE. All users MUST be on the same version of the FE.

Basic Steps
1. FE Verification - This is a method of verifying that the FE the user is trying to open is the correct FE they should be using. Again, there are a variety of methods to accomplish this with varying pros/cons.

2. FE Delivery - This is a method of delivering an updated FE to local users. This could be an e-mail attachment, an e-mailed link to a network file, a drop box file, etc. There are definite pros and cons to each method of delivery. For example, a potential con to the e-mail attachment is difficulty ensuring that anything actually happens with the e-mailed file.

3. FE Update - This is a method to actually deal with the FE file on the user's local computer. One method is simply allowing the user to manually copy/paste the file to the appropriate local location.

Some methods described early may cover more than one of these steps. But, all methodologies combined must verify the correct FE, deliver the correct FE to the user, and then ensure the correct FE is placed appropriately on the users computer.

 

[JMongi]

Now we can ask some questions to @magic_sponge.

-You mention using SQL Server and these are remote workers across the internet. Where is the BE hosted?
-Since you don't have direct access to these user's computers, it might be difficult to implement some of these ideas. Do you have authorization to ensure things like scripts or batch files can be generated and run on these remote devices?

@Pat Hartman already mentioned it, but, one thing you absolutely need is some type of FE verification. Regardless of your method of FE delivery and updating, your application needs to verify it is running the correct version of itself. The rest (the delivery and updating) is a question of how automatic you can make it given your technical ability, user setup, etc. But, regardless of whether your FE delivery and update system is manual or automatic, you need to protect your BE from someone using the wrong FE.

Edit: Fixed Typo (Sorry Pat!)

 

[bastanu]

I think when you have MS SQL, Oracle, MySQL, PostgreSQL or similar backends the best approach would be to store the front-ends right in the backend itself. You could have a tblFrontEndVersions table with fields storing the version number, description of changes and a zipped version of the front-end stored as a BLOB. The launcher would connect to server check the latest available version and compare it to the existing local FE, use VBA to download the zip file, extract, replace....

I am working in upgrading my version to include this option, the code needed is widely available, I am surprised that I haven't seen something like this yet as it seems like it makes the most sense....

Cheers,