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%DatabaseJob Management 2007”
Const strONEfilename = “C:UsersAllanDatabaseJob 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:UsersAllanDatabaseJob 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?
