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!

[Minion]

It sounds like you have a couple good ideas there, but let me ask a couple things.

1. Are all the FE's identical?

2. Do all the users have access to a networked location that you control?

The reason I ask is this... If the number of users is limited they should be able to share a front end. Baring this it is generally easier to alter a stored location that you control than trying to access a user's machine. If all the users were hooked to one location you could store the front ends on the server and they would link via shortcuts on their desktops. Then you would need only replace the FE's on the server and the user would be blissfully unaware.

There are several ways to do what you are asking, but it'll take some further understanding to find the best method so bear with me.

Hope this helps.

- Minion -

[Khriskin]

1. Are all the FE's identical?

2. Do all the users have access to a networked location that you control?

This is where it gets a little complicated, and the point where I wish I had a whiteboard to doodle on. ^_^;; *sheepish look* (baa!)

1. The back ends are all stored in one folder that is accessible to everyone. (\\Fileserver\D atabases)

2. The front ends (master copies) are in another folder that is also accessible to everyone, but which is read-only to everyone but me. (\\Fileserver\D atabases\Active Copies of UI) This stops folks from using the master copy, as I need to get in an work on them on a more or less regular basis. *sighs* This also lets them replace the local copy if it gets corrupted (something I'm still teaching the Supervisors how to do. I really need to automate it at some point...)

3. There are 10 back ends and 22 front ends, not counting the front ends that haven't hit beta yet. Each of these front ends does something unique, from running custom reports for the various departments and supervisors, to recording a different data set at each manufacturing station. Such fun. However there are several of these databases that run on more than one computer (such as the Supervisor's Production Database) and those are the ones I need to force updates on.

3a. There are fifty some-odd users, thirty some-odd computers. The computers are not always turned on (sigh) and thus I probably need to code in something that remembers what it has updated and what it hasn't. Hrmm.. *makes notes*

4. Why local copies instead of shortcuts? They kept corrupting when multiple people used the same front end. Plus the UIs need to be running 24/5 (for production) but only need to link to the back ends when actively in use (5% of the time). Since the bulk of the users are never active at the same time, it's easer to break down the back ends accessing to each user. Thus everyone has a local copy that can pick up and drop connections to the back ends as needed. (Only active tables are linked, static ones have local copies that are programmaticall y sync'd)

5. Users are sneaky and keep ferreting away copies of the UIs to locations I don't know about. (I run a Search on the network every so often to try and kill them off.) So I probably need to include a validation code in the UIs themselves, just to keep folks from using the old versions.

Too much info? Not enough?

[jaxjagfan]

Just a thought ...

I had a single application which users logged into. I did a lookup on the backend user table to see what group the user belonged to. The forms and menus were all driven by the user group. Some forms and capabilitlies were shared across groups and a group I called "reporters" were only able to view and run reports.

I had case statements which may open a form read-only for one group and full access for another. The case statements would enable/disable functions as well.

[Khriskin]

I had case statements which may open a form read-only for one group and full access for another. The case statements would enable/disable functions as well.

This is something I have implemented for several of the databases, it filters by user and access level as to what they see and what they can edit. The problem with combining them into one 'Evil Overlord' database would be it would be a pain in the neck to document clearly enough that if I got hit by a bus the company wouldn't suffer.

I've got an Inventory Database, a Production/WIP Database, a Sales/Forecasting database, a Scheduling Database, etc. All of them talk to each other, but each of them has a host of unrelated tables, forms, and queries. There is a three-ring binder for each, with full documentation. Plus it's much easier to troubleshoot fifty-odd pages of code than it is 500. ^_~ *grin*

[PianoMan64]

The best way that I've dealt with that very issue is to create a batch file that they use to run the install/Update batch file. This way they can get the most current published version of the application that you place in a folder that is used for published versions of the different applications.

Call "R:\CorpAdmin\Records And Imaging\CSI\Tally Sheets\New User Interface\Install4.BAT"
"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "c:\BatchTally\BatchTallyV4.mde"/wrkgrp "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\StandardADPA.MDW"

and the Install4.bat has the following in it.

c:
cd\
mkDir BatchTally
CD\BatchTally
Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\BatchTallyV4.MDE"
Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\*.bat" "c:\BatchTally"
Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\*.Lnk" "c:\Documents and Settings\%UserName%\Desktop"

Hope that helps,

Joe P.

[NeoPa]

I remember another Expert telling me of his solution for this, so I PMed him a link. Let's see if he can throw in something interesting ;)

[Denburt]

I know I am late on my posts a lot lately.

First I don't push them out since I don't have the necessary IT privileges etc.So what I do is this.

I created a routine which could definitely use some work and it is a little brutal or crude in some aspects but it serves it's purpose and it could/should help. Works great for us on quite a number of databases. A lot of this design is similar to some of your ideas and I completely understand the self taught approach so please feel free to ask as many questions you would like. I don't want it to just "work" I would rather you understand the code why it is there and what it is doing.

I start out with two tables one on the back end I call it "version", you update this one after designs are completed. The local one on front end I called it "versionFrm " to be compared with the one on the back end. Once they confirm they want the newer version I create a VBScript on the fly run it and the code immediately exits out of the db I am in, almost completely stupid proof. The VBScript copies the database creates a desktop shortcut etc.

Main form has a field with the current version number on it pulled from the local table Main form uses the on timer event to compare the tables periodically. I generally set the timer interval to 5min, set it longer if you don't change that database as often. For safety reasons I rename the older file each time and delete ones not needed, that way they will almost always have 2-3 backup front ends on the hard drive I am currently in need of automating the rollback so they don't need to call me for instructions on a manual rollback to the previous versions on the hard drive.

Does this sound like something you could use? If so I will post the code it is a fair amount so I will do a little house cleaning in case you would like it. I will be back in a day or two (tops I hope lol).

Oh btw I just had to add a check to see if the front end is on the network drive when the Main form is opened and if so I prompt them to copy it over this saves me so much time and headache. The supervisors can show the newbs hey look just click and go.

[NeoPa]

Thanks for posting this Denburt. As it's such an interesting concept anyway, can you go ahead and post what you have. That way it's here for posterity :)

[Denburt]

I should probably post this in the articles section yet I would like to refine it a bit before I do that. I think I have everything or at least most of it if there is any questions or comments please feel free to let me know.

Private Sub Form_Timer()
    If CheckVersion(Me!Version) = False Then
        msg = MsgBox("This is an old version there is a newer version available. " & vbCrLf & "Click yes to copy a newer version to your harddrive (the old version will be renamed Futures1.mde and saved for a backup)!" & vbCrLf & "Click no to continue using this version!", vbYesNo)
        If msg = vbYes Then
            UpdateDB
        End If
    End If
End If
End Sub
Public Function CheckVersion(Ver As String)
Dim dbs As Database
Dim rs As Recordset
   On Error GoTo CheckVersion_Error
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Version")
Do While Not rs.EOF
    If Ver = rs!VersionNo Then
        CheckVersion = True
    Else
        CheckVersion = False
    End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dbs = Nothing
   On Error GoTo 0
   Exit Function
CheckVersion_Error:
    ErrEmail "modUtilities", Err.Description, Err.Number, "CheckVersion"
End Function

The UpdateDB function is a bit crude and I did have some trouble getting it to work since it is essentially creating a VBScript file similar to the batch file an earlier poster mentioned. I create this using code so I have even less issues to worry about. Once created you call it, then quit the Access app. Similar to what I think you were describing when you said you could call a function from another DB. Something I have thought about as well but for now this is how I am handling it and it works quite well.

Function UpdateDB()
Dim strVBS As String, strFName As String, strPath As String
strFName = "\copyMDB.vbs"
strPath = Application.CurrentProject.Path

'The VBScript code is simply pasted in below this comment but I removed it and placed the file in a more legible context below. This  could probably be stored in a table or something but as you stated I have been thinking about moving it to another central code DB 

strVBS = "Option Explicit" & vbcrlf &  "etc. etc. see the code below"

'For the finale
SaveTextData strPath & "\" & strFName, strVBS, "ASCII"
'Function to create a shortcut
CreateShrtCut
DoEvents
'Function to call VBScript
RunIt strPath, strFName
DoEvents
DBEngine.Idle
Application.Quit
End Function

Function SaveTextData(FileName, Text, CharSet)
  Const adTypeText = 2
  Const adSaveCreateOverWrite = 2
  
  'Create Stream object
  Dim BinaryStream
  Set BinaryStream = CreateObject("ADODB.Stream")
  
  'Specify stream type - we want To save text/string data.
  BinaryStream.Type = adTypeText
  
  'Specify charset For the source text (unicode) data.
  If Len(CharSet) > 0 Then
    BinaryStream.CharSet = CharSet
  End If
  
  'Open the stream And write binary data To the object
  BinaryStream.Open
  BinaryStream.WriteText Text
  
  'Save binary data To disk
  BinaryStream.SaveToFile FileName, adSaveCreateOverWrite
  Set BinaryStream = Nothing
End Function
Public Sub CreateShrtCut()
On Error Resume Next
    Dim wsShell As New WshShell
    Dim wsSCut As WshShortcut
    Dim strCommandLine As String 'Command Line for shortcut to run
    Dim DskTop
    Dim strYN  As String
 strYN = MsgBox("Would you like a shortcut for this database on your desktop?", vbInformation + vbYesNo)
If strYN = 7 Then Exit Sub
    DskTop = wsShell.SpecialFolders("Desktop")
    Set wsSCut = wsShell.CreateShortcut(DskTop & "\ShortcutFutures.lnk")
    strCommandLine = "C:\SomeFolder\Futures.mde" 
    
    With wsSCut
        .TargetPath = strCommandLine
        .Save
    End With
   
    Set wsSCut = Nothing
    Set wsShell = Nothing
End Sub

VBScript code to be entered into the VBA code from above.

Option Explicit
Dim strFile, sfol, dfol
Dim Cnt
Dim fName, ext, strErr, isOpen
Dim ws, fso, myApp
Set fso = CreateObject("Scripting.FileSystemObject")
'File Name
fName = "futures"
'main folder location
sfol = "network path"
'Destination folder
dfol = "C:\someFolder\"
ext = ".mde"
Cnt = 3
Set WS = CreateObject("Wscript.Shell")
If not Folexists(dfol) then CreateFolder(dfol)
Do Until Cnt = 0
    If FExists(dfol & fName & Cnt & ext) And Cnt = 3 Then
    DeleteIt dfol & fName & Cnt & ext
    ElseIf FExists(dfol & fName & Cnt & ext) And Cnt = 2 Then
    RenameIt dfol & fName & Cnt & ext, dfol & fName & (Cnt + 1) & ext
    ElseIf FExists(dfol & fName & ext) And Cnt = 1 Then
    If FExists(dfol & fName & Cnt & ext) Then
        RenameIt dfol & fName & Cnt & ext, dfol & fName & (Cnt + 1) & ext
    End If
RenameIt dfol & fName & ext, dfol & fName & (Cnt) & ext
    End If
    Cnt = Cnt - 1
Loop
CopyIt sfol & fName & ext, dfol & fName & ext
WS.PopUp "Thanks for updating to the latest version!"
Ws.Run """C:\Program Files\Microsoft Office 2003\OFFICE11\Msaccess.exe """ & dfol & fName & ext,1 
Set myApp = Nothing
Set WS = Nothing
Set fso = Nothing
Function RenameIt(strFile, dfol)
Dim cnt
on error resume next
strErr = fso.MoveFile(strFile, dfol)
if err = 70 then
do until err= 0 or cnt = 200000
err.clear
strErr = fso.MoveFile(strFile, dfol)
cnt = cnt+1
if cnt = 200000 then 
ws.popup "There is an error that we are unable to account for at this time." & vbcrlf & vbcrlf & "Please close any open MS Access databases then go to C:\someFolder and open or click on the file named copyMDB.vbs" & vbcrlf & vbcrlf & "If you have any further problems questions or comments please contact your administrator."
exit do
end if
Loop
end if
End Function
Function DeleteIt(strFile)
strErr = fso.DeleteFile(strFile)
End Function
Function CopyIt(strFile, dfol)
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
strErr = fso.CopyFile(strFile, dfol, True)
Set fso = Nothing
End Function
Function FExists(strFNme)
If Not fso.FileExists(strFNme) Then
    FExists = False
Else
    FExists = True
End If
End Function
Function FolExists(strFol)
If fso.FolderExists(strFol) Then
    FolExists = True
Else
    FolExists = False
End If
End Function
Sub CreateFolder(strFol)
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(strFol) Then
    strErr = fso.CreateFolder(strFol)
End If
Set fso = Nothing
End Sub

[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