Access Front-end Auto-Updating Utility (1 Viewer)

boblarson · Jul 17, 2006

Here's a utility I created that will let you add auto-updating capability, with great ease, into an Access front-end.

It adds this functionality:

1. When the client opens their front-end, the code checks to see if the front-end is the latest version.

2. If the version numbers are different, then it will give the user a message saying that their front-end is out of date and it will now close and then reopen. It closes and then deletes the old database, copies the new front-end from the location set in the location table, and then reopens the front-end for the user.

There are instructions on how to use this utility in the utility itself. Click on the Instructions button to view them.

>>>REVISED 07/18/2006<<<<
I just had to upload a revised version as I found a letter missing in on VBCrLf in the code that gets pasted into the startup form. So, if you downloaded it before, please discard and re-download.

>>REVISED 04-09-2007<<<<
to download the latest version see the last post in this thread

Banana · Jul 17, 2006

Great! This is what I needed.

Thanks for making my life a bit more bearable!

boblarson · Jul 25, 2006

Instructions with screen shots

I am posting the instructions on how to use this tool, but this version of the instructions has screenshots to aid in understanding how to use the tool.

This is a zipped Word document.

*******************************
9/10/2006 IMPORTANT
*******************************

I found a typo in the document and have corrected it. It originally said that the 'tbl-version_master_location' was in the back-end, but it's really in the front-end.

**********************
12/22/2007

Removed documentation as latest is on my website: http://www.btabdevelopment.com
**********************

*Pete* · Jul 25, 2006

Fantastic!

This example is fantastic! It is a really useful tool.

Well Done

Regards

Pete

Oldsoftboss · Jul 25, 2006

Have used this with great success. But a word of warning. Be sure you have the correct path to the master file, else when you open it, it will check the version, check the path and delete the master db if the path is incorrect. (Yes that is what happened to me) But with my experience with access, of course I had a back up. Just a word of warning.

PS:
I made another form that allows you to easily change the version no, also added a date field, and a browse folder function to allow the Db admin to set the initial path to the master. Will post this addition and maybe Bob could add it to the download

Dave

Oldsoftboss · Jul 25, 2006

See if someone can tidy this up.

Dave

cdoyle · Jul 25, 2006

I like to use this, but did you say if the path to the master is wrong it will delete it?

What I'm afraid of is, lets say something is changed on our network and the path changes. ususally they dont' tell us these things, so if this happens and one of my users click on their icon on their desktop. The path will be wrong, and it will then delete the master DB?

Or did I read that wrong?

Oldsoftboss · Jul 25, 2006

If one of your users have a font end that needs upgrading and the path is wrong, it will delete THEIR front end and replace it with nothing.
I recommend always opening the master with the shift key down !

Dave

Banana · Jul 26, 2006

To clarify- did oldsoftboss's edits fixed the "delete FE" bug?

boblarson · Jul 26, 2006

Cool enhancements Oldsoftboss!

Oldsoftboss · Jul 26, 2006

Banana said:
To clarify- did oldsoftboss's edits fixed the "delete FE" bug?

Not so much a bug as a requirement for the Db admin to be sure the path to the master is set correctly before the initial distribution.

Dave

Banana · Aug 15, 2006

Ok- I have to get off my sorry ass and stop procrastinating.

Now, when I read the documentation, I'm not sure if this is good idea. Want to run it by you-

My scenario is that I have three remote offices who will use database. Common sense dictates that the FE master goes to a place that is common to all three office and that's on the main server accessible via VPN. I can make sure to add a messagebox to remind my users that they need to be connected in order to check for updates.

However, each office has their own local back-end, for security and performance reasons. Since they all use essentially same front-end, I'm thinking I'd just make one mini-BE that contains the version information in the same folder with master FE which I can replace with periodically, and link all FE to this mini-BE along with their local BE.

If there are problems, I'm open to other suggestions.

TIA.

Banana · Aug 16, 2006

After thinking it though, it became apparent to me that the utility "assumes" there's only one back-end which could be problematic for my users who may not use the same back-end. Therefore, I'm thinking that I'd select the beforementioned "mini-BE" to check the version, point to master FE in a common space, then add codes to open filedialog to ask users to locate their local back-end to refresh the links.

Will that work? I've never really worked outside of Access so am wary about making mistakes with creating/deleting/finding files in Windows.

Adeptus · Aug 17, 2006

Banana,
Here's my suggestion...

At the remote sites, put the back-end in the same mapped drive
eg,
at Location 1, have a network mapped drive Z: that points to Server1\SharedFolder
at Location 2, have a network mapped drive Z: that points to Server2\SharedFolder
at Location 3, have a network mapped drive Z: that points to Server3\SharedFolder

Set up the linked tables to look at this drive letter, not a UNC path (eg \\Server1\SharedFolder\)

Then you can just copy the new frontend in & it will always be pointing to the right data.

Also, it should be no problem having the version-tracking tables on a different back-end to the actual data.
(for the version-tracking BE location, you could use either mapped drive letter or UNC)

Banana · Aug 17, 2006

Just one question:

Didn't I read that it's generally preferable to use UNC instead of absolute address?

I guess what you're proposing is that by creating same path (relatively from the end user's viewpoint), there'll be no problem locating the back end?

Adeptus · Aug 18, 2006

Banana said:
Just one question:

Didn't I read that it's generally preferable to use UNC instead of absolute address?

Generally, I guess so... that way if a drive isn't mapped (or is mapped differently) it will still work.

I guess what you're proposing is that by creating same path (relatively from the end user's viewpoint), there'll be no problem locating the back end?

Yes, that's what I was thinking

steve711 · Sep 8, 2006

Thanks for this utility.

However I am going crazy as I cannot get it to work for me.

I have followed the instructions, as far as I can see, to the letter.

When I open the "old" FE it happily informs me I have the old version it then closes and then does nothing more.

Looking into the folder I see the old FE is still there and your batch file.

Here is my test set-up.

Papillonsheet_be.mdb located in c:\Temp
Papillonsheet_master.mdb located in c:\Temp

The test version of my user front end is:
Papillonsheet.mdb and is located in c:\FlightDuty

In your instructions you make this reference:
"In your back-end, find the table labeled 'tbl-version_master_location' and change the location to the correct location that the front-end master is located. Do NOT put a backslash at the end of the file location."

When I execute your application it is not creating a tbl-version_master_location in my BE it is creating that in my FE master.

So I added this table to the BE with no luck.

Sorry for being long winded but I would love to get this working.

steve711 · Sep 18, 2006

Ok am I the only one who cannot get this to work?

I have followed the directions to the tee with no luck. The only thing it does is create the batch file. It closes my "old" FE and then that's it nothing else. It doesn't actually copy the new one over.

crich21 · Sep 20, 2006

Why me

Has anyone used this method and run into this problem?
I have to open my db, get the message that it needs updated, click ok, then the program closes and never reopens. I have to open the db and go thru this process several times before it finally completes the update like it should.
any suggestions????

crich21 · Sep 25, 2006

I had trouble to

I had the same problem. I had to modify the batch file code. Apparently my db wasn't closing before the batch file tried to delete it so I changed the code to this.

Code:

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, ""
[COLOR="Red"]Print #1, "ping 1.1.1.1 -n 1 -w 2000"[/COLOR]
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
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

This line is the only thing I could find to slow down the batch file and give access more time to close. The 2000 at the end of the red line of code can be changed to a higher number if more time is needed. Hope this helps.

Access Front-end Auto-Updating Utility (1 Viewer)

Smeghead

split with a cherry atop.

Smeghead

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

split with a cherry atop.

Smeghead

AWF VIP

split with a cherry atop.

split with a cherry atop.

What's this button do?

split with a cherry atop.

What's this button do?

Registered User.

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread