Updating users front ends via VBA

[tmyers]

I have been reading about this off and one, since I know it is a bridge I am eventually going to have to cross.

For quick reference, I have read things like:

MS Access - Deploying Your Database | DEVelopers HUT

A few approaches to deploying your database front-end to your end-users.

www.devhut.net

How do I distribute updates to a Access database front end?

I've got an Access 2007 database that I developed which connects to SQL Server for the actual data storage. I used the Package Solution Wizard to create a distributable installer which included ac...

stackoverflow.com

And a few other sites and post.

Since I am at the stage of having people starting to use my app for testing, I would like to keep their front end current without having to constantly tell them a new version is uploaded to our server to be downloaded. Since I am still very actively updating/bug fixing/changing things, it would be easier if I could have everyone's front end do a check on open, then update if needed.

I think the option offered by:

Free Access Tools | BTAB Development

FRONT-END AUTO-UPDATE ENABLING TOOL (Works for all versions 2000 and up) Microsoft Access Front-End Auto-Update Enabling Tool - allows you to quickly add auto-updating to your front-ends (PLEASE NOTE: This tool is primarily meant for networked users that have direct access to the backend...

btabdevelopment.com

Would work, but it is not updated for 64 bit.

Im sure @isladogs has a sample database to work with, as all of their samples are super helpful and informative (I currently use your setup for minimizing/hiding the nav pane and such so thank you for having posted that in the past ).

Could anyone recommend something?

 

[Gasman]

Search this site as I have posted what I used in the past, and do not believe it was bit dependent.?
Others recommend just copying the FE each time it is opened from a shortcut.

 

[theDBguy]

I also started with Bob's utility but now use a script instead.

 

[tmyers]

I also started with Bob's utility but now use a script instead.

I would like to try it, but I get the error regarding 32/64 bit.

 

[tmyers]

@Gasman I did find:

Update FE for users

Hi all, It has been asked several times here 'How do I issue updates to the FE for users'. I have used Bob Larsen's code in the past and that was fine for me. Some have struggled with it though. Found this on my travels around the net and thought it might be worth mentioning as another...

www.access-programmers.co.uk

Does your code from Github have the same issue regarding 32/64 bit?
If not, I would be more than willing to try it out.

 

[Gasman]

This is all I have for my updating?

Option Compare Database
Option Explicit
' global variable for path to original database location
Public g_strFilePath As String
' global variable for path to database to copy from
Public g_strCopyLocation As String
Sub CheckFE()
' Used for updating FE
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String
Dim strReqPath As String
Dim lngDateID As Long

' looks up the version of the front-end as listed in the backend
strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")

' looks up the version of the front-end on the front-end
strFE = DLookup("fe_version_number", "tbl-fe_version")

' looks up the location of the front-end master file
strMasterLocation = DLookup("s_masterlocation", "tbl-version_master_location")

' checks for the existence of an updating batch file and deletes it if it exists
    strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"
    
    If Dir(strFilePath) <> "" Then
        Dim fs As Object
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.DeleteFile (strFilePath)
        Set fs = Nothing
    End If
        

' if the current database opened is the master then it bypasses the check.
If CurrentProject.Path = strMasterLocation Then

    Exit Sub
    
Else

' if the version numbers do not match and it is not the master that is opened,
' the database will do the update process
    If strFE < strFEMaster Then ' Amended to less than rather than not equals
        MsgBox "Your program is not the latest version." & vbCrLf & _
        "The front-end needs to be updated.  The program will " & vbCrLf & _
        "now close and then should reopen automatically.", vbCritical, "VERSION NEEDS UPDATING"
        
        ' sets the global variable for the path/name of the current database
        g_strFilePath = CurrentProject.Path & "\" & CurrentProject.Name
        
        ' sets the global variable for the path/name of the database to copy
        g_strCopyLocation = strMasterLocation
        
        ' calls the UpdateFrontEnd module
        
        UpdateFrontEnd
        
    End If
    
End If
End Sub

Public Sub UpdateFrontEnd()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String

' sets the file name and location for the file to delete
strKillFile = g_strFilePath
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
' sets the file name of the batch file to create
TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
' sets the restart file name
strRestart = """" & strKillFile & """"
' creates the batch file
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1
'Exit Sub
' runs the batch file
Shell TestFile

'closes the current version and runs the batch file
DoCmd.Quit



End Sub

 

[Gasman]

@Gasman I did find:

Update FE for users

Hi all, It has been asked several times here 'How do I issue updates to the FE for users'. I have used Bob Larsen's code in the past and that was fine for me. Some have struggled with it though. Found this on my travels around the net and thought it might be worth mentioning as another...

www.access-programmers.co.uk

Does your code from Github have the same issue regarding 32/64 bit?
If not, I would be more than willing to try it out.

As I mentioned in that thread. Bob Larsen's code was fine for me. His DB sets it all up as well, but still some people could not get to grips with it.?

 

[tmyers]

Ill give it a go and get back to you if I run into problems I cant solve.
As always, thanks for your help Gas!

 

[tmyers]

As I mentioned in that thread. Bob Larsen's code was fine for me. His DB sets it all up as well, but still some people could not get to grips with it.?

I couldn't test it due to a bit version issue.

That is way beyond me to solve on my own.

 

[Gasman]

It has been ages since I have had to implement it. Once it is in it just works.
Did you use this version?

OK, I found come APIs that appear to be for copying text. You can copy it manually as commenting out the code means the Copy button does not work.

Try the accdb loaded.

 

[tmyers]

Yup that's the one I tried using that gives me that error.

 

[Gasman]

Yup that's the one I tried using that gives me that error.

Try the accdb version

 

[tmyers]

Try the accdb version

Tried both attachments on your post, same error on both.

 

[theDBguy]

Tried both attachments on your post, same error on both.

Hi. Can you post the file you're using, so we can take a look? Thanks.

 

[Gasman]

Tried both attachments on your post, same error on both.

You need a 64 bit person. I cannot help any further. Sorry.

 

[Minty]

Try this version

 

[tmyers]

Try this version

It didn't instantly throw that error on opening, so I will mess with it and let you know!
Thanks Minty!

 

[Minty]

You are welcome - apologies to @Gasman for jumping in - he just lacks some bits...

 

[tmyers]

Nope. Trying to run anything causes the app to crash out.
Maybe its because it is for I believe an older version of Access? Aren't MDB files for 2010 and older?

Edit:
For the record, I am running 2016 with no access (no pun intended) to older versions

 

[Gasman]

You are welcome - apologies to @Gasman for jumping in - he just lacks some bits...

None, required, even if I could have gone further with it.