Check for newest DB version? (1 Viewer)

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
(Access 2013 64 bit split database)

So... Periodically I will made modifications to the functionality and appearance of my database to make it more user friendly and whatnot.

I know I can't do anything about the previous versions I released but for future versions I would like to know what version my front end users are using.

Is this just a matter of adding a table called something like tbl_currentversion and having the value I make up... then put in some code on the on load of their main form to check that?

I know that since I have released maybe 3 new versions in a few weeks, some of my users are not the sharpest pencils in the box and if someone starts having errors I would like to know if they are using the latest build that I sent everyone.

Does this sound easy? Can someone help me with the code for this?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:44
Joined
Feb 28, 2001
Messages
26,996
I approached this with a little bit of a variation. I had a version table, but since everyone had to go through my startup form, I used it to absolutely verify which build-version they were running.

When I changed anything, the next version of the FE would be updated anyway, right? So part of my "make a new version" protocols included updating a constant version-id string declared in the main declaration area of the startup form. I had a version compatibility table that resided in the shared BE, which of course CAN only be one version - 'cause there is only one file. The BE's table listed ALL past versions. The changes I made were listed in a child table of the version table, so I could document changes associated with each version.

Since I had a formal protocol to follow, I always maintained a version number that increased in a predictable way. The FE version table had two versions of interest. The first was the highest version seen so far. The second was one that I would manually update during changes. It was the LOWEST version that could run against the common BE file. If you were too old, the FE immediately popped up a message telling you that an update was mandatory. If you were old but not too old, you were given a notice that while you COULD keep on running, you probably should update in order to get bug fixes.

I knew about the auto-updater method, a batch job to download and then launch the latest, greatest version of the FE, but it turns out our security guys were foaming-at-the-mouth paranoid about that sort of thing. And when you are working with the U.S. Dept. of Defense, the level of paranoia is palpable. So I had to throw it onto my users to voluntarily drag new versions down from the shared area.

At least the opening form knew enough to verify that I wasn't running the FE directly from the shared folder. THAT got you a really nasty message and I got a quick CDO-based e-mail that would lead to a visit and a discussion of why to not do that again.
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
See the utility I posted in this thread https://access-programmers.co.uk/forums/showthread.php?t=293847


I just use the fe_version_number in the tbl-fe_version table value on the switchboard form.
HTH

Gasman,

First... thank you for pointing me to your utility. Since I am not a system administrator on our network and my database users are in several different states, I cannot use the utility you suggested due to not being able to put the exe on all of their computers in all of their logins.

But I ran into this thread by Frothingslosh
https://www.access-programmers.co.uk/forums/showthread.php?t=265491
That looks promising for me.

Is anyone familiar with this method?

I followed the instructions but there was some missing information.

It did not specify if the code was meant to be a stand alone "Module" (it didn't specify a name for the module if so) or if it went in the code of an existing form such as the main form in my DB in the on load event or the on open.

I am assuming the latter because otherwise there is nothing to execute this code otherwise.

Am I correct in thinking this?
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
So the first bit of code cannot be put in the on load or on open because it is a public function and the second is its own sub.

I have no idea where to put this code

Confused
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 12:44
Joined
Sep 21, 2011
Messages
14,038
Firstly it is not my utility, I just found it easy to use.
AFAIK there is no exe?, it is just set of tables to import.

The code does create a batch file that copies the latest version to the user FE.
You put the latest accde in the required location depending on what you have entered in the tables. I keep mine with the BE.

When the user opens the FE, they get advised they have an old version. That gets updated and reopens as the new FE. Very slick.

The only drawback (if you want to call it that) is that you have to issue the new FE manually the very first time. After that it does it all itself.

HTH


Gasman,

First... thank you for pointing me to your utility. Since I am not a system administrator on our network and my database users are in several different states, I cannot use the utility you suggested due to not being able to put the exe on all of their computers in all of their logins.

But I ran into this thread by Frothingslosh
https://www.access-programmers.co.uk/forums/showthread.php?t=265491
That looks promising for me.

Is anyone familiar with this method?

I followed the instructions but there was some missing information.

It did not specify if the code was meant to be a stand alone "Module" (it didn't specify a name for the module if so) or if it went in the code of an existing form such as the main form in my DB in the on load event or the on open.

I am assuming the latter because otherwise there is nothing to execute this code otherwise.

Am I correct in thinking this?
 

isladogs

MVP / VIP
Local time
Today, 12:44
Joined
Jan 14, 2017
Messages
18,186
So the first bit of code cannot be put in the on load or on open because it is a public function and the second is its own sub.

From looking in my codes the functions go in a "Module" and the subs go in the form code events... correct?

If so... he says the second code should go in the same module as the first.

Confused

NO!

Functions are used to obtain an output.
Subs are procedures to run one or more actions
Either of these can be called from Form_Load events

Sub or Functions can go in form modules but if so they can only be called from that form.
If you want to reuse code across your database, subs & functions need to go into general modules

Note: many programmers including myself will at times use functions when a sub would do. Some programmers only use functions for reusable code

Does that help or confuse you more?

P.S You could also look at my Access FE updater in the same thread as Gasman's code though it would need adapting for your needs
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
Firstly it is not my utility, I just found it easy to use.
AFAIK there is no exe?, it is just set of tables to import.

The code does create a batch file that copies the latest version to the user FE.
You put the latest accde in the required location depending on what you have entered in the tables. I keep mine with the BE.

When the user opens the FE, they get advised they have an old version. That gets updated and reopens as the new FE. Very slick.

The only drawback (if you want to call it that) is that you have to issue the new FE manually the very first time. After that it does it all itself.

HTH

My apologies.... I saw this int he instructions:
Now copy the supplied FE Updater.exe program file to each PC that is using the database and ensure that it is copied into the same folder as the database front-end file. When the user wants to run their database they will need to run the FE Updater.exe program instead of the database front-end file. If the users normally run the database from a short cut icon on the desk-top then you will need to change the short cut path so that it runs the FE Updater.exe program instead.
And there is an executable file in the zip.

Never the less I still cannot use this one and was hoping to use the code in the thread I found... I will ask for help in that thread.
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
NO!

Functions are used to obtain an output.
Subs are procedures to run one or more actions
Either of these can be called from Form_Load events

Sub or Functions can go in form modules but if so they can only be called from that form.
If you want to reuse code across your database, subs & functions need to go into general modules

Note: many programmers including myself will at times use functions when a sub would do. Some programmers only use functions for reusable code

Does that help or confuse you more?

P.S You could also look at my Access FE updater in the same thread as Gasman's code though it would need adapting for your needs

I tried but the first function did not work in the main form because even if I started a new sub like this:
Code:
Private Sub Form_Load()
Public Function CheckFrontEnd() As Integer
' ************************************************************
' Created by       : Scott L Prince
' Parameters       : None
' Result           : Determines if backend can be reached, and if front end is the current version.
' Returns          : 0 - Misc Error
'                  : 1 - No current version found in Version Manager file
'                  : 2 - Front end being run from master location
'                  : 3 - Master file path not found in Version Manager file
'                  : 999 - Front end current
' Date             : 5-30-14
' Remarks          : Based on previously-existing code by Bob Larson posted at StackOverflow
' Changes          :
' ************************************************************
Dim FrontEndVersion As String               'Front end version number
Dim MasterVersion As String                 'Master version number
Dim MasterPath As String                    'Location of the master FE file
Dim BatchPath As String                     'Location of the batch file that does the actual update
 
    'Determine master version number.
    MasterVersion = DLookup("fe_version_number", "tbl-version_fe_master")
 
    'Determine if the database containing the version information can be accessed.
    Select Case MasterVersion
        Case ""                             'Master version number cannot be found, or backend/version manager is missing.
 
            CheckFrontEnd = 1
 
        Case Else                           'Version data found.
 
            'Look up the path for the master file location.
            MasterPath = DLookup("s_masterlocation", "tbl-version_master_location")
 
            'Determine if the master file is being run rather than a local copy.
            If Nz(MasterPath, "") = "" Then
 
                'No master path was found.  Return error value.
                CheckFrontEnd = 3
 
            ElseIf MasterPath = CurrentProject.Path Then
 
                'The actual master file is the one being executed.
                CheckFrontEnd = 2
 
            Else
 
              'Master file path found and is not being run.  Determine the version number of the active front end.
                FrontEndVersion = DLookup("fe_version_number", "tbl-fe_version")
 
              'Compare the version number in the front end to the master version number.
                Select Case (FrontEndVersion = MasterVersion)
 
                    Case True           'Version numbers match.
 
                        'Return "OKAY" result.
                        CheckFrontEnd = 999
 
                    Case False         'Version numbers do not match.
 
                      'Create the path for the batch file used to update the front end.
                        BatchPath = CurrentProject.Path & "\UpdateDbFE.cmd"
 
                        'Check for an already-existing BatchPath, and kill it if it exists.
                        If Dir(BatchPath) <> "" Then Kill BatchPath
 
                        'Notify the user that the application will update.
                        MsgBox "UPDATE REQUIRED" & vbCrLf & vbCrLf & _
                        "Your program is not the latest version." & vbCrLf & vbCrLf & _
                        "The front-end needs to be updated. The program will now close and then should reopen automatically.", _
                        vbCritical
 
                        'Execute 'UpdateFrontEnd'.
                        UpdateFrontEnd CurrentProject.Path & "\" & CurrentProject.Name, MasterPath
 
                End Select
            End If
    End Select
 
End Function
End Sub
It doesn't like it because it keeps highlighting the
Code:
Private Sub Form_Load()
stating it is expecting an End Sub. This doesn't look right because Private Sub and then Public Function doesn't look right.
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
So this should be in a Module and then needs to call that module in the on load event of whatever form I choose?
 

isladogs

MVP / VIP
Local time
Today, 12:44
Joined
Jan 14, 2017
Messages
18,186
That's not the way to do things.
You can't embed one procedure inside another.
Instead you call one from the other.

In this case, put you CheckFrontEnd function in a standard module so it can be called from anywhere.

Then use code like this

Code:
Private Sub Form_Load()

CheckFrontEnd

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:44
Joined
Sep 21, 2011
Messages
14,038
No, it is I that needs to apologise, that is the wrong utility.:banghead:
The help shows pdh_software as the author.?

I'll need to find what I actually am using, but it is by Bob Larsen..

FWIW here is the documentation.

The utility can be found from the first link at https://btabdevelopment.com/free-access-tools/
Sorry for leading you astray.




My apologies.... I saw this int he instructions:

And there is an executable file in the zip.

Never the less I still cannot use this one and was hoping to use the code in the thread I found... I will ask for help in that thread.
 

Attachments

  • Documentation_Adding_Auto-Update_to_Frontends.zip
    128.1 KB · Views: 241
Last edited:

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
That's not the way to do things.
You can't embed one procedure inside another.
Instead you call one from the other.

In this case, put you CheckFrontEnd function in a standard module so it can be called from anywhere.

Then use code like this

Code:
Private Sub Form_Load()

CheckFrontEnd

End Sub

That really what I was thinking when you posted that.

I did this... I created a module and named is CheckFrontEnd with this code:
Code:
Option Compare Database

Public Function CheckFrontEnd() As Integer
' ************************************************************
' Created by       : Scott L Prince
' Parameters       : None
' Result           : Determines if backend can be reached, and if front end is the current version.
' Returns          : 0 - Misc Error
'                  : 1 - No current version found in Version Manager file
'                  : 2 - Front end being run from master location
'                  : 3 - Master file path not found in Version Manager file
'                  : 999 - Front end current
' Date             : 5-30-14
' Remarks          : Based on previously-existing code by Bob Larson posted at StackOverflow
' Changes          :
' ************************************************************
Dim FrontEndVersion As String               'Front end version number
Dim MasterVersion As String                 'Master version number
Dim MasterPath As String                    'Location of the master FE file
Dim BatchPath As String                     'Location of the batch file that does the actual update
 
    'Determine master version number.
    MasterVersion = DLookup("fe_version_number", "tbl-version_fe_master")
 
    'Determine if the database containing the version information can be accessed.
    Select Case MasterVersion
        Case ""                             'Master version number cannot be found, or backend/version manager is missing.
 
            CheckFrontEnd = 1
 
        Case Else                           'Version data found.
 
            'Look up the path for the master file location.
            MasterPath = DLookup("s_masterlocation", "tbl-version_master_location")
 
            'Determine if the master file is being run rather than a local copy.
            If Nz(MasterPath, "") = "" Then
 
                'No master path was found.  Return error value.
                CheckFrontEnd = 3
 
            ElseIf MasterPath = CurrentProject.Path Then
 
                'The actual master file is the one being executed.
                CheckFrontEnd = 2
 
            Else
 
              'Master file path found and is not being run.  Determine the version number of the active front end.
                FrontEndVersion = DLookup("fe_version_number", "tbl-fe_version")
 
              'Compare the version number in the front end to the master version number.
                Select Case (FrontEndVersion = MasterVersion)
 
                    Case True           'Version numbers match.
 
                        'Return "OKAY" result.
                        CheckFrontEnd = 999
 
                    Case False         'Version numbers do not match.
 
                      'Create the path for the batch file used to update the front end.
                        BatchPath = CurrentProject.Path & "\UpdateDbFE.cmd"
 
                        'Check for an already-existing BatchPath, and kill it if it exists.
                        If Dir(BatchPath) <> "" Then Kill BatchPath
 
                        'Notify the user that the application will update.
                        MsgBox "UPDATE REQUIRED" & vbCrLf & vbCrLf & _
                        "Your program is not the latest version." & vbCrLf & vbCrLf & _
                        "The front-end needs to be updated. The program will now close and then should reopen automatically.", _
                        vbCritical
 
                        'Execute 'UpdateFrontEnd'.
                        UpdateFrontEnd CurrentProject.Path & "\" & CurrentProject.Name, MasterPath
 
                End Select
            End If
    End Select
 
End Function

Private Sub UpdateFrontEnd(ByVal LocalFilePath As String, _
                 ByVal MasterFileFolder As String)
 
Dim BatchFile As String
Dim MasterFilePath As String
Dim Restart As String
 
    'Set the file name and location for the file to copy
    MasterFilePath = MasterFileFolder & "\" & CurrentProject.Name
    'Set the file name of the batch file to create
    BatchFile = CurrentProject.Path & "\UpdateDbFE.cmd"
    'Set the restart file name
    Restart = """" & LocalFilePath & """"
 
'Create the batch file
    Open BatchFile For Output As #1
    Print #1, "@Echo Off"
    Print #1, "ECHO Deleting old file..."
    Print #1, ""
    Print #1, "ping 127.0.0.1 -n 5 -w 1000 > nul"
    Print #1, ""
    Print #1, "Del """ & LocalFilePath & """"
    Print #1, ""
    Print #1, "ECHO Copying new file..."
    Print #1, "Copy /Y """ & MasterFilePath & """ """ & LocalFilePath & """"
    Print #1, ""
    Print #1, "ECHO Starting Microsoft Access..."
    Print #1, "START /I " & """MSAccess.exe"" " & Restart
    Close #1
 
  'Run the batch file
    Shell BatchFile
 
  'Close the current application so batch file can execute.
    DoCmd.Quit
End Sub

Of course followed all the instructions in the thread linked above.

Then I put this in my main forms on load event:
Code:
Private Sub Form_Load()

  CheckFrontEnd

End Sub
But I get this error:
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    95.8 KB · Views: 740

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
No, it is I that needs to apologise, that is the wrong utility.:banghead:
The help shows pdh_software as the author.?

I'll need to find what I actually am using, but it is by Bob Larsen..

FWIW here is the documentation.

The utility can be found from the first link at https://btabdevelopment.com/free-access-tools/
Sorry for leading you astray.

I see... sadly this won't work for 64 bit without being updated. Had to use the task manager to make it close... it was in an error loop lol.

Thanks anyway sir.
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
I figured out how to get it to run:
Code:
Private Sub Form_Load()

  CheckFrontEnd.CheckFrontEnd

End Sub

but for some reason it will correctly delete the database front end but leaves a command prompt up stating it can't find the path to the new file.

What I did was saved the front end master to the same location as the back end to ensure everyone that uses the database has permissions to access the new download.

I know the path is correct as it is the same path used in the table linking.
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
Well it actually wasn't correct...

After looking at the code closer I noticed I had the file name at the end of the path and the code didn't want that.

All good and this works. Thanks all.
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
So... I discovered that the code used leaves a batch file on the desktop or wherever you have your front end located and if yo happen to click on it while your fe is open it ruins it.

Anyone happen to know what to do to that code to make it not leave that file behind?
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
Better yet I should ask the person that posted the code.

Marking this thread solved
 

psyc0tic1

Access Moron
Local time
Today, 07:44
Joined
Jul 10, 2017
Messages
360
UGH... didnh't read first and my post was in the code repository... that one is going nowhere
 

Users who are viewing this thread

Top Bottom