Automatic Update

Posted on 2007-11-19
Last Modified: 2013-11-28
I have a form in Microsoft Access with VB code behind the form.
I have 5 users using the Access Database individually on there system, all connecting to MSSQL Database tables.

I changed vb code in the form, but instead of me individually going to each terminal and changing the code, or sending them a new mdb file out, is there another method that they can download the new form to update there existing access?

Question by:jseaman12
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 57
ID: 20313753
<<I changed vb code in the form, but instead of me individually going to each terminal and changing the code, or sending them a new mdb file out, is there another method that they can download the new form to update there existing access?>>

  The simplest method is to place a verison table in the front end and code to check the version of the local copy vs the server copy of the FE.  If they don't match tell the user they need to update.

  The "update" need be nothing more then a batch file they can execute to copy down the new version of the FE.

  below is my own version check and an example of a batch file.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20313758
LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 20313775
sorry, hit the return to fast<g>.  Note too that the batch file is fairly complex.  At it's simplest, all you need is a file copy.


Function CheckVersion()

          Dim pb As New Form_frmProgBar
          Dim sngNetVersion As Single
          Dim sngLocalVersion As Single
          Dim strMsgText As String
          Dim strCrLf As String

10        pb.SetMessage "Checking for new version..."
20        pb.SetBarVisible False

30        sngNetVersion = DMax("[Version]", "tblAppVersionControlNet")
40        sngLocalVersion = AppVersion()

50        Set pb = Nothing

60        If sngLocalVersion < sngNetVersion Then
70            strCrLf = Chr$(13) & Chr(10)
80            strMsgText = "You do not have the most recent version of this application.  Please update the application." & strCrLf & strCrLf
90            strMsgText = strMsgText & "The following changes have occured:" & strCrLf & strCrLf
100           strMsgText = strMsgText & "    " & DLookup("[UserComment]", "tblAppVersionControlNet", "[Version] = " & sngNetVersion)
110           MsgBox strMsgText
120           Call ApplicationExit
130       End If

End Function

@ECHO Checking for application directories...
if not exist "%HOMEDRIVE%\Program Files" mkdir "%HOMEDRIVE%\Program Files"
if not exist "%HOMEDRIVE%\Program Files\xxxxxx Custom" mkdir "%HOMEDRIVE%\Program Files\xxxxxx Custom"
@ECHO Copying application files...
COPY "P:\xxxxxx Custom\PackingLabels\PackingLabels.MDE" "%HOMEDRIVE%\Program Files\xxxxxx Custom"
COPY "P:\xxxxxx Custom\PackingLabels\PackingLabels.ICO" "%HOMEDRIVE%\Program Files\xxxxxx Custom"
COPY "P:\xxxxxx Custom\Update.ico" "%HOMEDRIVE%\Program Files\xxxxxx Custom"
COPY "P:\xxxxxx Custom\vbSendMail.dll" "%HOMEDRIVE%\Program Files\xxxxxx Custom"
COPY "P:\xxxxxx Custom\ABARCODE.MDA" "%HOMEDRIVE%\Program Files\xxxxxx Custom"
COPY "P:\xxxxxx Custom\XYRLogoMenu.BMP" "%HOMEDRIVE%\Program Files\xxxxxx Custom"
COPY "P:\xxxxxx Custom\IDSLogoMenu.BMP" "%HOMEDRIVE%\Program Files\xxxxxx Custom"
@ECHO Setting up environment...
"P:\xxxxxx Custom\SetClientEnv.EXE" PackingLabels %HOMEDRIVE%
"C:\Windows\System32\Regsvr32.exe" "%HOMEDRIVE%\Program Files\xxxxxx Custom\vbsendmail.dll" /s
rem Install mswinsck.ocx if local intall.  If TS user this has already been done.
COPY "P:\xxxxxx Custom\mswinsck.ocx" "C:\WINDOWS\SYSTEM32"
"C:\Windows\System32\Regsvr32.exe" "C:\WINDOWS\SYSTEM32\MSWINSCK.OCX" /s
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 38

Expert Comment

ID: 20313934
check out this link for a tool called the Front-end Loader, which is supposed to enable you to, among other things, deploy new releases automatically.  I have never personally used it, so I can not endorse it....but it is certainly worth a look...30 day free trial ($199 to buy).
LVL 19

Expert Comment

by:Eric Sherman
ID: 20313976
I kind of do the same thing automatically each time my large multi-user applications starts up.  Rather than using a batch file I actually use a little Startup.mdb application that checks a field in the Front-End call LAST_MODIFIED.  This field is stored in a table called SetupGeneral.  When ever I make changes to the front-end I simply update this field with the current date/time then send the modified application to the client.  They simply download it into a folder on their server.  Each time the users starts their front end the startup application will check the dates in the two applications (server version and workstation version).  If a later version is on the server the user will be notified and the server version copied to their machine.  You can do all types of things with the startup application like startup notification forms, check to see if the proper directories exist, delete temp files, etc.  

All you have to do is create this function and call it using an AutoExec Macro in your startup application.  Form1 is a status form and I also display the company's logo there.  Makes for a nice presentation when opening the application.

Function CheckNewVersion()
On Error GoTo err_CheckNewVersion

DoCmd.OpenForm "Form1", acNormal
DoCmd.SetWarnings False
DoCmd.RepaintObject acForm, "Form1"

Dim dbg As DAO.PrivDBEngine
Dim rst As Recordset
Dim dbs As Database
Dim wks As Workspace
Dim varLastMod As Date
Dim varLastMod2 As Date
Dim RetVal As Double
Dim strPath As String

Dim varDrive As String
varDrive = 'T'

'Find the path to local msaccess.exe file
    strPath = ""
    If Dir("C:\Program Files\Microsoft Office\Office\MSACCESS.EXE") <> "" Then
        strPath = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE C:\MyApplications\Application1.mdb /WRKGRP " & varDrive & ":\MyApplications\Application1.mdw"
    End If
    If Dir("C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE") <> "" Then
        strPath = "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE C:\MyApplications\Application1.mdb /WRKGRP " & varDrive & ":\MyApplications\Application1.mdw"
    End If
    If strPath = "" Then
        MsgBox "Microsoft Access has not been installed in the Office or Office11 folders.  Please install first.", vbCritical, ""
        Exit Function
    End If

'Create Private DBEngine and set the SystemDB to AquaSecured workgroup file
Set dbg = New PrivDBEngine
dbg.SystemDB = "T:\MyApplications\Application1.mdw"

'Create the workspace and open the local workstation to retrieve the last modified date
Set wks = dbg.CreateWorkspace("NewWks", "Admin", "columbo")
Set dbs = wks.OpenDatabase("C:\MyApplications\Application1.mdb")

Set rst = dbs.OpenRecordset("SetupGeneral", dbOpenDynaset)
varLastMod = rst!LAST_MODIFIED

'Open the workstation on the server to retrieve its last modified date
Set dbs = wks.OpenDatabase(varDrive & ":\MyApplications\Application1.mdb")

Set rst = dbs.OpenRecordset("SetupGeneral", dbOpenDynaset)
varLastMod2 = rst!LAST_MODIFIED


'Compare the two dates
If varLastMod < varLastMod2 Then
    Forms!Form1!Label14.Caption = "A later version of the application is available and will be installed on your computer, please wait ....."
    DoCmd.RepaintObject acForm, "Form1"
    If Dir("C:\MyApplications\Application1.mdb") <> "" Then
        Kill "C:\MyApplications\Application1.mdb"
    End If
    FileCopy varDrive & ":\MyApplications\Application1.mdb", "C:\MyApplications\Application1.mdb"

End If

'Start the application
Forms!Form1!Label14.Caption = "Starting application ..."
DoCmd.RepaintObject acForm, "Form1"

RetVal = Shell(strPath, vbMaximizedFocus)

Set dbg = Nothing
Set rst = Nothing
Set dbs = Nothing
Set wks = Nothing

'close startup program

MsgBox Err.Number & "  " & Err.Description
Resume exit_CheckNewVersion

End Function

LVL 57
ID: 20314223
Yes, that is the other typical way this is handled; a "launcher" app.  That's handy becuase you can't update the database your in while it's open.  One of the links posted uses the "hand-off" technique by calling another database with the name your updating (same is done for compacts on the fly).

 Each approach has pros and cons.  The laucher is best from the user perspective as they need to do nothing.  However some type of initial install of it is required and what if you want to update the launcher itself?

  The batch file I suggested is more work for the user and clutters up the start menu if you have a lot of apps, but then I like the fact that I can change the "install" as needed (the batch file is up on the server) quickly and easily.

  It also works not only for an update, but the initial install as well.  I just need to send an e-mail to the user with a shortcut to the batch file.

  So each approach has it's good and bad points, but are are quite workable and really help with the distribution chores.


Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question