Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    309
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Need help with code

    I was looking on the net for ways to update the users FE and came across the following code which I have tried to adapt for my needs

    Code:
    Dim WSHShell
    Dim appAccess               ' Current Access application window
    Dim fso                 ' FileSystem object used to access files
    'Dim DesktopPath            ' Desktop folder, where shortcut is placed
    
    Const iButtonTypeOK = 0         ' Popup controls
    Const iIconTypeStopMark = 16
    Const iIconTypeQuestionMark = 32
    Const iIconTypeExclamationMark = 48
    Const iIconTypeInformationMark = 64
    Dim intButton
    Const iOKButtonClicked = 1
    Const iButtonsNotClicked = -1
    
    'Const strONEfilename = "%USERPROFILE%\Database\Job Management 2007"
    Const strONEfilename = "C:\Users\Allan\Database\Job Management 2007"
    Const strONEfolder = "r:\"
    
    Set WSHShell = CreateObject("WScript.Shell")
    Set fso = CreateObject("Scripting.FilesystemObject")
    
    
    ' Warn the user that updating is about to occur.
    ' (A 10 second delay is added.  With a slow network connection, somehow this
    ' helps to avoid the automatic update from being launched a second time.)
    
    While intButton <> iButtonsNotClicked
    
    intButton = WSHShell.PopUp("W A I T !  Don't click the button!" & vbCrLf & vbCrLf & "ONE is about to be automatically updated.  Please wait while the new version is loaded--then you will see another alert dialog.", 10, "Automatic Update of ONE", iButtonTypeOK + iIconTypeStopMark)
    
    Wend
    
    
    ' First stop the current Access program, then copy files from the
    ' ONE folder to the C: drive, then launch the new program in the same
    ' application window.
    
    On Error Resume Next
    ' Detect if ONE is running (from the proper folder) by an indirect method:
    ' If the LDB file cannot be deleted then the MDB file is probably opened.
    ' Trap the error accordingly.
    fso.DeleteFile "C:\Users\Allan\Database\Job Management 2007.laccdb"
    'fso.DeleteFile strONEfilename & ".laccdb"
    If Err.Number = 70 Then
        ' LDB file cannot be deleted, so ONE is running as expected.
        On Error GoTo 0
    
        ' Close down the instance that called this script
        Set appAccess = GetObject(strONEfilename & ".accdb")
        appAccess.CloseCurrentDatabase
    
        ' Update ONE
        On Error Resume Next
        fso.CopyFile strONEfolder & "\Job Management 2007.accdb", strONEfilename & ".accdb"
    
        If Err.Number = 0 Then
    
            ' Announce that ONE has been successfully updated
    
            WSHShell.PopUp "Your ONE program has been updated." & vbCrLf & vbCrLf & "Click the button to launch ONE.", 0, "Automatic Update of ONE", iButtonTypeOK + iIconTypeInformationMark
    
            ' Launch the updated program in the same Access window
    
            appAccess.OpenCurrentDatabase strONEfilename & ".accdb"
    
            ' Done !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    
        ElseIf Err.Number = 76 Then
    
            ' The ONE file server folder is not available.
            ' Leave Access open with no program running.
    
            WSHShell.PopUp "The copy script cannot update your program, probably due to an intermittent network problem; it failed with error " & Err.Number & ": " & Err.DESCRIPTION & "  !!!", 0, "Automatic Update Failure", iButtonTypeOK + iIconTypeInformationMark
    
            ' Done !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    
        Else
    
            ' Unexpected error
    
            WSHShell.PopUp "The copy script failed with error " & Err.Number & ": " & Err.DESCRIPTION & "  !!!  Please report this error and do not continue using the program !!!", 0, "Automatic Update Failure", iButtonTypeOK + iIconTypeExclamationMark
    
            ' Done !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    
        End If
    
    Else
    
        'Perhaps the script was launched in another way,
        'or the user has already closed ONE.
    
        WSHShell.PopUp "The copy script failed with error " & Err.Number & ": " & Err.DESCRIPTION & "  !!!  If this happens again then please report this error and do not continue using the program !!!", 0, "Automatic Update Failure", iButtonTypeOK + iIconTypeExclamationMark
    
    End If
    the original code comes from this thread item 5.

    Questions and problems.

    Does this code need to be called from a module? I have tried it behind a button on a form and it shuts the database down but doesn't continue.

    This command fso.DeleteFile strONEfilename & ".laccdb" will not run as %USERPROFILE% is not understood by the command. Is there a way to read this earlier or to read the current database file location so that the script will work for different PCs?
    "Heading for the deep end"

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    Weyrman,

    Under normal circumstances when you're running a front end the users have access to the back end on a server of some kind. What I used to do is just put an updated copy of the FE on the server and then place an Icon (shortcut running a simple batch file) on the User's desktop that they can double click to copy the FE file from the server to the local workstation. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    309
    Thanks
    3
    Thanked 0 Times in 0 Posts
    That's actually what I have right now but I thought it would be nice if I could get it to be fully automated.
    "Heading for the deep end"

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    What you can do in the bat file is to startup your frontend so all you have to do is kick start the bat file

  6. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    309
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I found an excellent code module here that creates a bat file that closes then reopens the database that ran it. By simply adding lines to it that copied the DB from the "server" where the latest version is kept before reopening it allowed me to achieve what I wanted, And how!!!
    "Heading for the deep end"

  7. #6
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    82
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Two recommended solutions (I use the AutoFEUpdater Utility):
    http://autofeupdater.com/ or
    http://www.fmsinc.com/MicrosoftAcces...onLauncher.asp

    Note that you can sometimes get unexpected results if you attempt to run code from within an Access application that is designed to replace itself, if it detects a newer version. In fact, I used to use such code, but about 1 or 2% of the time, I would have user's complain that the application was not useable. After switching many years ago to the AutoFEUpdater, I have had no more such problems.
    Last edited by tgw7078; 2014-06-05 at 11:13.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •