Launcher Application for Version Verification

ericx1 · #1 (**permalink**) 12-29-11, 14:23

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

Sinndho · #2 (**permalink**) 12-29-11, 18:10

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.

ericx1 · #3 (**permalink**) 12-30-11, 09:05

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

ericx1 · #4 (**permalink**) 12-30-11, 12:12

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.

Sinndho · #5 (**permalink**) 12-30-11, 14:43

"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.

ericx1 · #6 (**permalink**) 12-30-11, 16:23

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.

ericx1 · #7 (**permalink**) 12-30-11, 16:46

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.

Sinndho · #8 (**permalink**) 12-30-11, 16:58

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;

ericx1 · #9 (**permalink**) 01-03-12, 14:52

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.

ericx1 · #10 (**permalink**) 01-03-12, 15:45

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.

Sinndho · #11 (**permalink**) 01-03-12, 15:46

Quote:

Originally Posted by ericx1

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

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

ericx1 · #12 (**permalink**) 01-04-12, 11:03

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!

ericx1 · #13 (**permalink**) 01-04-12, 11:27

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.

ericx1 · #14 (**permalink**) 01-04-12, 11:56

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech