Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2011
    Posts
    71

    Unanswered: Launcher Application for Version Verification

    I found out today the scope of a database I'm working on is changing. I am now needing to have my Front End Database on several machines, around 50. No this doesn't necessarily mean I will be having 50 users at the same time it will be more like 5 to 10 in most cases. However, it does mean that I will have to manually install the front end on every computer (we do not have access to manually push out new programs or updates through our network).

    So, here are some options I see.

    1. Manually update each machine. (Way too time intensive)
    2. Have a place on the network where they could grab the new version of the database (Some people barely know how to type in their password, but doable)
    3. Create a launcher program that automatically checks the versions updates the access database and then runs it (Pefect, can it be done)?

    If there are more options please let me know. I have noticed there are softwares on the market that do what I want for a price. However, those are out since our network admins will not allow them to be installed.

    Question:

    For Idea #3, is this possible in Access? The basics of what I can see that I would want done is. It would compare the FE file on the network to the one on the computer if the versions do not match then it would copy the network file and overwrite the desktop file with it.

    The thought behind this is: Users are users are users and if its more than a couple of clicks there will be users that will not try.

    Thanks in advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The database in the attached file contains a class Cls_Update that does precisely that. It uses another class Cls_Version to check the version of the current database against the latest available version. In this example, Cls_Version obtains the version infos from a SQL Server but it can be easily adapted.
    Attached Files Attached Files
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    71
    Great Thanks! I'll peruse over this chunk of coding and see what I can figure out. Hope it does exactly what I need, I'll be back once I figure out how the thing works

  4. #4
    Join Date
    Oct 2011
    Posts
    71
    So, I've been looking and studying this code. Side note: I have never used 90% of the code in this database so my brain now hurts from trying to absorb all this new information. Anyways, I've googled a lot of the functions that I'm not aware of and I want to make sure I'm getting a proper understanding of how this works.

    Instead of this being a seperate database looking into the primary one, it seem this code is made to run at the beginning of the database, check the versions, update if necessary and then run the normal database?

    The biggest part of this code i'm still trying to rap my head around is the connecting to the sql server. Now I do not have a sql server as you know, but I figured if I figured that out I could figure out how to tie it to my backend database. Primarily:

    m_Version(V_LOCAL, V_FULL) = m_Version(V_Server, V_Major) & "." & _
    (and what follows)

    I understand that m_Version is defined as a string, but as far as what is in the () I cannot piece it together at this point.

    I apologize for my lack of knowledge in this area. I'm trying to get a grasp on all of this in a short time.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    "m_clsVersion" is a member variable defined as a variable of type "Cls_Version":
    Code:
    Private m_clsVersion As Cls_Version
    This class can retrieve several pieces of information concerning the version of an application: Version Number Major, Version Number Minor, Version Number Revision and Version Date. This is how Microsoft identifies its sofwares and I copied from it, e.g. Access 2003 SP3 internally is: Access 11(Major).8321(Minor).8341(Revision). The class retrieves the various version data of the local database (the Front-End) that are stored in user-defined properties of the database object, using the class "Cls_DbProperty". The names of these properties are: "VersionMajor", "VersionMinor", "VersionRevision" and "VersionDate" :
    Code:
        With clsDBProperty
            m_Version(V_LOCAL, V_MAJOR) = .Value("VersionMajor")
            m_Version(V_LOCAL, V_MINOR) = .Value("VersionMinor")
            ' The original code states V_MINOR on the next line: this is a bug, please correct.
            m_Version(V_LOCAL, V_REVISION) = .Value("VersionRevision") 
            m_Version(V_LOCAL, V_DATE) = .Value("VersionDate")
        End With
    For the data retrieved from the SQL Server, the connection string is retrieved from another property of the current database: "Jet_Server_Connect", or is passed as the argument to the function "GetVersion". A stored procedure "Get_Version_Info" is called, that returns a RecordSet with a single row and five fields: "Application_Version_Major", "Application_Version_Minor", "Application_Version_Revision", "Application_Date" and "Application_Must_Update". (two additional fields: "Source_Path" and "Destination_Path" are not used here but will be later)

    The meaning of the first fourth fields is evident while the meaning of the fifth needs an explanation. The rules for determining whether an update is necessary or not are as follows:
    - If the local Version Major is lower than the remote (sent by SQL Server) Version Major or if the local Version Minor is lower than the remote (sent by SQL Server) Version Minor, then an upgrade is mandatory.
    - If the local Version Revision is lower than the remote (sent by SQL Server) Version Revision or if the local Version Date is lower than the remote (sent by SQL Server) Version Date, then an upgrade depends on the value of the "Application_Must_Update" field of the returned RecordSet.

    The version data, both local and remote are stored in an array (m_Version), like this:
    Code:
        m_Version     | V_LOCAL (= 0)   | V_SERVER (= 1)
    ------------------+-----------------+----------------
    V_MAJOR (= 0)     |                 |
    V_MINOR (= 1)     |                 |
    V_REVISION (= 2)  |                 |
    V_DATE (= 3)      |                 |
    V_FULL ( = 4)     |                 |
    V_Full is a compound assembled with the various parts. It is mainly used for displaying version info in a help window or in the application title. example:
    V_Major = 2, V_Minor = 12, V_Revision = 4, V_Date = 20111207, V_FULL = "2.12.4-20111207".

    The constants V_SERVER, V_MAJOR, etc. are public and defined in the module "Mod_Update_Constants":
    Code:
    Public Const V_LOCAL As Integer = 0
    Public Const V_SERVER  As Integer = 1
    Public Const V_MAJOR As Integer = 0
    Public Const V_MINOR As Integer = 1
    Public Const V_REVISION As Integer = 2
    Public Const V_DATE As Integer = 3
    Public Const V_FULL As Integer = 4
    When the application starts, it instanciates the class Cls_Update and interrogates its "MustUpdate" property. If "MustUpdate" is True, the "UpdateApplication" method of the class is invoked:
    Code:
    ' Check the application version againt the server version information.
    '
        If InStr(UCase(Command), "NOUPDATE") = 0 Then
            Set clsUpdate = New Cls_Update
            With clsUpdate
                .Connect = GetParameter("Connect")
                If .MustUpdate = True Then .UpdateApplication
            End With
            Set clsUpdate = Nothing
        End If
    Basically, the "UpdateApplication" function creates a tiny database that contains the code to copy a new version of the current database from a repository folder that resides on a remote server (the source and destination names are provided by the fields "Source_Path" and "Destination_Path" that are returned by the stored procedure "Get_Version_Info"). This tiny database is then launched, the new version of the application database is copied from the server, then this new version database is started.
    Have a nice day!

  6. #6
    Join Date
    Oct 2011
    Posts
    71
    Code:
        strServer = ConnectionString
        If strServer = "" Then strServer = m_strConnect
        If strServer = "" Then strServer = clsDBProperty.Value("Jet_Server_Connect")
        If Len(strServer) > 0 Then
            Set dbsSQL = OpenDatabase("", dbDriverNoPrompt, False, strServer)
            strcommand = "dbo.Get_Version_Info '" & Left(CurrentProject.Name, InStr(CurrentProject.Name, ".") - 1) & "'"
            Set rstSQL = dbsSQL.OpenRecordset(strcommand, dbOpenSnapshot, dbSQLPassThrough)
            With rstSQL
                If Not .EOF Then
                    m_Version(V_SERVER, V_MAJOR) = !Application_Version_Major
                    m_Version(V_SERVER, V_MINOR) = !Application_Version_Minor
                    m_Version(V_SERVER, V_REVISION) = !Application_Version_Revision
                    m_Version(V_SERVER, V_DATE) = !Application_Date
                    m_varMustUpdate = CBool(!Application_Must_Update)
                    m_SourceDatabase = !Source_Path
                    m_DestinationDatabase = !Destination_Path
                End If
                .Close
            Set rstSQL = Nothing
            dbsSQL.Close
            Set dbsSQL = Nothing
        End If
        Set clsDBProperty = Nothing
            End With
    So basically, this is where all the connection and getting the version from the network file happens. It checks to see if a paramater has been passed to it through the function call. If one hasn't it then checks the m_strConnect if that still doesn't have anything it hits the db property "Jet_Server_Connection" With which I'm assuming doesn't have anything assigned to it in this version of the DB.

    If one of those 3 attempts has something in it, it then attempts to open the database. Once opened it opens the record set grabs the information and assigns it to the server prperties major, minor, revision, date, MustUpdate, sourcedatabase, destinationdatabase.

    Closes everything out and sets it all to Nothing.

    And then the comparisons can start. So if i'm right I feel as my head is coming up out of the water. Since i'm not using a SQL database, I'm assuminng I'd put a table in my Access Back End and way the code looks I would need to name that table the same as my database name and put those 7 fields in it.

    I'm still trying to play around with the code on how to get it to point to my back end.

    Thanks for all the help, I seem to have worked you pretty hard today.

  7. #7
    Join Date
    Oct 2011
    Posts
    71
    The other quick think to mention, I think, is I do see how Main() is using the clsUpdate connection function in conjunction with Mod_startup get_Parameter function to parse the .ini file and grab the server information.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's it. Here are the definitions for the table "Tbl_Version" and for a query equivalent of the stored procedure "Get_Version_Info" in a simplified "Access-style" translation:
    Code:
    CREATE TABLE Tbl_Versions
    (
        SysCounter Counter Primary Key,
        Application_Name Text(50),
        Application_Version_Major Long,
        Application_Version_Minor Long,
        Application_Version_Revision Long,
        Application_Must_Update Binary,
        Application_Date datetime,
        Source_Path Text(255),
        Destination_Path Text(255),
        Comments Text(255),
        Inactive Binary
    )
    Code:
    PARAMETERS @Application_Name Text(50);
    SELECT Top 1
       0 AS [Status],
       Application_Version_Major, 
       Application_Version_Minor, 
       Application_Version_Revision, 
       Application_Must_Update,
       Application_Date, 
       Source_Path, 
       Destination_Path
    FROM Tbl_Versions
    WHERE Application_Name = @Application_Name 
    AND Inactive = 0
    ORDER BY Application_Date DESC;
    Have a nice day!

  9. #9
    Join Date
    Oct 2011
    Posts
    71
    Thanks so much so far with your help. I have so far, been able to make the connection, bring in the server's version, check it against the local version if it needs to be updated. Now creating the new database. I want to see if I am getting the right understanding of how this works.

    I apologise for delay in response, this was a 3 day weekend for me and I attempt to keep work at work on those weekends

    The updateApplication Function is called, which then calls
    CreateApplicationFile
    CreateApplicationObject
    CompleteApplicationModule

    CreateApplicationFile
    Delets the Database File
    Creates a New DatabaseFile in the same spot

    CreateApplicationObject
    It creates a new instance of access

    Then it does some error checking. Checks to make sure there is a source file and that it matches the name of the current project.

    It then checks to make sure there is something in the destination variable.

    Part of the check does confuse me,
    If Len(SourceDatabase) Then

    Len() returns a number so it would be something like
    If 13 Then

    There is no comparison so how can there be a true and false?

    After the check, one puts copys the information into the new access instance, the other copies it directly into the destination database, though I'm not sure exactly where the difference is in this.

    CompleteApplicationModule
    Helps Create the Form Screen telling people the file is updating.


    The largest issue I am having is seeing where the user's database file is deleted and a new one is put in its place. I see a lot of code that alludes to it, but I guess i'm being dense and just can't put it together in my head.

    On the Kill function, how does it delete a database that your currently running, or is this update function an application outside of the primary database file? Sorry for my slow learning, I am truely trying to understand this, instead of just copy and pasting.

  10. #10
    Join Date
    Oct 2011
    Posts
    71
    I'll make a quick note of something I forgot to mention. I am able to create the Update_DatabaseName File, however only a couple of modules show up in it, none of the major forms/ queries/ tables from my main file copy over.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by ericx1 View Post
    Part of the check does confuse me,
    If Len(SourceDatabase) Then

    Len() returns a number so it would be something like
    If 13 Then

    There is no comparison so how can there be a true and false?
    This:
    Code:
    If Len(SourceDatabase) Then
    is a shortcut for:
    Code:
    If Len(SourceDatabase) > 0 Then
    This is possible because any non-zero expression is evaluated to True in this case. I normally use the canonical form (second expression), but I guess that I was tired or lazy the day I wrote this module

    Quote Originally Posted by ericx1 View Post
    On the Kill function, how does it delete a database that your currently running, or is this update function an application outside of the primary database file? Sorry for my slow learning, I am truely trying to understand this, instead of just copy and pasting.
    The trick is that the the first database (the ancient version) is not running any more when the code kills it. At the end of the function "UpdateApplication", you find these lines:
    Code:
        CreateApplicationObject strUpdateTool, "AutoExec", acMacro, "", "M_Update_AutoExec"
        Shell "msaccess.exe " & Chr(34) & strUpdateTool & Chr(34), vbMaximizedFocus
        Application.Quit
    - The first line creates an "AutoExec" macro is the "Updater" database that's being created. This means that when the "Updater" database is open, this macro is executed automatically.
    - The second line actually opens (starts) the "Updater" database (which executes its "AutoExec" macro).
    - The third line closes the current database. The process of closing the "old" database is faster than the process of starting the "Updater" one which needs a longer time to reach the point where it kills the "old" one.

    In the new database ("Updater") you have:
    1. A Macro "AutoExec" which contains:
    Action: OpenForm
    Form Name: F_Update
    View: Form
    Window Mode: Dialog
    Which is equivalent to the VBA instruction:
    Code:
    DoCmd.OpenForm "F_Update", acNormal ,,,,acDialog
    This macro is executed automatically when the database is open.

    2. A form "F_Update" in which:
    Code:
    Private Sub Form_Load()
    
        Dim strCaption As String
        
        Set m_DBProperty = New Cls_DbProperty
        strCaption = m_DBProperty.Value("TEXTLABEL1")
        If Len(strCaption) > 0 Then Me.Label_1.Caption = m_DBProperty.Value("TEXTLABEL1")
        strCaption = m_DBProperty.Value("TEXTLABEL2")
        If Len(strCaption) > 0 Then Me.Label_2.Caption = m_DBProperty.Value("TEXTLABEL2")
        
    End Sub
    This code set the captions of the labels that will display a message explaining what's going on.

    However this form has its "TimerInterval" property set to 2500 (2,5 seconds). After this delay, the following procedure (also in the class module of the form) is called:
    Code:
    Private Sub Form_Timer()
    
        
        Dim objFSO As Object
        Dim strSource As String
        Dim strdestination As String
        
        Me.TimerInterval = 0
        strSource = m_DBProperty.Value("SOURCEFILE")
        strdestination = m_DBProperty.Value("DESTINATIONFILE")
        If Len(strSource) > 0 And Len(strdestination) > 0 And strSource <> strdestination Then
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            DoEvents
            objFSO.CopyFile m_DBProperty.Value("SOURCEFILE"), m_DBProperty.Value("DESTINATIONFILE"), True
            Set objFSO = Nothing
            Shell "msaccess.exe " & Chr(34) & m_DBProperty.Value("DESTINATIONFILE") & Chr(34) & "/cmd NOUPDATE", vbMaximizedFocus
            Application.Quit
        Else
            DoCmd.Close acForm, Me.Name
        End If
    
    End Sub
    This procedure actually copies the new version of the application database (objFSO.CopyFile...) retrieving the source and destination path from two properties that were assigned to the Database object when the "Updater" database was created (I could have used two constants for the same purpose).

    The timer is used to be sure that a reasonable delay separates the moment the former version on the database is closed and deleted from the moment the new version is copied.

    The new copy of the application database is then started and the "Updater" database is closed.

    Although this is a rather marginal use of this technique, notice that you can actually move the application database from one folder to another, by specifying a different path for the "DESTINATIONFILE" property than the path where the old version of the application is located. That's the reason why both pathes are retrieved from the server in the function "GetVersion" that uses two public properties of the class "Cls_Version":
    Code:
    Private Function GetVersion(Optional ByVal ConnectionString As String)
        
        Set m_clsVersion = New Cls_Version
        m_clsVersion.Connect = m_strConnect
        If m_clsVersion.Version(V_LOCAL, V_MAJOR) < m_clsVersion.Version(V_SERVER, V_MAJOR) Then
            m_varMustUpdate = True
        ElseIf m_clsVersion.Version(V_LOCAL, V_MINOR) < m_clsVersion.Version(V_SERVER, V_MINOR) Then
            m_varMustUpdate = True
        Else
            If m_clsVersion.Version(V_LOCAL, V_REVISION) < m_clsVersion.Version(V_SERVER, V_DATE) Then m_varMustUpdate = m_clsVersion.MustUpdate
        End If
        m_SourceDatabase = m_clsVersion.SourceDatabase
        m_DestinationDatabase = m_clsVersion.DestinationDatabase
            
    End Function
    Have a nice day!

  12. #12
    Join Date
    Oct 2011
    Posts
    71
    Alrighty, so must say THANK YOU, you took a lot of time and helped me through this mess. The updater, I believe is working now, I just have to fight the Vista/Network security headache I think.

    It keeps getting into an infinite loop on having to kill macros because I have to enable the access settings and Open the content because its not from a trusted source. It does update the file though so thanks once again.

    Anyways, there is alot more in this code that I still have to learn, but you've given me the tools to continue on. Thanks!

  13. #13
    Join Date
    Oct 2011
    Posts
    71
    Okay so I lied, I was able to get around some of the network security issues and I'm still haveing some issues with it updating. I'll give some more info in a little while when I get a chance to look at whats going on.

  14. #14
    Join Date
    Oct 2011
    Posts
    71
    I'm about to delve into the code and see exactly whats going on, however here is the basic issue. It seems to be stuck in an infinite update loop. On the second iteration of the loop it kills itself because the database is already open and updated mind you. However, it does not seem to be storing the updated information in away the program is liking it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •