Solved Database not updating from Citrix ... (1 Viewer)

[Marshall Brooks]

Really hard to explain, but this works in Windows10 with Access M365 and does not work in Citrix with Access 2016 - although it used to work. I somewhat understand where the error comes into play. I'm using a variation of https://btabdevelopment.com/free-access-tools/ front end auto update tool.

When the database opens, it opens my frm_switchboard. The form_Load() procedure for that form calls Function AutoExec() in Module1. Function AutoExec() calls Function AutoUpdate() in Module 1.

Function AutoUpdate checks the date in a local table against a date in a backend table and if the date in the backend table is newer, it updates the database, otherwise it opens normally.

Opening normally works in either OS.

If the database is out-of-date, Function AutoUpdate calls Function UpdateFrontEnd in module BasFEUpdate. That function writes a batchfile to update the database, opens the batch file, and closes the database. The batch file deletes the current database and then downloads the new version from the server and opens it.

I have a progress bar that says "Checking for Updates". Under Win10, the progress bar briefly comes up after I tell it to perform the update and then goes away. Under Citrix, the progress bar that says "Checking for Updates" never goes away, so the database doesn't ever close, so the batch file cannot update the database.

I think what is happening is the database can't close until the frm_Switchboard finishes loading, but under Citrix, it gets stuck in a loop and never exits. I need to know how to prevent this.

Also - I was giving up up on the complicated update procedure so I changed the code for Citrix to just run DoCmd.quit acQuitSaveNone. That doesn't work either, it just shows "Checking for Updates" and I end up closing it from the Task Manager. (However, DoCmd.Quit acQuitSaveNone works fine from a button on my forms in Citrix or locally, just not from the startup functions.

Thanks in advance!!!!!

 

[Pat Hartman]

There is almost no overhead attached to downloading a fresh copy of the FE each time the user opens it and when running from Citrix, the overhead is even less since both are on the same drive and so you don't have the LAN between the source and the target locations. I just use a small batch file to always copy the FE. The app does compare the FE and BE versions and won't open the FE if it is out of sync with the BE but your code doesn't seem to do that.

Without your database, we probably can't figure out why the switchboard is stuck in a loop but you could try by posting ALL the code. Also, have you tried to walk through the code to see where it is failing?

Two choices - either just comment out all the code related to whether or not you load a new version and use a bat file to always load a new version OR, dig in to figure out the problem with the code.

 

[Marshall Brooks]

More Oddities ...

I have a message box to say the db is out-of-date and if you click the update button, it is supposed to either update or close. That code looks like this:

If strResult = vbBt1 Then
' Outdated = True
DoCmd.Quit acQuitSaveNone
UpdateFrontEnd
End If

It used to call UpdateFrontEnd, but that didn't work in Citrix. For Citrix I changed it to DoCmd.Quit, but that doesn't work either.

I just tried setting a public Boolean variable called Outdated and running setting that to True if you click Update on the msg box.

Then on the switchboard Form_Load, I added:
If Outdated = True Then
DoCmd.Quit acQuitSaveNone
End If

Nothing happened - the Switchboard form loaded properly.

I moved the code to Form_Activate and it was stuck in the loop again.

 

[Marshall Brooks]

There is almost no overhead attached to downloading a fresh copy of the FE each time the user opens it and when running from Citrix, the overhead is even less

Quite true. Since the script wasn't working to update the Front End, I wrote the batch file manually and it works just fine. It would just be a matter of training our users to run that instead of opening the front end normally.

but you could try by posting ALL the code.

Not happening - too many network links that couldn't be shared with the forum, but I understand what you mean.

Also, have you tried to walk through the code to see where it is failing?

Of course, but I've somewhat lost track. I'll try again and report what I find ...

 

[Pat Hartman]

Not happening - too many network links that couldn't be shared with the forum, but I understand what you mean.

You should not be hardcoding stuff that is changeable, let alone sensitive.

To get beyond the training issue, change the name of the BE so the old FE will break and don't forget to add code to the new FE to compare the versions so you don't have to rename the BE again to break the FE.

 

[Marshall Brooks]

You should not be hardcoding stuff that is changeable, let alone sensitive.

Hindsight is 20/20, but point taken.

Got odd results stepping through the code. First of all, I had a dumb mistake - I had the check for the Outdated variable at the beginning of the code, b/c I thought it was running through the form load code twice.

I changed it to:
Call AutoExec
If Outdated = True Then
DoCmd.Quit acQuitSaveNone
End If

If I do nothing if the db is outdated, it opens normally. If I set Outdated = True, it tends to hang on "DoCmd.Quit acQuitSaveNone" - but as I said, I have the same code behind a button and it works without any issues.

Odd - if I have the code the way I originally had it - i.e. in Form_Load():

If Outdated = True Then
DoCmd.Quit acQuitSaveNone
End If
Call AutoExec

It seems to get all the way to End Sub in Form_Load and then it hangs on that line ...

 

[Pat Hartman]

I would not call a procedure AutoExec because that is the name of a special purpose macro. I can't imagine that this is the problem but I would still change it.

There is no way we can evaluate the code since "Outdated" is meaningless without the code as is "AutoExec".

 

[Marshall Brooks]

Outdated is a Boolean variable - as I said above.

Forgot to mention two other things ...

It works the same way with Application.Quit as it does with DoCmd.Quit.

I've been running it with Task Manager open since I usually need to close it that way. When it is supposed to close, Access disappears from Task Manager and then comes back again. So it almost seems like Access closes and then re-opens, but I'm pretty sure that isn't what is really happening.

 

[Pat Hartman]

We'll never know since we can't see the code. Good luck.

 

[Marshall Brooks]

Posting a little bit (actually a lot) of code:

Private Sub Form_Load()
    If AlreadyOpened = False Then
        Call AutoExec
        AlreadyOpened = True
        If Outdated = True Then
            DoCmd.Quit acQuitSaveNone
        End If
    End If
' Code to show some buttons based on path and not otherwise and some buttons based on users and not otherwise not posted.
End Sub

Function AutoExec()
On Error GoTo AutoExec_Err
'   On Error GoTo 0
    Call ShowAppInterface ' Maximize Access so progress bars are centered.
    Call HideAppInterface
    With ufProgress
        .LabelCaption.caption = "  Loading Database"
        .LabelProgress.Width = 0.12 * (.FrameProgress.Width)
        .Show
        .Repaint
    End With
    If LCase(CurrentProject.Path) = "Development Folder on Network" Then
        ShiftKeyProp (True)
    Else
        ShiftKeyProp (False)
    End If
    DoCmd.OpenForm "frmLogoutTimer", acNormal, "", "", , acHidden
    If (CurrentProject.AllForms("frmDownForMaintenance").IsLoaded = True) Then
        If LCase(CurrentProject.Path) = "Marshall Brooks Folders" Then
'       MsgBox "Right Path"
            ' Bypass the shutdown if the database is on MB's Desktop so that he can do maintenance on the database.
        Else
'        MsgBox "Wrong Path"
            Exit Function
        End If
    End If
    Call AutoUpdate
' Code to update local tables from backend tables.
End Function

Sub AutoUpdate()
'AutoUpdate Procedure modified from https://btabdevelopment.com/free-access-tools/ (Bob Larson's Page) 30-Aug-2022 by Marshall Brooks.
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strCLLocation As String
Dim strFilePath As String
Dim Location1 As String'  - These are network paths that the database is allowed to open from.
Dim Location2 As String
Dim Location2A As String
Dim Location2B As String
Dim Location3 As String
Dim Location4 As String
Dim fs As Object
With ufProgress
    .LabelCaption.caption = "  Verifying Startup Path"
    .LabelProgress.Width = 0.25 * (.FrameProgress.Width)
    .Show
    .Repaint
End With
strFEMaster = Nz(ELookup("[fe_version_number]", "[tbl-version_fe_master]"), "") & ""
' looks up the version of the front-end on the front-end
strFE = Nz(ELookup("[fe_version_number]", "[tbl-fe_version]", "[ID] =1"), "") & ""
strMasterLocation = Nz(ELookup("[masterlocation]", "[tbl-version_fe_master]"), "") & ""
' Locations are assigned network paths
If LCase(CurrentProject.Path) = Location1 Or LCase(CurrentProject.Path) = Location3 Or LCase(CurrentProject.Path) = Location4 Then
    'MsgBox "Okay Location"
Else
    strResult = Dialog.Box(Prompt:="Prohibited Location!" & vbCrLf & "" & vbCrLf & "Please copy database to either your Desktop (Local) or U:\ (Citrix) and re-open." & vbCrLf & vbCrLf & _
    "Database will now close" & "", Buttons:=(0 + 16))
'    Call UnloadAllForms
    Dim I As Integer
    For I = VBA.UserForms.Count - 1 To 0 Step -1
        Unload VBA.UserForms(I)
    Next
    DoCmd.Quit acQuitSaveAll
End If
'' checks for the existence of an updating batch file and deletes it if it exists
        strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"
        If LenB(Dir(strFilePath)) <> 0 Then
            Set fs = CreateObject("Scripting.FileSystemObject")
            fs.DeleteFile (strFilePath)
            Set fs = Nothing
        End If
' if the current database opened is the master or the developmnt folder then it bypasses the check. This prevents overwiriting the development database with the released version.
If CurrentProject.Path = strMasterLocation Or LCase$(CurrentProject.Path) = "My Development Folder" Then
'    Exit Sub
'    GoTo Load_Form - Not required, will exit the If-Else-Endif anyway.
Else
    With ufProgress
        .LabelCaption.caption = "  Checking for Updates"
        .LabelProgress.Width = 0.38 * (.FrameProgress.Width)
        .Show
        .Repaint
    End With
        If CDate(strFE) < CDate(strFEMaster) Then ' This used to be Do While instead of If-Then - seems to fail the same either way.
            Unload ufProgress
            If LCase(CurrentProject.Path) = Location1 Then ' Desktop - Non-Citrix
                strResult = Dialog.Box(Prompt:="Database is outdated.\n\nYou are using version            " & Format(strFE, "dd-mmm-yyyy") & ".\nThe latest released version is " & Format(strFEMaster, "dd-mmm-yyyy") & ".\n\nClick UPDATE to close the database and download the latest version.\nThe database will then re-open.", Buttons:=(1024 + 16), _
                LabelButton1:="View Changelog", LabelButton2:="Update", DefaultButton:="2", TITLE:="Version Update Required")
    '            Exit Sub
                If strResult = vbBt1 Then
                     strCLLocation = Nz(ELookup("[changeloglocation]", "[tbl-version_fe_master]"), "") & ""
                     Call fHandleFile(strCLLocation, 1)
                Else
                    ' 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
            Else ' Citrix
                strResult = Dialog.Box(Prompt:="Database is outdated.\n\nYou are using version            " & Format(strFE, "dd-mmm-yyyy") & ".\nThe latest released version is " & Format(strFEMaster, "dd-mmm-yyyy") & ".\n\nClick UPDATE to close the database, then double-click UpdateDatabaseCitrix from your U:\ Drive.", Buttons:=(1024 + 16), _
                LabelButton1:="View Changelog", LabelButton2:="Update", DefaultButton:="2", TITLE:="Version Update Required")
    '            Exit Sub
                If strResult = vbBt1 Then
                     strCLLocation = Nz(ELookup("[changeloglocation]", "[tbl-version_fe_master]"), "") & ""
                     Call fHandleFile(strCLLocation, 1)
                Else
                    Outdated = True
'                    DoCmd.Quit acQuitSaveNone
                End If
            End If
        End If ' This used to be Loop - seems to fail either way.
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
strReplFile = g_strCopyLocation
' 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, ""
' Pause 15 seconds, I think - was originally 2 seconds.
Print #1, "ping 1.1.1.1 -n 1 -w 15000"
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 - Does not work in Citrix and neither does WinScript Shell.
Call fHandleFile(TestFile, 2)
'closes the current version and runs the batch file
' 8-Sep-2022 Marshall Brooks DoCmd.Quit is deprecated and Application.Quit is preferred, but DoCmd.Quit is faster. With Application.Quit, the batch file was trying to delete the database before it closed.
DoCmd.Quit
'Application.CloseCurrentDatabase
'Application.Quit
End Sub

 

[Pat Hartman]

Perhaps someone will play computer and try to follow the code step by step but it won't be me. Good luck.

 

[Marshall Brooks]

We'll never know since we can't see the code. Good luck.

Perhaps someone will play computer and try to follow the code step by step but it won't be me. Good luck.

<sarcasm>You can't have it both ways ... (Well, actually I guess you can ...) Initially, you said you couldn't help me b/c you couldn't see the code, so I posted most of the code and now you're still saying you won't help me.</sarcasm>

(I'm mostly kidding. You and I take different approaches and yours are more conventional and more widely accepted (and likely more correct.) It's no fun reviewing someone else's code plus the fact that I omitted some proprietary information that seemed to be working fine but could be causing the errors.)

I do appreciate your advice and I do realize it is usually good and better than the approach I often choose to take.

 

[Marshall Brooks]

I've been running it with Task Manager open since I usually need to close it that way. When it is supposed to close, Access disappears from Task Manager and then comes back again. So it almost seems like Access closes and then re-opens, but I'm pretty sure that isn't what is really happening.

More info - I changed a LOT yesterday before I posted here and in some cases I forgot what I changed.

The above scenario happened AFTER if revised the generated .cmd file to increase the delay and add a line to "taskkill /im MSACCESS.EXE /t /f" https://stackoverflow.com/questions/20068415/close-ms-access-from-a-batch-file - to the file.

My thinking was if the DoCmd.Quit line was not properly closing Access and that was preventing the batch file from downloading the new version, than I could just close it from the batch file.

THAT is when I saw the above. (And it wasn't what I expected). I would run the database and click Update, the batch file would appear in the Task Bar, Access would disappear in the task manager and than re-appear a 1/2-second or so later, the batch file would close and nothing would have updated.

I'll test a bit more and post any more info that I find.

 

[Marshall Brooks]

Okay - I have a theory about what is happening, but I'm not sure exactly why it is happening (especially since it works fine under Win10 locally).

Going back to the initial reply - the frm_switchboard is loaded at startup. It calls the function AutoExec, which calls the AutoUpdate, which might close the database.

I think what it is doing is Access won't process the close command until the form loads, but since it never loads since the parameters are not met, it won't close.

As a test - you can see where the code (AutoUpdate) has "Prohibited Location" that the same code "DoCmd.Quit acQuitSaveAll" is supposed to run there.

So I moved the database (under Citrix) to a subfolder which the code won't like.

I got the message pop-up and then this (which is what I had been seeing previously):

Message is the title of the "Prohibited Location".

I thought there might be a problem with Enhanced Message Box (although it claims and has a test that it is non-blocking), I commented out that line. Then the database should just close with no warning.

I ran it again and this time I got something similar to the above (Not Responding), but the title of the window was "Database is Outdated". That is a later message, but it shouldn't appear at all, unless the DoCmd.Quit line was not processed yet.

It is NOT an issue with DoCmd.Quit not working under Citrix b/c I have that code behind a button AFTER the form loads and it works fine from there.

What I think MIGHT work is moving my Startup check from Form_Load to either Form_Activate or Form_Current - but I don't think that should be necessary (and seems like a bad idea, but ...)

 

[Marshall Brooks]

Progress!!!

(Actually solved, except for some oddities).

I moved the following lines:

    If AlreadyOpened = False Then
        Call AutoExec
        AlreadyOpened = True
        If Outdated = True Then
            DoCmd.Quit acQuitSaveNone
        End If
    End If

Out of form_load() for the frm_Switchboard and into form_Activate. No change, still got the error at the same location.

I moved the code to frm_current and everything seems to be working fine.

Some oddities ...

Initially, I tried opening the file from the subfolder of my U:\ Drive. Got the prohibited location message clicked okay and Access closed. I then tried to open the file from U:\ by double-clicking it and nothing happened. Right-clicked and selected open and nothing happened. I was able to open Access and select it from the recent files and it opened, but when I tried to make changes, it said that I might not be able to save them (and I wasn't) b/c I didn't have exclusive access to the database. This is in my data folder on Citrix. Besides me, nobody but a sysadmin is supposed to be able to view it, much less have my database open. I did see that there was still an .laccdb file in the folder and I was able to delete that but I didn't do that in this case before I tried to open the database.

I'm going to mark the thread as solved, but I'd appreciate any insights as to why this was needed as the solution.

 

[Pat Hartman]

@Marshall Brooks You can be as sarcastic as you want. You do realize don't you that we don't get paid to help you, right? You refused to upload a database. You refused to upload the complete code module. I don't have time in my life to read incomplete code and try to figure out what is wrong with it. You can't find the error and you are looking at the complete module AND running the code to step through it which just points out what a waste of my time it would have been to try to figure out what is wrong with my hands tied behind my back. Please let us know if you figure out the problem.

 

[Marshall Brooks]

See Reply #15. (Also, I re-opened Citrix and did not see the errors with not being able to double-click and open the database after the code closed it. I think having it hang and having to close it from Task Manager caused those issues.)

DoCmd.Quit apparently doesn't work under Citrix (our version - it used to) from either Form_Load or Form_Activate. It works from a button or from Form_Current. It works fine from form_load or form_activate under Win10 locally.

I'm not sure why it didn't work under Citrix when it used to work.

I realize you don't get paid to help me and I don't get paid to post what I did that worked and hope it helps others who may run into similar problems.

I refused to upload the full database or the full modules that were having problems. I loaded enough for others to test/verify - although I'm not sure the issue is present for ALL versions of Citrix and it is not present for Win10 - so I'm not sure how much help loading it would have been. Chances are someone would have replied "Works fine for me under Citrix." which would have left me even more confused.

 

[Pat Hartman]

Use Application.Quit rather than DoCmd.Quit

I don't know whether this makes a difference with Citrix but "Application" means "Access" whereas DoCmd would refer to what ever object has the focus.

docmd.quit vs. Application.Quit - Microsoft Access / VBA

docmd.quit vs. Application.Quit. Microsoft Access / VBA Forums on Bytes.

bytes.com

DoCmd.Quit vs Application.Quit

What is the difference between DoCmd.Quit and Application.Quit? Is it that when you can't quit with DoCmd.Quit you can using the Application.Quit command? Perhaps there isn't a difference? I think it's the latter but you can't be too sure...

www.access-programmers.co.uk

 

[Marshall Brooks]

Use Application.Quit rather than DoCmd.Quit

I don't know whether this makes a difference with Citrix but "Application" means "Access" whereas DoCmd would refer to what ever object has the focus.

Not going to get in an argument with you over this, but ...

That wasn't the issue. I'm almost positive I did try using Application.Quit instead of DoCmd.Quit and it still failed.

Also, see my comments in the last code block from 8-Sep-2022.

Essentially, Application.Quit and DoCmd.Quit are SUPPOSED to do the same things - I know your first link showed an example where they don't. DoCmd.Quit was supposed to call Quit from the menu bar and is deprecated, but retained for compatibility with (I believe) Access 95.

In my testing in 2022, Application.Quit was slower and my created batch file (which runs before Access closes) was trying to delete the existing database while it was still open and was failing.

In hindsight, now that I understand more how the code works, I could have added a couple more seconds pause to the created batch file and used Application.Quit and I don't see why it wouldn't have worked.

Also - something else was going on with the recent error - for example, I still saw the "Database is Outdated" message when it was trying to close, and that should have been gone as soon as I clicked Update.

I'm more interested in why neither one works in (our version of ...) Citrix when called from a Form_Load or Form_Activate event but works fine from Form_Current, but that DID turn out to be the solution.