Solved

Automating Deployment of an access MDE.

Posted on 2008-06-26
6
268 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 11

Accepted Solution

by:
RgGray3 earned 500 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

What Security Threats Are You Missing?

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

14 Experts available now in Live!

Get 1:1 Help Now