Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    rankhornjp is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jul 2011
    Posts
    46

    Another way to Auto Update of Front End

    Create a folder on your network drive. In this folder you should have your Master copy of your FE and a .bat file.

    Open notebook and create the .bat file and save it to your network drive. Then go to each computer that is going to use the database and create a shortcut that points to the .bat file. This is the shortcut they will use every time they open the database, so rename it appropriately. Rename your Master FE file with a version number, I used the date.

    The .bat file should read:
    Code:
    @echo off
    REM file used to automatically update the Database when updates are available.
    if not exist %userprofile%\appdata\local\redirect\ui1.3.2013.accdr echo Database needs to update to newer version.
    if not exist %userprofile%\appdata\local\redirect\ui1.3.2013.accdr pause
    @echo off
    if not exist %userprofile%\appdata\local\redirect\ui1.3.2013.accdr if exist %userprofile%\appdata\local\redirect\ui*.accdr del %userprofile%\appdata\local\redirect\ui*.accdr
    
    
    if not exist %userprofile%\appdata\local\redirect\ui1.3.2013.accdr Robocopy \\DUI_NAS\data\database %userprofile%\appdata\local\redirect ui1.3.2013.accdr
    
    
    Start MSAccess.exe /runtime %userprofile%\appdata\local\redirect\ui1.3.2013.accdr
    
    
    Exit
    Where local file is kept
    File name


    Message that pops up in a CMD window to let the user know the file is updating. They will be prompted to press any key.
    This line checks for previous versions and deletes them prior to copying the new version
    Location of the Master Copy on network drive.
    Runtime switch is only needed if you run your database in runtime mode or your users don't have the full version of Access


    How it works.

    The .bat file checks the local copy of the FE against the Master Copy (on the network drive), If they match, it opens MSAccess with the local file. If they don't match, it copies the Master Copy to the local drive and the opens MSAccess with the new local file. When you update the Master Copy, change the date when you save. Then input the new file name in the .bat file (Find and Replace works well for this) and the next time they start the database they will update to the newest version.



    I have been using this method for 6 months without any issues.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It is possible to do automatic update without an external script file. I used to use VBScript file to perform the copy/replace but found code that can run within the Access project to do copy/replace of the file. Code first checks a version number saved in BE table against version number in a label on form. If not a match then the copy/replace code executes. Here is excerpt of code.
    Code:
    If Me.tbxVersion <> Me.lblVersion.Caption Then
    'copy Access file
    CreateObject("Scripting.FileSystemObject").CopyFile _
        gstrBasePath & "Program\Install\MaterialsDatabase.accdb", "c:\", True
    'allow enough time for file to completely copy before opening
    Dim Start As Double
    Start = Timer
    While Timer < Start + 3
        DoEvents
    Wend
    'load new version - SysCmd function gets the Access executable file path
    'Shell function requires literal quote marks in the target filename string argument, hence the quadrupled quote marks
    Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
    'close current file
    DoCmd.Quit
    End If
    I still have the VBScript file on each user machine because occasionally user copy gets corrupted and I have to force an update.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MagicMarkerz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    7
    First of all, I'm very sorry for reviving a topic from two years ago. I promise to be very brief with this, if at all possible.

    June mentioned being able to update a front end through internal Access workings, and I understand most of what her code's doing. The part that I'm lost at is the very, very beginning of it:
    Code:
    If me.tbxVersion <> me.lblVersion
    I understand what it's doing, but I'm not necessarily understanding what it's looking at, specifically. Could you please elaborate?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Only 1 year ago so you are forgiven!

    A form opens by default when the db opens. This form is bound to a table with 1 record and 1 field, text content: Ver x.x.x

    A textbox is bound to that field. A label has the caption: Ver x.x.x

    Whenever I publish a new version I change both and replace the frontend copy in the Install folder (no one else ever touches my development master in my Edit folder).

    When user opens their copy of the db, code compares these values. If different then there is a new version and the copy code runs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    MagicMarkerz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    7
    Then I suppose I only feel half as guilty, huzzah!

    That makes nearly perfect sense. Just one final thing of clarification: when you say 'Caption', do you mean the datasheet caption property you can set? Or are you setting it in some other fashion?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I mean a label control on form. Form in single view.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    June, I don't quite get how you are keeping the database from copying over itself while it's still open. What am I missing?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I know what you mean. The code still baffles me - I didn't originate it. I just know it works. Occasionally a user's copy will become corrupt and it doesn't work and have to 'force' an update. I still have the VBScript on each machine for those occasions - hasn't happened for months now.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Yeah it doesn't work for me in 2010 on a test database. I get to the copy part and it says I don't have permission.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Same issue here. Permissions is why the user's copy has to be at C:\ root.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Curious on two counts. I had the same thought as Xipooo, that you were copying a file over itself while it was open, in essence having 2 files with the same name. Very surprised it works. Second, I've had the opposite experience with Win 7 and the root directory. This is cut/pasted from my version table where I track changes made to a db, relates to an Excel file being built/saved:

    chg code to get template from and save to C:\AccessAp since Windows 7 won't allow on root

    My previous code that saved to the root threw an error on Win 7. We have Win 7 Pro and log into a domain; don't know if those change anything.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    We just upgraded to Windows 7 from XP and the code still works as designed. We do login to network domain as well.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One of those many mysteries we'll probably never know the reasons for. Perhaps your IT dept opened C up and mine (me) didn't. Just tried on my PC and I can't save a file on the root. I certainly know how to change the permissions, but didn't want to bother changing all user PC's, so I just tweaked the code. I'm lazy that way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Here's one I just wrote today. You'll obviously need to make some adjustments to fit your needs.

    Public Sub VersionUpdate()
    Dim localVersion As Single
    Dim remoteVersion As Single
    Dim currentPath As String
    Dim oldPath As String
    Dim rs As Recordset
    Dim rs2 As Recordset
    Dim fso As Object
    Dim db As Database
    Dim intRight As Integer
    Dim filename As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set rs = CurrentDb.OpenRecordset("tbl9WorkControl", dbOpenDynaset, dbSeeChanges)
    If Not rs.EOF Then
    localVersion = rs("VersionNumber")
    currentPath = Nz(rs("CurrentPath"), "")
    End If
    Set rs = CurrentDb.OpenRecordset("tbl9CompanyInfo", dbOpenDynaset, dbSeeChanges)
    If Not rs.EOF Then
    remoteVersion = rs("VersionNumber")
    End If
    If remoteVersion > localVersion Then
    If MsgBox("New Version " & remoteVersion & " Found." & vbCrLf & "Would you like to update now?", vbYesNo, "New Version") = vbNo Then Exit Sub
    If Not fso.FolderExists(CurrentProject.Path & "\update\") Then fso.CreateFolder CurrentProject.Path & "\update\"
    fso.CopyFile rs("ApplicationDownloadPath"), CurrentProject.Path & "\update\", True
    intRight = InStrRev(rs("ApplicationDownloadPath"), "\")
    filename = Right(rs("ApplicationDownloadPath"), Len(rs("ApplicationDownloadPath")) - intRight)
    oldPath = CurrentProject.FullName
    Set db = OpenDatabase(CurrentProject.Path & "\update\" & filename, True)
    Set rs2 = db.OpenRecordset("tbl9WorkControl", dbOpenTable)
    rs2.MoveFirst
    rs2.Edit
    rs2!currentPath = oldPath
    rs2.Update
    Set rs2 = Nothing
    Set db = Nothing
    Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.Path & "\update\" & filename & """", vbNormalFocus
    DoCmd.Quit
    ElseIf remoteVersion = localVersion And CurrentProject.FullName <> currentPath Then
    Shell Environ$("comspec") & " /c xcopy """ & CurrentProject.FullName & """ """ & currentPath & """ /y", vbHide
    Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & currentPath & """", vbNormalFocus
    DoCmd.Quit
    ElseIf remoteVersion = localVersion And CurrentProject.FullName = currentPath Then
    If fso.FolderExists(CurrentProject.Path & "\update") Then
    Shell Environ$("comspec") & " /c rd """ & CurrentProject.Path & "\update"" /s /q"
    End If
    End If

    End Sub
    We use a table on the SQL server called tbl9companyinfo to hold a version number and network location of the new FE. We compare the SQL table version number to a version number held on a local table to see if an update is ready I also have a field on the tbl9WorkControl that holds the location of the original FE database location. This is important so that when the new version is opened it can see that it's not in the original location and copies itself to the correct location.

    You COULD modify this to use an access database as the "rover" if you will... or even for that matter use the new database itself so long as it's location stays consistent.

  15. #15
    MagicMarkerz is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    7
    So just to make sure I'm understanding this correctly, June -- your system is copying the new front end and placing it directly on the C:\ drive, correct?

    If so, could it be modified to go to a different location?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-09-2013, 05:26 PM
  2. update front end mde
    By slimjen in forum Access
    Replies: 2
    Last Post: 10-31-2011, 11:37 AM
  3. Front End Update Problem
    By Randy in forum Access
    Replies: 2
    Last Post: 02-12-2011, 09:46 PM
  4. auto link between front db and back db
    By ahmed in forum Programming
    Replies: 3
    Last Post: 08-09-2010, 07:10 AM
  5. Auto Update Forms
    By Vikki in forum Forms
    Replies: 1
    Last Post: 02-09-2010, 10:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums