?
Solved

Automating Deployment of an access MDE.

Posted on 2008-06-26
6
Medium Priority
?
334 Views
Last Modified: 2013-11-27
Right now my process for deploying updates to a database called chm_tracker.mdb
to an access database includes
1.- Finishing up all the modifications and verifying that it works.
2.- Decompile the chm_Tracker.mdb
3.- Compact and Repair database
4.- ReCompile the database
5.- Make an MDE from the database  that gets saved in a network location called "j:\shared\topio\shipping\"
6.- Notify all the users in the outlook list TrackerUpdates that a new version has been place on the server

This is what  I want to automatize but I don't know if I can do this from inside the tracker database
or jsut create anew database with a very simple form to perform this activity.
0
Comment
Question by:TOPIO
  • 4
  • 2
6 Comments
 
LVL 11

Expert Comment

by:RgGray3
ID: 21879806
Well....  you can....  with the help of another MDB

I don't make an mde but distribute a "locked" mdb...  (I like to be able to unlock and debug on a users machine from time to time...  alot also depends on your environment)

That asside...  the distribution process will be the same

OK you have a distribution point...
place a Version file in your front end.  This can take 1 of many forms....  I use a programmers notes table that I place my release notes into  (you could also have a table with a single field and record with your version number in it.. or any number of other versioning methods....)

The trick is you want to link to this table in your distribution location...    Now do a comparison between your local version table and the linked distribution one.
I check record count on each table and if the one at the distribution point has more records...  its newer....  

Now what to do to copy the file...  you are inside a database and you want to copy a new copy over the one your in....  won't work...  (sort of like cutting off the branch of a tree that your sitting on)

Use a third file.  I use a temp mdb (where I have all my temp tables located in)   Lets call the users FE as A  the distribution file as B and the temp as C

A checks B and realizes it is newer...  A opens C and closes A,  C copies B over A,  C opens A and closes C  

You are now in the new front end in the original location

Easy right !


Well I have cut in my code for your review...  
Sorry I use alot of home grown stuff and may not have included some of the  supporting routines
but trace through the code and my convoluded explaination above and you should get the trick

Start with the function ->  IsTherAnUpdate,  and trace the logic

Rich
Option Compare Database
Option Explicit
'-------------------------------------------------
' Rather than use my global vars for the msgbox,
' Create local variable for the module
' will make it easier to transport to other apps
'-------------------------------------------------
Dim strMbMessage   As String
Dim intMbStyle     As Integer
Dim strMbTitle     As String
Const cstrCredentials  As String = " /wrkgrp w:\Data\AUA\Security\AUA_Workgroup.mdw "
 
Sub ANewerVersionHasBeenPosted()
'-------------------------------------------------------------------------------
' Run from the Frontend program when a newer version is found
' at the distribution location
'-------------------------------------------------------------------------------
On Error GoTo Err_ANewerVersionHasBeenPosted
    
    '-------------------------------------------------------------------------------
    'MsgBox Code:
    '       Icon: i-Information Message
    '    Buttons: (OK) Default: (OK)  MsgBox function returns: (OK) = 1
    '-------------------------------------------------------------------------------
    DoCmd.Beep
    strMbMessage = "       There is an update to this program ready for your use." & _
                    vbCrLf & vbCrLf & _
                    "Pressing OK will install the update for you and restart the program." & _
                    vbCrLf & vbCrLf & vbCrLf & _
                    "                The process should take less than a minute." & _
                    vbCrLf & vbCrLf & _
                    "                                  PLEASE BE PATIENT !"
                    
    intMbStyle = vbInformation + vbOKOnly + vbDefaultButton1
    strMbTitle = "Application Update Ready"
    
    MsgBox strMbMessage, intMbStyle, strMbTitle
    '-----------------------------------------------
    ' I don't give them a choice
    '-----------------------------------------------
    GetNewTempMDB
    RunUpdateForFrontEnd
   
    DoCmd.Quit 'close this application so the copy can happen
    
Exit_ANewerVersionHasBeenPosted:
    Exit Sub
 
Err_ANewerVersionHasBeenPosted:
    Call ErrorLog(Err.Description, _
                  Err.Number, _
                  "uSys_VersionManagementControl_bas", _
                  "ANewerVersionHasBeenPosted")
                  
    Resume Exit_ANewerVersionHasBeenPosted
 
End Sub
 
Private Sub RunUpdateForFrontEnd()
'-------------------------------------------------------------------------------
' Run from the Front End
' Looks up the Control MDB name and shells out to open the program
' In my application this is the Temp.mdb
' located in the same directory as the front end
'-------------------------------------------------------------------------------
On Error GoTo Err_RunUpdateForFrontEnd
Dim htask               As Variant
Dim strAppPath          As String
Dim strExecutionMacro   As String
Dim strCopyControlMdb   As String
 
 
    strCopyControlMdb = tLookup("cstrValue", _
                                "uSys_ap_ApplicationConstants_tbl", _
                                "ConstantName = 'ControlMDB'")
                                
    strAppPath = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE "
    strExecutionMacro = " /xVersionControl_UpdateLocalMDB_mcr"
    
    htask = Shell(strAppPath & " " & _
                  GetPathPart(CurrentDb.Name) & strCopyControlMdb & _
                  cstrCredentials & " /User RichG /Pwd misiu " & _
                  strExecutionMacro, 1)  'Run the  Batch File
 
Exit_RunUpdateForFrontEnd:
    Exit Sub
 
Err_RunUpdateForFrontEnd:
    Call ErrorLog(Err.Description, _
                  Err.Number, _
                  "uSys_VersionManagementControl_bas", _
                  "RunUpdateForFrontEnd")
                  
    Resume Exit_RunUpdateForFrontEnd
 
End Sub
 
Function ReRunOrigApp()
'-------------------------------------------------------------------------------
' Run from the Control (temp) program
' Shells out to open the new copy of the initial Front end application
'-------------------------------------------------------------------------------
On Error GoTo Err_ReRunOrigApp
Dim htask           As Variant
Dim strAppPath      As String
Dim strTargetFile   As String
 
    strTargetFile = tLookup("cstrValue", _
                            "uSys_ap_ApplicationConstants_tbl", _
                            "ConstantName = 'TargetProgMDB'")
                            
    strAppPath = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE "
    htask = Shell(strAppPath & " " & GetPathPart(CurrentDb.Name) & strTargetFile & cstrCredentials, 3)
    DoCmd.Quit
    
Exit_ReRunOrigApp:
    Exit Function
 
Err_ReRunOrigApp:
    Call ErrorLog(Err.Description, _
                  Err.Number, _
                  "uSys_VersionManagementControl_bas", _
                  "ReRunOrigApp")
                  
    Resume Exit_ReRunOrigApp
 
End Function
 
Function GetNewProgMDB()
'-------------------------------------------------------------------------------
' Run from the Control (temp) program
' Copies the Front end application from the distribution point
' To the users local machine
'-------------------------------------------------------------------------------
On Error GoTo Err_GetNewProgMDB
Dim strSourceFile   As String
Dim strTargetFile    As String
 
    strSourceFile = tLookup("cstrValue", _
                            "uSys_ap_ApplicationConstants_tbl", _
                            "ConstantName = 'SourceProgMDB'")
                            
    strTargetFile = GetPathPart(CurrentDb.Name) & GetNamePart(strSourceFile)
 
TryAgain:
 
    ShellXCopy strSourceFile, strTargetFile
    ReRunOrigApp
    
Exit_GetNewProgMDB:
    Exit Function
 
Err_GetNewProgMDB:
    Call ErrorLog(Err.Description, _
                  Err.Number, _
                  "uSys_VersionManagementControl_bas", _
                  "GetNewProgMDB")
                  
    Resume Exit_GetNewProgMDB
 
End Function
 
Function GetNewTempMDB()
'-------------------------------------------------------------------------------
' Run from the Front end program
' Copies the Temp or Control application from the distribution point
' This will insure that they have the most up to date temp file
' which will also control the updating of the Front end
'-------------------------------------------------------------------------------
On Error GoTo Err_GetNewTempMDB
Dim strSourceFile   As String
Dim strTargetFile   As String
 
    strSourceFile = tLookup("cstrValue", _
                            "uSys_ap_ApplicationConstants_tbl", _
                            "ConstantName = 'SourceTempMDB'")
    
    strTargetFile = tLookup("cstrValue", _
                            "uSys_ap_ApplicationConstants_tbl", _
                            "ConstantName = 'TargetTempMDB'")
 
TryAgain:
    ShellXCopy strSourceFile, GetPathPart(CurrentDb.Name) & strTargetFile
    
Exit_GetNewTempMDB:
    Exit Function
 
Err_GetNewTempMDB:
    If Err.Number = 70 Then
        Resume TryAgain
    Else
        Call ErrorLog(Err.Description, _
                      Err.Number, _
                      "uSys_VersionManagementControl_bas", _
                      "GetNewTempMDB")
        Resume Exit_GetNewTempMDB
    End If
 
End Function
 
 
'The function to check the records in the front end and distribution mdb's
'I use a home grown TSql replacement for DCount  you can use DCount
Public Function ThereIsAnUpdate() As Boolean
   ThereIsAnUpdate = tCount("*", "uSys_tb_ProgrammersReleaseNotes_tbl", "") _
                   < tCount("*", "uSys_tb_ProgrammersReleaseNotes_tbl_Distribution", "")
End Function
 
Public Function IsThereAnUpdate() As Boolean
    
    '--------------------------------------------------------------------------------
    ' Check for an update
    '--------------------------------------------------------------------------------
    If ThereIsAnUpdate Then
        '-------------------------------------------------------------------------------
        'MsgBox Code:
        '       Icon: !-Warning Message
        '    Buttons: (OK) Default: (OK)  MsgBox function returns: (OK) = 1
        '-------------------------------------------------------------------------------
        ANewerVersionHasBeenPosted
    End If
    
    If Application.GetOption("Use Row Level Locking") Then
        '---------------------------------------------------
        'This is good
        '---------------------------------------------------
    Else
        '---------------------------------------------------
        'Make a note of it in the error log and turn it on
        '---------------------------------------------------
        gstrSql = "INSERT INTO uSys_Log_Errors_tbl ( errDate, errNumber, errDescription, errObjName, errRoutine, errUserName, errWrkStation ) " & _
                  "SELECT Now(), 0, 'Workstation Not set to Record Level Locking', 'Application', 'Startup', CurrentUser(), GetMachineName();"
        RunSqlNoWait gstrSql
        
        Application.SetOption "Use Row Level Locking", True
    End If
 
End Function

Open in new window

0
 
LVL 11

Expert Comment

by:RgGray3
ID: 21879829
Oh...  you must put this code in both the Application MDB and the Temp mdb....  
since the Temp picks up the process in the middle  and I actually update the temp mdb which may have changes too

So the updated logic becomes

Working FE copies new Temp MDB
                                     Opens new Temp MDB while closing self
New Temp MDB copies New FE from Distribution to Original location
                                    Opens New FE While closing self


               
                                     
0
 
LVL 10

Author Comment

by:TOPIO
ID: 21902654
RgGray3:
I do think that your solution is great I'm only hesitant to do such a extensive surgery to the FE.
An also you got me thinking about how nice it would be if my "dear" users had all the full version
of Access installed in their computers, if that were the case I could distribute a locked version of the MDB instead of just an MDB ( I could also distribute an MDB but the end result is the same there are too many things you can't do editing wise with the run time version).

IF I understand your propossal correctly  (which is a big If ) I would have a table called tblVersion with just one record called version which is a incremental number.
And upon opening the FE the system would perform the update?


0
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!

 
LVL 11

Accepted Solution

by:
RgGray3 earned 2000 total points
ID: 21903882
Simplified...
The Trigger...
Have 1 table with 1 record which is your version number   IE: Call it ApplicationVersionNumber_tbl   with a single record, single field with a value  ex: 1.21
This table would of course exist in your distribution fe
Link to this table at your distribution point  by default it will be called ApplicationVersionNumber_tbl1
Rename it ApplicationVersionNumber_Distribution

Now choose you poison...    RecordSet, Query or Dlookup  
retrieve the value from the local table
retrieve the value from the remote table
Compare them
if the value in the remote table is larger...   the distribution copy is newer than the local copy...  trigger the update

Now when you update your FE  you change its value to 1.22

Your user still has version 1.21    
the update is triggered

The only thing simpler would be write a batch file that copies the file from the distribution point to the local machine, create a shortcut to it and ...
tell the users ...   Click the stupid shortcut

There really is no surgery to the FE...   though you will have to comb through the attached code and replace my configuration routines with hard coded FullPathFileNames

ie  strCopyControlMdb = tLookup("cstrValue", _
                                "uSys_ap_ApplicationConstants_tbl", _
                                "ConstantName = 'ControlMDB'")
would be replaced with
 strCopyControlMdb = "c:\YourPath\YourFileName.mdb"

copy the code to a standard module
Create a "Temp MDB"
Copy the same code to a standard module
replace my Tlookups with hardcoded references to your

My code is setup to use a configuration table and I pull the values from there...  so I can use the same code in multiple appliations ...  don't let that confuse you...
If you only have 1 application to manage...  hard code it
0
 
LVL 11

Expert Comment

by:RgGray3
ID: 21903923
One last thing to consider is where to put the testing code...

I do it in the on open of my splash screen...  
If there is an update I trigger the copy...

0
 
LVL 10

Author Comment

by:TOPIO
ID: 21934180
OK LET ME TRY IT THIS WAY
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 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