Run code AFTER Access closes

[CedarTree]

I have a front-end that sits on people's desktops. Using Timer in a hidden form, VBA checks periodically if a new version of the FrontEnd is available (it looks to an ODBC table to see if a new version is available). Currently, if a new version is flagged, Access warns the user, opens File Explorer so they can copy the new version from the LAN to their desktop, and then closes Access.

Is there a way to easily run code to copy the Front-end from the LAN and overwrite the version on the user's desktop? Of course, the front-end has to be closed for Windows to copy the file over. I've thought about maybe creating a batch file (but then how to run it?) Or create a temporary database to do it? Sounds clumsy though.

Suggestions?

 

[essaytee]

There are many threads in AWF regarding this issue. What I do is check for the existence of an updated frontend at the time of opening the frontend. If there is a new version, then run a batch/script file and the very next line exit out of the frontend (as you say, the frontend must be closed). In the batch/script file, you could have a 'wait' statement, pausing for x number of seconds, 5 or 10 or 15, long enough with a bit to spare to make sure that the frontend has closed. The batch/script file then copies from the network the updated frontend to the user's desktop. Once copied, open the frontend.

 

[June7]

Automatic updating database is fairly common topic. Here is original updater code from my db. Notice there is no script file called, all self-contained - don't ask me how it works, it is found code. It worked fine for several years until IT updated computers and permissions no longer allow programmatic copying of files. Now I have code that does what you describe - open file explorer to folder holding updated frontend so they can manually copy.

Private Sub Form_Load()
        
'Check for updates to the program on start up - if values don't match then there is a later version
If Me.tbxVersion <> Me.lblVersion.Caption Then
    'because administrator opens the master development copy, only run this for non-administrator users
    If DLookup("Permissions", "Users", "UserNetworkID='" & Environ("UserName") & "'") <> "admin" Then
        'copy Access file
        CreateObject("Scripting.FileSystemObject").CopyFile _
            gstrBasePath & "Program\Install\MaterialsDatabase.accdb", "c:\", True
        'allow enough time for file to completely copy before opening
        Dim Start As Double
        Start = Timer
        While Timer < Start + 3
            DoEvents
        Wend
        'load new version - SysCmd function gets the Access executable file path
        'Shell function requires literal quote marks in the target filename string argument, apostrophe delimiters fail, hence the quadrupled quote marks
        Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
        'close current file
        DoCmd.Quit
    End If
Else
    'tbxVersion available only to administrator to update version number in Updates table
    Me.tbxVersion.Visible = False
    Call UserLogin
End If
End Sub

 

[CedarTree]

Wow -- cool ideas I'll check out.

 

[CedarTree]

Actually, can you point me to a few more code examples... I did some searching with no luck. (Sorry to bother you guys).

 

[theDBguy]

Hi. I don't have a link handy but did you do a search on the term "auto fe updater?"

 

[essaytee]

Actually, can you point me to a few more code examples... I did some searching with no luck. (Sorry to bother you guys).

Which code part are you after? The batch/script file or code within the frontend? As The DB guy says, search this forum, you will get a ton of info regarding this. I'll check later my code snippets, can't at the moment.

 

[CedarTree]

Yeah I tried script and frontend. Let me try auto fe updater. Thanks.
I'm looking for the code in the FrontEnd that would create the Script (so that gives me the Script code too), that would copy the FrontEnd from the LAN on top of itself.

 

[CedarTree]

And... still no luck. Sorry, maybe I'm still searching in a wrong way.

 

[theDBguy]

The one I used to use came from Bob Larson’s website.

Sent from phone...

 

[theDBguy]

And... still no luck. Sorry, maybe I'm still searching in a wrong way.

Interesting, when I Googled it, I got a lot of hits.

 

[June7]

Did you read post 3?

If you really want a version that calls a script, I have that as well.

 

[essaytee]

I had a quick look at one of my apps. The following function is called from a startup routine and obviously before the startup form is called.

Sub CheckForUpdate()
    If k_YOUR_VERSION < gIntLatestVersion Then
        MsgBox "An updated version is now available, press any key to upgrade"
        Shell "FilepathYourBatchFile", vbNormalFocus
        Application.Quit
    End If
End Sub

The above is the barebones

 

[deletedT]

There are many threads in AWF regarding this issue.

The main problem is that search doesn't work as it should.
https://www.access-programmers.co.uk/forums/showthread.php?t=304101

In the batch/script file, you could have a 'wait' statement, pausing for x number of seconds, 5 or 10 or 15, long enough with a bit to spare to make sure that the frontend has closed.

I do something like this, but I don't wait for an un-specific amount of time. I check to see if a mydatabase.laccdb file exists in the directory or not. If not it means the DB is closed successfully and I can copy and overwrite it.

 

[essaytee]

The main problem is that search doesn't work as it should.
https://www.access-programmers.co.uk/forums/showthread.php?t=304101



I do something like this, but I don't wait for an un-specific amount of time. I check to see if a mydatabase.laccdb file exists in the directory or not. If not it means the DB is closed successfully and I can copy and overwrite it.

Yep, that works and the next time I get to modify my app I will probably incorporate that.

 

[CedarTree]

I was searching within the forum. I'll check Google too, and the recent posts. Post #3 seems to run the script while the database is running so I don't think that's what I need (?)

 

[CedarTree]

So here's what I found from another forum... (Bob Larson's code)... but doesn't this run the batch file BEFORE it closes the current version?

Option Compare Database
' 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


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

 

[theDBguy]

Hi. You would think so based on how it's written but this comment at the bottom tells you what really happens:

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

As I said earlier, this is what I used to use, and it worked fine then.

 

[June7]

If the code in post 3 works why would you need a separate script file? As I said, I have tried both and implemented version that does not rely on separate script file. It worked for several years until IT updated computers and no longer allow programmatic copy.

 

[CedarTree]

I guess somehow the batch file knows how to wait for Access to close... I'll give it a try.