Does this help? This is all of the code:
Option Compare Database
Option Explicit
Private strVerClient As String
Private strVerServer As String
Private Sub Form_Load()
On Error Resume Next
' Populate module level variables when form loads.
strVerClient = Nz(DLookup("[VersionNumber]", "[tblVersionClient]"), "")
strVerServer = Nz(DLookup("[VersionNumber]", "[tblVersionServer]"), "")
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String
' looks up the version of the front-end as listed in the backend
strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")
' looks up the version of the front-end on the front-end
strFE = DLookup("fe_version_number", "tbl-fe_version")
' looks up the location of the front-end master file
strMasterLocation = DLookup("s_masterlocation", "tbl-version_master_location")
' checks for the existence of an updating batch file and deletes it if it exists
strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"
If Dir(strFilePath) <> "" Then
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile (strFilePath)
Set fs = Nothing
End If
' if the current database opened is the master then it bypasses the check.
If CurrentProject.Path = strMasterLocation Then
Exit Sub
Else
' if the version numbers do not match and it is not the master that is opened,
' the database will do the update process
If strFE <> strFEMaster Then
MsgBox "Your program is not the latest version." & vbCrLf & _
"The front-end needs to be updated. The program will " & vbCrLf & _
"now close and then should reopen automatically.", vbCritical, "VERSION NEEDS UPDATING"
' sets the global variable for the path/name of the current database
g_strFilePath = CurrentProject.Path & "" & CurrentProject.Name
' sets the global variable for the path/name of the database to copy
g_strCopyLocation = strMasterLocation
' calls the UpdateFrontEnd module
UpdateFrontEnd
End If
End If
End Sub
Private Sub Form_Timer()
On Error Resume Next
Dim strMsg As String
Dim strPath As String
Dim strUpdateTool As String
Const q As String * 1 = """"
Me.TimerInterval = 0
' If versions match, then proceed with opening of main form.
If strVerClient = strVerServer Then
Me.Visible = False
DoCmd.OpenForm "Switchboard"
' ... if not, then offer the user the option to download latest.
Else
strMsg = "You do not have the correct version." & vbCrLf & vbCrLf & _
"Please download the latest version of Survey Central?"
If MsgBox(strMsg, vbExclamation + vbOKCancel, "Update") = vbOK Then
' Notice that I'm using a custom function, LastInStr(), to find
' the last instance of a character. Newer versions of Access expose
' the InstrRev() function to accomplish this, but Access 97 does not.
strPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, ""))
strPath = strPath & "\\IS-SBS001\Shared Folders\Resources\Databases\Survey central database and backups\Update.accdb"
' Enclose the file path in quotes to avoid problems with spaces
' in file and/or folder names.
strUpdateTool = "MSAccess.exe " & q & strPath & q
' This is where the real work happens.
' Use SHELL command to open the UPDATE.ACCDB utility
' ... then quit this client so it may be overwritten.
Shell strUpdateTool, vbNormalFocus
DoCmd.Quit
End If
End If
End Sub
