Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Automatic Update

Posted on 2007-11-19
6
Medium Priority
?
279 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
[X]
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
6 Comments
 
LVL 58
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20313758
0
 
LVL 58

Accepted Solution

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Technology Partners: 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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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. …

604 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