Access 2003 - How best to push front end updates?

[Khriskin]

As a warning, I'm self-taught so I have a bad habit of brute-forcing things out of ignorance. I've done some searching online and haven't found a solution, so I figured it couldn't hurt to ask you kind folks if there was a better alternative.

PROBLEM
I have multiple front ends that are updated on an almost daily basis as needs change (or I find problems). I need an easy way to push out new local copies of the front ends to multiple computers. Asking the users to replace the database themselves is highly problematic as there are very few computer savvy people on the production floor.

MY IDEA
I was going to create a PushTo access database (2003) that would store a list of the front ends, and a list of the locations/directories where the programs are stored. The program would then cycle through the locations for the program selected by the user, deleting the old version and copying the newer one over from the master folder. Thus I could trigger an update whenever I had a new version ready to go.

The alternate thought was to put code inside the front ends that would check on startup to see if it was current or not. But I'm not sure if there is a way to trigger a self-delete/replacement while the program is active.

QUESTION
Is there any easier way to do this? Am I going about this completely backwards?

Thanks!

[Denburt]

Generally speaking I ALWAYS work off my hard drive, then when ready I update the servers and let the users do the rest. It is bad enough that a user entering data can suffer a corrupt database from using it across the network but if it happens during the design phase it really sucks.

Following the direction from the big M (Microsoft) I only took a paragraph from the following article since it has relevance this post:
http://support.microsoft.com/kb/889588

"Configure all database front-end computers to maintain an open connection to the back-end database files. To do this, create a table in the back-end database file that contains one text field and one record. For example, create a table that has the following configuration:
Table Name: tblConnect
Field Name: Field1
Data type: Text
First record: “Connection”

Link this table to your front-end database and create a form that is based on the table. Open the database by using a hidden form with your startup routine. Your startup routine can be put in a macro or in a startup form, as in the following example:

DoCmd.OpenForm " tblConnect",acN ormal ,,,,acHidden

Alternatively, you can open a recordset that is based on this table. The recordset variable has to be declared in a global declaration section of a module. It also has to be closed when you exit the front-end database."

The relevance is that I use the Main menu recordsource to keep my linked version table open, only one record and one field. I enable the version field only for myself using an active directory function but you can use something like
If "denburt"= Environ("USERNA ME") then
me!vesionField. enabled = true
I store a local front end and use a separate back end for each divisions local server.
So when I update the linked table from that field I have it automatically update the local "versionFrm " table in that database then have it copied to a list of servers stored in a table in the database and update each "Version" table respectively, then with the clients portion you are rolling things out with little effort on your part.


Note: The following is incomplete and is a work in progress for one it fails to convert the front end to an mde file before copying it to the servers. It also has a function for relinking each database once it is copied over etc.

Public Sub Version_AfterUpdate()
Dim db As Database, Servdb As Database
Dim strVer As String
Dim rs As Recordset, rs1 As Recordset, ServRs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Version")
strVer = rs!VersionNo
rs.Close
    Set db = CurrentDb
    Set rs = db.OpenRecordset("ServerList")
    If Not rs.EOF And rs.RecordCount > 1 Then
        Do Until rs.EOF
            Set rs1 = db.OpenRecordset("ServerInfo")
                rs1.Edit
                If rs1!ServerPath <> rs!ServerPath Then
                    rs1!ServerPath = rs!ServerPath
                End If
                rs1.Update
                rs1.Close
                RlnkTbl False
        CopyFile Application.CurrentProject.FullName, rs!ServerPath & Application.CurrentProject.Name, False
DoCmd.RunSQL "UPDATE Version IN '" & MyPath & "\" & Left(BkEnd, Len(BkEnd) - 4) & "\" & BkEnd & "' SET Version.VersionNo = '" & strVer & "'"
        rs.MoveNext
        Loop
    rs.Close
    End If
End If
Set rs = Nothing
Set db = Nothing
End Sub