By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 3,021 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

Access 2003 - How best to push front end updates?

P: 20
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.

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.

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.

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

Feb 11 '08 #1
Share this Question
Share on Google+
10 Replies

Expert 100+
P: 108
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 -
Feb 11 '08 #2

P: 20
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\Databases)

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\Databases\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 programmatically 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?
Feb 11 '08 #3

Expert 100+
P: 254
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.
Feb 11 '08 #4

P: 20
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*
Feb 11 '08 #5

Expert 100+
P: 374
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.

Expand|Select|Wrap|Line Numbers
  1. Call "R:\CorpAdmin\Records And Imaging\CSI\Tally Sheets\New User Interface\Install4.BAT"
  2. "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.

Expand|Select|Wrap|Line Numbers
  1. c:
  2. cd\
  3. mkDir BatchTally
  4. CD\BatchTally
  5. Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\BatchTallyV4.MDE"
  6. Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\*.bat" "c:\BatchTally"
  7. Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\*.Lnk" "c:\Documents and Settings\%UserName%\Desktop"
Hope that helps,

Joe P.
Feb 12 '08 #6

Expert Mod 15k+
P: 31,494
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 ;)
Feb 17 '08 #7

Expert 100+
P: 1,356
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.
Feb 28 '08 #8

Expert Mod 15k+
P: 31,494
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 :)
Feb 28 '08 #9

Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2.     If CheckVersion(Me!Version) = False Then
  3.         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)
  4.         If msg = vbYes Then
  5.             UpdateDB
  6.         End If
  7.     End If
  8. End If
  9. End Sub
  10. Public Function CheckVersion(Ver As String)
  11. Dim dbs As Database
  12. Dim rs As Recordset
  13.    On Error GoTo CheckVersion_Error
  14. Set dbs = CurrentDb
  15. Set rs = dbs.OpenRecordset("Version")
  16. Do While Not rs.EOF
  17.     If Ver = rs!VersionNo Then
  18.         CheckVersion = True
  19.     Else
  20.         CheckVersion = False
  21.     End If
  22. rs.MoveNext
  23. Loop
  24. rs.Close
  25. Set rs = Nothing
  26. Set dbs = Nothing
  27.    On Error GoTo 0
  28.    Exit Function
  29. CheckVersion_Error:
  30.     ErrEmail "modUtilities", Err.Description, Err.Number, "CheckVersion"
  31. 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.

Expand|Select|Wrap|Line Numbers
  1. Function UpdateDB()
  2. Dim strVBS As String, strFName As String, strPath As String
  3. strFName = "\copyMDB.vbs"
  4. strPath = Application.CurrentProject.Path
  6. '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 
  8. strVBS = "Option Explicit" & vbcrlf &  "etc. etc. see the code below"
  10. 'For the finale
  11. SaveTextData strPath & "\" & strFName, strVBS, "ASCII"
  12. 'Function to create a shortcut
  13. CreateShrtCut
  14. DoEvents
  15. 'Function to call VBScript
  16. RunIt strPath, strFName
  17. DoEvents
  18. DBEngine.Idle
  19. Application.Quit
  20. End Function
  22. Function SaveTextData(FileName, Text, CharSet)
  23.   Const adTypeText = 2
  24.   Const adSaveCreateOverWrite = 2
  26.   'Create Stream object
  27.   Dim BinaryStream
  28.   Set BinaryStream = CreateObject("ADODB.Stream")
  30.   'Specify stream type - we want To save text/string data.
  31.   BinaryStream.Type = adTypeText
  33.   'Specify charset For the source text (unicode) data.
  34.   If Len(CharSet) > 0 Then
  35.     BinaryStream.CharSet = CharSet
  36.   End If
  38.   'Open the stream And write binary data To the object
  39.   BinaryStream.Open
  40.   BinaryStream.WriteText Text
  42.   'Save binary data To disk
  43.   BinaryStream.SaveToFile FileName, adSaveCreateOverWrite
  44.   Set BinaryStream = Nothing
  45. End Function
  46. Public Sub CreateShrtCut()
  47. On Error Resume Next
  48.     Dim wsShell As New WshShell
  49.     Dim wsSCut As WshShortcut
  50.     Dim strCommandLine As String 'Command Line for shortcut to run
  51.     Dim DskTop
  52.     Dim strYN  As String
  53.  strYN = MsgBox("Would you like a shortcut for this database on your desktop?", vbInformation + vbYesNo)
  54. If strYN = 7 Then Exit Sub
  55.     DskTop = wsShell.SpecialFolders("Desktop")
  56.     Set wsSCut = wsShell.CreateShortcut(DskTop & "\ShortcutFutures.lnk")
  57.     strCommandLine = "C:\SomeFolder\Futures.mde" 
  59.     With wsSCut
  60.         .TargetPath = strCommandLine
  61.         .Save
  62.     End With
  64.     Set wsSCut = Nothing
  65.     Set wsShell = Nothing
  66. End Sub
VBScript code to be entered into the VBA code from above.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Dim strFile, sfol, dfol
  3. Dim Cnt
  4. Dim fName, ext, strErr, isOpen
  5. Dim ws, fso, myApp
  6. Set fso = CreateObject("Scripting.FileSystemObject")
  7. 'File Name
  8. fName = "futures"
  9. 'main folder location
  10. sfol = "network path"
  11. 'Destination folder
  12. dfol = "C:\someFolder\"
  13. ext = ".mde"
  14. Cnt = 3
  15. Set WS = CreateObject("Wscript.Shell")
  16. If not Folexists(dfol) then CreateFolder(dfol)
  17. Do Until Cnt = 0
  18.     If FExists(dfol & fName & Cnt & ext) And Cnt = 3 Then
  19.     DeleteIt dfol & fName & Cnt & ext
  20.     ElseIf FExists(dfol & fName & Cnt & ext) And Cnt = 2 Then
  21.     RenameIt dfol & fName & Cnt & ext, dfol & fName & (Cnt + 1) & ext
  22.     ElseIf FExists(dfol & fName & ext) And Cnt = 1 Then
  23.     If FExists(dfol & fName & Cnt & ext) Then
  24.         RenameIt dfol & fName & Cnt & ext, dfol & fName & (Cnt + 1) & ext
  25.     End If
  26. RenameIt dfol & fName & ext, dfol & fName & (Cnt) & ext
  27.     End If
  28.     Cnt = Cnt - 1
  29. Loop
  30. CopyIt sfol & fName & ext, dfol & fName & ext
  31. WS.PopUp "Thanks for updating to the latest version!"
  32. Ws.Run """C:\Program Files\Microsoft Office 2003\OFFICE11\Msaccess.exe """ & dfol & fName & ext,1 
  33. Set myApp = Nothing
  34. Set WS = Nothing
  35. Set fso = Nothing
  36. Function RenameIt(strFile, dfol)
  37. Dim cnt
  38. on error resume next
  39. strErr = fso.MoveFile(strFile, dfol)
  40. if err = 70 then
  41. do until err= 0 or cnt = 200000
  42. err.clear
  43. strErr = fso.MoveFile(strFile, dfol)
  44. cnt = cnt+1
  45. if cnt = 200000 then 
  46. 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."
  47. exit do
  48. end if
  49. Loop
  50. end if
  51. End Function
  52. Function DeleteIt(strFile)
  53. strErr = fso.DeleteFile(strFile)
  54. End Function
  55. Function CopyIt(strFile, dfol)
  56. Dim fso
  57. Set fso = CreateObject("Scripting.FileSystemObject")
  58. strErr = fso.CopyFile(strFile, dfol, True)
  59. Set fso = Nothing
  60. End Function
  61. Function FExists(strFNme)
  62. If Not fso.FileExists(strFNme) Then
  63.     FExists = False
  64. Else
  65.     FExists = True
  66. End If
  67. End Function
  68. Function FolExists(strFol)
  69. If fso.FolderExists(strFol) Then
  70.     FolExists = True
  71. Else
  72.     FolExists = False
  73. End If
  74. End Function
  75. Sub CreateFolder(strFol)
  76. Dim fso
  77. Set fso = CreateObject("Scripting.FileSystemObject")
  78. If Not fso.FolderExists(strFol) Then
  79.     strErr = fso.CreateFolder(strFol)
  80. End If
  81. Set fso = Nothing
  82. End Sub
Mar 3 '08 #10

Expert 100+
P: 1,356
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:

"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",acNormal ,,,,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("USERNAME") 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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub Version_AfterUpdate()
  2. Dim db As Database, Servdb As Database
  3. Dim strVer As String
  4. Dim rs As Recordset, rs1 As Recordset, ServRs As Recordset
  5. Set db = CurrentDb
  6. Set rs = db.OpenRecordset("Version")
  7. strVer = rs!VersionNo
  8. rs.Close
  9.     Set db = CurrentDb
  10.     Set rs = db.OpenRecordset("ServerList")
  11.     If Not rs.EOF And rs.RecordCount > 1 Then
  12.         Do Until rs.EOF
  13.             Set rs1 = db.OpenRecordset("ServerInfo")
  14.                 rs1.Edit
  15.                 If rs1!ServerPath <> rs!ServerPath Then
  16.                     rs1!ServerPath = rs!ServerPath
  17.                 End If
  18.                 rs1.Update
  19.                 rs1.Close
  20.                 RlnkTbl False
  21.         CopyFile Application.CurrentProject.FullName, rs!ServerPath & Application.CurrentProject.Name, False
  22. DoCmd.RunSQL "UPDATE Version IN '" & MyPath & "\" & Left(BkEnd, Len(BkEnd) - 4) & "\" & BkEnd & "' SET Version.VersionNo = '" & strVer & "'"
  23.         rs.MoveNext
  24.         Loop
  25.     rs.Close
  26.     End If
  27. End If
  28. Set rs = Nothing
  29. Set db = Nothing
  30. End Sub
Mar 3 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.