Auto update of distributed .mde file

mlai08

Registered User.
Local time
Today, 12:18
Joined
Dec 20, 2007
Messages
110
Hi there,

We keep a backend and a master frontend of a database on a network drive and distributed a .mde version of the frontend to the users who save a copy of it on their hard drive. There is a separate version table on the frontend and backend of the database for version comparsion. Whenever there is change on the master frontend, I shall change the version # on those tables and make a new .mde file on the network.

When user open their own mde frontend, there are scripts to check its version with the master frontend on the network and any mismatch will trigger off the following scripts (on the default form) to replace the old version with a new one from the network.

Private Sub Form_Load()
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 (shown below)

UpdateFrontEnd

End If

Option Compare Database
' global variable for path to original database location
Public g_strFilePath As String
' global variable for path to database to copy from
Public g_strCopyLocation As String

Public Sub UpdateFrontEnd()

Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String

' sets the file name and location for the file to delete
strKillFile = g_strFilePath
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
' sets the file name of the batch file to create
TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
' sets the restart file name
strRestart = """" & strKillFile & """"
' creates the batch file

Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1
'Exit Sub
' runs the batch file

Shell TestFile

'closes the current version and runs the batch file
DoCmd.Quit

End Sub

End If

End Sub

(Sorry about the code alignment, I can't indent them properly)

This process works fine for everyone in Access 2010. However, these scripts appear to have conflicts with Win 7 when users upgraded to the O/S. They keep getting database unstable error message and the process fails to work. Does anyone have a solution to this problem and whether we can change the scripts to make them compatible with Win 7? I got those scripts from the internet a while back.

Thanks in advance.
 
For starters, what Paul said.

I jumped in because at my previous job, my predecessor used this exact code, and I wound up having to clean it up. I'm going to format it for you and post it in code tags (please still read Paul's link!), but just from looking at it I can tell you that you have it all jumbled. You have one procedure completely included inside the other, and your option and procedure-level declarations are in the middle of your code!
 
but just from looking at it I can tell you that you have it all jumbled. You have one procedure completely included inside the other, and your option and procedure-level declarations are in the middle of your code!
I am quiet impressed you even managed to read the code. ;) I walked through the first four lines and I gave up. So I did not comment on anything !
 
For starters, what Paul said.

I jumped in because at my previous job, my predecessor used this exact code, and I wound up having to clean it up. I'm going to format it for you and post it in code tags (please still read Paul's link!), but just from looking at it I can tell you that you have it all jumbled. You have one procedure completely included inside the other, and your option and procedure-level declarations are in the middle of your code!


Thank you for your quick response and help. I shall wait for the good news on the revised codes.
 
Paul, I was able to read it because I recognized it - I've seen that EXACT code before! :D

Okay, OP, here's your code as-posted. I've indented it and color-coded some issues. My comments are in GREEN and prefaced by ///

Code:
[COLOR=red]Private Sub Form_Load()[/COLOR]
[COLOR=seagreen]///Personally, instead of an on-load event, I'd put this in a start-up routine and run that [/COLOR]
[COLOR=seagreen]routine via the AutoExec macro.[/COLOR]
[COLOR=red]End Sub[/COLOR]
[COLOR=seagreen]///You ended the sub immediately after declaring it![/COLOR]
[COLOR=seagreen]///Everything between this point and the next procedure declaration is so much gibberish [/COLOR]
[COLOR=seagreen]to the compiler.[/COLOR]
 
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,
            [COLOR=red]VERSION NEEDS UPDATING"[/COLOR]
            [COLOR=seagreen]///Missing an opening quote for the message box title.[/COLOR]
            ' 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 (shown below)
 
            UpdateFrontEnd
            [COLOR=seagreen]///SERIOUS problem here, and what I had to fix:  If the routine cannot locate the[/COLOR]
[COLOR=seagreen]          backend for some reason (in my case, it was users attempting to run the tool without[/COLOR]
[COLOR=seagreen]          logging into the WAN), this routine will DELETE the front end entirely, as[/COLOR]
[COLOR=seagreen]          strMasterLocation will be an empty string.[/COLOR]
        End If
 
        [COLOR=red]Option Compare Database[/COLOR]
[COLOR=red]     ' global variable for path to original database location[/COLOR]
[COLOR=red]     Public g_strFilePath As String[/COLOR]
[COLOR=red]     ' global variable for path to database to copy from[/COLOR]
[COLOR=red]     Public g_strCopyLocation As String[/COLOR]
        [COLOR=seagreen]///These need to be at the top of the module.[/COLOR]
 
        Public Sub UpdateFrontEnd()
 
        Dim strCmdBatch As String
        Dim notNotebook As Object
        Dim FSys As Object
        Dim TestFile As String
        Dim strKillFile As String
        Dim strReplFile As String
        Dim strRestart As String
 
        ' sets the file name and location for the file to delete
        strKillFile = g_strFilePath
        ' sets the file name and location for the file to copy
        strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
        ' sets the file name of the batch file to create
        TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
        ' sets the restart file name
        strRestart = """" & strKillFile & """"
        ' creates the batch file
 
        Open TestFile For Output As #1
        Print #1, "Echo Off"
        Print #1, "ECHO Deleting old file"
        Print #1, ""
        Print #1, "ping 1.1.1.1 -n 1 -w 2000"
        Print #1, ""
        Print #1, "Del """ & strKillFile & """"
        Print #1, ""
        Print #1, "ECHO Copying new file"
        Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
        Print #1, ""
        Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
        Print #1, "START /I " & """MSAccess.exe"" " & strRestart
        Close #1
        'Exit Sub
        ' runs the batch file
 
        Shell TestFile
 
        'closes the current version and runs the batch file
        DoCmd.Quit
 
        End Sub
 
    [COLOR=red]End If[/COLOR]
[COLOR=seagreen]///Procedures cannot be contained within other procedures.[/COLOR]
End Sub

To be quite honest, I cannot see how this code as presented would even compile, much less execute.

I'll get a cleaned copy up soon.
 
Last edited:
Okay, here's a cleaned-up version. As I said, I would STRONGLY advise placing both procedures in their own start-up module, and setting up an AutoExec macro that runs a start-up procedure that runs the first procedure (that right now is Form_Load), and then opens your initial form manually after that.

My comments are still prefaced with /// and can be removed if you so desire.

Code:
Option Compare Database
Option Explicit
[COLOR=seagreen]'///*ALWAYS* USE OPTION EXPLICIT[/COLOR]
 
[COLOR=seagreen]' global variable for path to original database location[/COLOR]
Public g_strFilePath As String
[COLOR=seagreen]' global variable for path to database to copy from[/COLOR]
Public g_strCopyLocation As String
 
[COLOR=seagreen]'///AS A RULE TRY TO AVOID DECLARING GLOBAL VARIABLES IN FORM MODULES[/COLOR]
[COLOR=seagreen]'///MOVE THIS ENTIRE PROCEDURE TO A MODULE, SO YOU CAN CHANGE THESE[/COLOR]
[COLOR=seagreen]'   TO PROCEDURE-LEVEL DECLARATIONS.[/COLOR]
 
Private Sub Form_Load()
[COLOR=seagreen]'///THIS REALLY SHOULD BE DONE AS PART OF A STARTUP ROUTINE FOR THE DATABASE,[/COLOR]
[COLOR=seagreen]'   NOT AS A FORM_LOAD EVENT.  YOU WANT THIS TO RUN BEFORE *ANYTHING* ELSE![/COLOR]
 
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String
 
[COLOR=seagreen]  ' looks up the version of the front-end as listed in the backend[/COLOR]
    strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")
[COLOR=seagreen]  ' looks up the version of the front-end on the front-end[/COLOR]
    strFE = DLookup("fe_version_number", "tbl-fe_version")
[COLOR=seagreen]  ' looks up the location of the front-end master file[/COLOR]
    strMasterLocation = DLookup("s_masterlocation", "tbl-version_master_location")
 
[COLOR=seagreen]  ' checks for the existence of an updating batch file and deletes it if it exists[/COLOR]
    strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"
    If Dir(strFilePath) <> "" Then Kill strFilePath
[COLOR=seagreen]  '///THIS IS A FASTER/SHORTER METHOD TO DELETE A FILE THAN IN THE ORIGINAL.[/COLOR]
 
[COLOR=seagreen]  'Make sure that a master file location was found.[/COLOR]
    If strFEMaster = "" Then
        'No master file was found - backend not available.
        MsgBox "Backend not available!  Shutting down.", vbCritical
        DoCmd.Quit
[COLOR=seagreen]  ' if the current database opened is the master then it bypasses the check.[/COLOR]
    ElseIf CurrentProject.Path <> strMasterLocation Then
[COLOR=seagreen]      ' if the version numbers do not match and it is not the master that is opened,[/COLOR]
[COLOR=seagreen]      ' the database will do the update process[/COLOR]
        If strFE <> strFEMaster Then
           [COLOR=seagreen]'Notify the user that the application will update.[/COLOR]
            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"
 
[COLOR=seagreen]          ' sets the global variable for the path/name of the current[/COLOR] database
            g_strFilePath = CurrentProject.Path & "\" & CurrentProject.Name
 
[COLOR=seagreen]          ' sets the global variable for the path/name of the database to copy[/COLOR]
            g_strCopyLocation = strMasterLocation
 
[COLOR=seagreen]          ' calls the UpdateFrontEnd module (shown below)[/COLOR]
            UpdateFrontEnd
        End If
    End If
End Sub
 
Public Sub UpdateFrontEnd()
 
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String
 
[COLOR=seagreen]  ' sets the file name and location for the file to delete[/COLOR]
    strKillFile = g_strFilePath
[COLOR=seagreen]  ' sets the file name and location for the file to copy[/COLOR]
    strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
[COLOR=seagreen]  ' sets the file name of the batch file to create[/COLOR]
    TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
[COLOR=seagreen]  ' sets the restart file name[/COLOR]
    strRestart = """" & strKillFile & """"
 
[COLOR=seagreen]  ' creates the batch file[/COLOR]
    Open TestFile For Output As #1
    Print #1, "Echo Off"
    Print #1, "ECHO Deleting old file"
    Print #1, ""
    Print #1, "ping 1.1.1.1 -n 1 -w 2000"
    Print #1, ""
    Print #1, "Del """ & strKillFile & """"
    Print #1, ""
    Print #1, "ECHO Copying new file"
    Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
    Print #1, ""
    Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
    Print #1, "START /I " & """MSAccess.exe"" " & strRestart
    Close #1
[COLOR=seagreen]  'Exit Sub[/COLOR]
[COLOR=seagreen]  ' runs the batch file[/COLOR]
 
    Shell TestFile
 
[COLOR=seagreen]  'closes the current version and runs the batch file[/COLOR]
    DoCmd.Quit
End Sub

Anyway, see if this works any better. If you get errors, please post exactly what the error says.
 
Last edited:
Thank you so much for cleaning up the codes. I agree it is better run the codes at startup to check the version and replace the mde file where neccessary. I have now created a sub routine in the module with all the codes in the form so that all codes for the process are in the same module. However, when I run the sub in AutoExec marco, it does not work. I selected Run Code in the marco action. What should be the correct action to call a procedure in the marco?

Sorry for my ignorance since I have just recently been inherited with the database and trying to sort out various issues arosed from it.
 
Sorry for my ignorance since I have just recently been inherited with the database and trying to sort out various issues arosed from it.

It's not a problem.

The Run Code macro can only run a function. The easy fix there is to change the first one from a sub to a function, and it should run fine.
 
The autoexec marco works to replace the old mde file but the problem is that the start up from will show up at the same time as the incorrect version message box which would be confusing to the users.

Leaving the codes in the load form event does not have this problem. I really want to set a start up form since it is an user login form.
 
Go into your startup options and turn off the option to load a specific form at startup.

In your startup PROCEDURE, include a DoCmd.OpenForm "FormName" at the end, replacing FormName with the name of your startup form.

That way, the form will only be opened if the version check is passed.

Edit: Here's an example from one of my tools. Although it doesn't do a version check, the same logic applies.

Code:
Public Function Startup()
' ************************************************************
' Created by       : Scott L Prince
' Parameters       : None
' Result           : Runs necessary startup functions.
' Date             : 1-3-14
' Remarks          :
' Changes          :
' ************************************************************
On Error GoTo Startup_Err
 
Dim ProcedureName As String
 
    ProcedureName = "Startup"
 
    'Determine if the dev switch is active.
    If Command() = "dev" Then
        'Show the database window.
        DoCmd.SelectObject acTable, , True
    Else
        'Relink all linked tables.
        If RelinkTables() = False Then
            'Relink failed.  Ask the user what to do; if they choose not to continue, quit the app.
            If MsgBox("Backend relink failed.  Do you wish to open the tool anyway?", vbYesNo + vbDefaultButton2, AppTitle) _
               <> vbYes Then DoCmd.Quit
        End If
 
[COLOR=blue]      'Open the main menu.[/COLOR]
[COLOR=blue]      DoCmd.OpenForm "frmMainMenu"[/COLOR]
 
    End If
 
Startup_Exit:
    Exit Function
 
Startup_Err:
    MsgBox "Error occurred" & vbCrLf & vbCrLf & _
    "In procedure:" & vbTab & ProcedureName & vbCrLf & _
    "Err Number: " & vbTab & Err.Number & vbCrLf & _
    "Description: " & vbTab & Err.Description, vbCritical, AppTitle
    Resume Startup_Exit
 
End Function
 
Last edited:
Go into your startup options and turn off the option to load a specific form at startup.

In your startup PROCEDURE, include a DoCmd.OpenForm "FormName" at the end, replacing FormName with the name of your startup form.

That way, the form will only be opened if the version check is passed.

Edit: Here's an example from one of my tools. Although it doesn't do a version check, the same logic applies.

Code:
Public Function Startup()
' ************************************************************
' Created by       : Scott L Prince
' Parameters       : None
' Result           : Runs necessary startup functions.
' Date             : 1-3-14
' Remarks          :
' Changes          :
' ************************************************************
On Error GoTo Startup_Err
 
Dim ProcedureName As String
 
    ProcedureName = "Startup"
 
    'Determine if the dev switch is active.
    If Command() = "dev" Then
        'Show the database window.
        DoCmd.SelectObject acTable, , True
    Else
        'Relink all linked tables.
        If RelinkTables() = False Then
            'Relink failed.  Ask the user what to do; if they choose not to continue, quit the app.
            If MsgBox("Backend relink failed.  Do you wish to open the tool anyway?", vbYesNo + vbDefaultButton2, AppTitle) _
               <> vbYes Then DoCmd.Quit
        End If
 
[COLOR=blue]     'Open the main menu.[/COLOR]
[COLOR=blue]     DoCmd.OpenForm "frmMainMenu"[/COLOR]
 
    End If
 
Startup_Exit:
    Exit Function
 
Startup_Err:
    MsgBox "Error occurred" & vbCrLf & vbCrLf & _
    "In procedure:" & vbTab & ProcedureName & vbCrLf & _
    "Err Number: " & vbTab & Err.Number & vbCrLf & _
    "Description: " & vbTab & Err.Description, vbCritical, AppTitle
    Resume Startup_Exit
 
End Function

Should I run this start up function (after editing the start up form name) in the autoexec marco after running the version check codes?
 
Should I run this start up function (after editing the start up form name) in the autoexec marco after running the version check codes?

No. You use the autoexec macro to call your startup function. In that function, you call your version check routine, then you open your startup form. The startup procedure I copied for you was basically just an example - you'd replace the RelinkTables function with your Version check function, and alter the code to deal with the fact that the version check doesn't return any data.

Edit: Sorry, had to go get dinner. Anyway, basically, all you really need is this:

Code:
Public Function Startup()

    VerifyFrontEndVersion
    DoCmd.OpenForm "frmMainMenu"

End Function

That's the bare minimum you would need.
 
Last edited:
Got it but in this case, users may also see other objects (forms, tables etc.) in the database on top of the start up form.

When the form is put in the database start up option, only this form shows up when users open the database and that is what I want.
 
Got it but in this case, users may also see other objects (forms, tables etc.) in the database on top of the start up form.

When the form is put in the database start up option, only this form shows up when users open the database and that is what I want.

There is a startup option called 'Display Database Window'. Make sure it is NOT selected, and the window with the various database objects will not show, even during startup, unless they do a shift-bypass.

That is actually why in my original code, I have these lines when starting the tool in dev mode:

Code:
        'Show the database window.
        DoCmd.SelectObject acTable, , True
 
There is a startup option called 'Display Database Window'. Make sure it is NOT selected, and the window with the various database objects will not show, even during startup, unless they do a shift-bypass.

That is actually why in my original code, I have these lines when starting the tool in dev mode:

Code:
        'Show the database window.
        DoCmd.SelectObject acTable, , True

Everythng seems working fine except that the UpdateDbFE.cmd file is not removed after the version update. It still sits at the location where the mde file is.
 
No worry. I found out why, because the new version did not open automatically after the update and that was why the batch file was not removed.

Anyway, thank you so much for your patience and helpful comments. Much appreciated.
 
Thanks again for helping me to tidy up the file updating codes a while back. We now have a slight issue when running the codes. The VersionCheck sub routine runs fine and display the version different message but when users click OK, the Update FrontEnd batch file does not seem to run. It just stays in user's C:\. User has to click open the batch file to replace the current frontend version with the new one.

This might be coincidence but this issue seems happening after we are upgraded to Win 7. Is there any way to refine the codes to enforce running of the batch file?

Thanks
 
Last edited:
Thanks again for helping me to tidy up the file updating codes a while back. We now have a slight issue when running the codes. The VersionCheck sub routine runs fine and display the version different message but when users click OK, the Update FrontEnd batch file does not seem to run. It just stays in user's C:\. User has to click open the batch file to replace the current frontend version with the new one.

This might be coincidence but this issue seems happening after we are upgraded to Win 7. Is there any way to refine the codes to enforce running of the batch file?

Thanks

Yes. The code I grabbed and gave you initially wasn't really that well-written. I've posed an updated, cleaned up version in the Code Repository that should work better.

The post in question is HERE. If you don't want to replace the entire front end check procedure, you should be able to just grab the batch file portion of the update procedure and edit it to fit your needs.

Let me know if you run into any problems.
 
The revised codes work great in Win 7. Thanks again for your help.
 

Users who are viewing this thread

Back
Top Bottom