Updating an access application by distribution to users

[JohnGo]

Access offers a great way to update single objects.
But as most users don't know how to import new objects or shouldn't be hold responsible for something like that I was considering a 'packaging' tool.
Before programming it myself I guess there are off-the shelf programs for easy updating access applications.
I was thinking about an application which makes it possible to tell which objects to delete and what objects should be transferred into the existing database. In this way the package will be kept tiny and it would be nice if it's executable like a setup program.

Somebody ever used/noticed such a tool?

 

[bradcccs]

Dave (Oldsoftboss) posted some good code to facilitate similar tasks here:

http://www.access-programmers.co.uk/forums/showthread.php?t=75795

 

[jimbrooking]

Updating Access

I recently went through this issue, and saw Dave's solution as well as a couple of commercial packages. In my case they all had a fatal flaw: somehow you had to be sure you remembered - somehow - to get every last object you changed into the update database. I am, I guess, undisciplined, but when I'm working on something, I often have several objects open, and maybe change one or more of them a little, and forget to log it, so stuff doesn't always get into the update pile. I guess an end-to-end Q/A cycle would find those, but like the proverbial one-armed paper-hanger in a hurricane, sometimes there's just too much going on.

Finally I decided to just redistribute the entire front end (of the split database). My manually split back-end database has only the user-changable tables in it. All the system management tables stay in the front end. And a new "table of tables" tells which tables are for users, so may not be updated, and which are system tables. My DB has code in it to split the user tables out of the DB, and to join the DB, in case anyone needs to do that. Oh, and one other useful piece of code is that which re-initializes all the user-changable, as well as the system tables to their out-of-the-box settings. The "table of tables" mentioned above makes that easy to do.

Anyway, for what it's worth, instead of fooling around with individual DB objects, my experience strongly suggests that just distributing the whole front-end is the answer to maintenance issue.

HTH,
Jim

 

[Fizzio]

I have to agree with Jim.

Problems with creating and deleting objects, whether dynamically or not will always create bloat (despite compact and repair) and has a strong potential for corruption!

I go with jim here. How I update is to create a setup file using the excellent freeware Inno Setup. This will unpack the componenets I want to install into the directory of mine (or the users choice) - I usually install to the BackEnd directory on the network.
I then launch the new central frontend which automatically refreshes all the table links and self closes.

When the user starts their local copy, they are given the option to update their local copy which is then pulled from the server onto the local drive and self restarts.

This allows me to keep the Front End as compact as necessary and tweak things in a little more detail.

The only time I use code in one of these distributions is if I am changing the data structure in the BE - creating tables and fields.

 

[JohnGo]

Thanks for the advice. As Í'm struggling with updating issues just sending the whole front-end might be easiest thing to do. Guess the query/form/macro code won't take much space.
Very soon I'll be using attached tables making it easy to update the front-end.

Fizzio, thanks about the Inno Setup application hint.

 

[bradcccs]

Weighing back in...

We all seem to be singing the same tune here...

Dave's solutions are for manipulating a Back End with a newly distributed Front End.

Distributing a brand new front end = Easy = Happy Customer

Distributing a "new" backend = Disaster = Phone call from solicitor

 

[GabrielR]

well I use a Simple routine that executes a batch file whenever the system tables in my "Update" front end differs from the system table in the back end. I Compare these two Using a simple query.

I have had no problem so far with 20MB Plus databases that excede the 20 Users.

Dont know if there is better option. If you guys want to I can share the code, giving credit to the person that wrote it in the first place.

Gabriel

 

[jimbrooking]

I think this might be useful to see what's different between two databases, esp. if it looks at queries, forms, reports, etc. So if you'd post the code so we could have a look....

Thanks,
Jim

 

[GabrielR]

The basic principle is this:

A DB has a table that store the DB objects names and time of last update. If I compare this table with a table on another database I can extract the differences and triger an object to appear ( in this case a button and a label prompting to update) for the user to interact. Clicking this button will execute a batch file that copies the Update DB to the local folder where the User's Database reside and overwrite. That way I keep all my databases in Sync.

This is how I manage the front ends on my DBs

I have an "Offline Database" one that Compares its System Objects table to itself... So It will never request for an Update. Once I make a change and I am happy with it I just export the new or altered object to the "On line Database (Update)" and the next time the Users open their front end they are prompted to update.... Works like a charm.

I use a custom message like: There is an update ready to install, please click the button" My Users are quite impressed by it actually.

Here is the code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OpenForm
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryUpdateSystem")

If Not rs.BOF Then
    Me!cmdUpdate.Visible = True
    Me!lblUpdateNeeded.Visible = True
Else
    Me!cmdUpdate.Visible = False
    Me!lblUpdateNeeded.Visible = False
End If
Exit_OpenForm:
    Exit Sub

Err_OpenForm:
    If Err.Number = 3024 Then
        MsgBox Err.Description & vbCrLf & vbCrLf & "Please Re-link Tables and re-open FrmSwitchboard"
        DoCmd.Close
    Else
        MsgBox Err.Description
    End If
    Resume Exit_OpenForm
End Sub

When the User clicks on the Button to Update:

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click


    runBatchFile
    DoCmd.Quit

Exit_cmdUpdate_Click:
    Exit Sub

Err_cmdUpdate_Click:
    MsgBox Err.Description
    Resume Exit_cmdUpdate_Click
    
End Sub

and the code to run the Batch File:

Sub runBatchFile()
Dim db As Database
Dim BatchFileName As String
Dim strCommand As String
Dim htask As Variant
Dim FileName As String, BackFile As String
Dim tdf As TableDef
Dim MasterFile As String
Dim Response As Integer

Set db = CurrentDb
Set tdf = db.TableDefs("RemoteSystemObjects")
MasterFile = Mid(tdf.Connect, 11)
'MsgBox MasterFile

FileName = db.Name
'MsgBox FileName
BatchFileName = "c:\Batfile.bat"

Open BatchFileName For Output As 2
strCommand = "copy """ & MasterFile & """ """ & FileName & """"
Print #2, strCommand
Close  '*** Batfile.bat

'MsgBox strCommand
Response = MsgBox("You are about to update this Application.  It will close to download and install the latest update. Are you sure you want to continue?", vbYesNo)
If Response = 6 Then
    htask = Shell(BatchFileName, 0) 'Run the  Batch File
End If
End Sub

This code has the following Tag at the begining (For Credit Purposes)

Option Compare Database
Option Explicit
'**********************************
'Created by Roger Carlson         *
'Roger.Carlson@spectrum-health.org*
'Rog3erc@aol.com                  *
'**********************************

Please see this example from the original source.

Roger's Access Library

Gabriel Ruiz

 

[jimbrooking]

Thanks GabrielR (And Roger Carlson)!

Jim