Solved

Automating Deployment of an access MDE.

Posted on 2008-06-26
6
311 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
[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
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

730 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