Solved

Automatic Update

Posted on 2007-11-19
6
260 Views
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?

Thanks,
0
Comment
Question by:jseaman12
6 Comments
 
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.

JimD
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 20313758
0
 
LVL 57

Accepted Solution

by:
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.

JimD.

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 OFF
@ECHO.
@ECHO.
@ECHO Checking for application directories...
@ECHO.
IF "%HOMEDRIVE%" == ""  SET HOMEDRIVE=C:
%HOMEDRIVE%
CD\
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.
@ECHO.
@ECHO Copying application files...
@ECHO.
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.
@ECHO.
@ECHO Setting up environment...
@ECHO.
"P:\xxxxxx Custom\SetClientEnv.EXE" PackingLabels %HOMEDRIVE%
"C:\Windows\System32\Regsvr32.exe" "%HOMEDRIVE%\Program Files\xxxxxx Custom\vbsendmail.dll" /s
rem
rem Install mswinsck.ocx if local intall.  If TS user this has already been done.
rem
if NOT %HOMEDRIVE% == C: GOTO EXIT
COPY "P:\xxxxxx Custom\mswinsck.ocx" "C:\WINDOWS\SYSTEM32"
"C:\Windows\System32\Regsvr32.exe" "C:\WINDOWS\SYSTEM32\MSWINSCK.OCX" /s
:EXIT
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 38

Expert Comment

by:puppydogbuddy
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).
                      http://rbytes.net/software/access-frontend-loader-review/
0
 
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)
rst.MoveFirst
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)
rst.MoveFirst
varLastMod2 = rst!LAST_MODIFIED

rst.Close
dbs.Close

'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)


exit_CheckNewVersion:
Set dbg = Nothing
Set rst = Nothing
Set dbs = Nothing
Set wks = Nothing

'close startup program
DoCmd.Quit

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

End Function

ET
0
 
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.

JimD.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now