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
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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