Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Automatic Update

Posted on 2007-11-19
Medium Priority
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 59
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 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 59
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

578 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