Solved Database sluggish after Citrix Update

[Marshall Brooks]

@Pat Hartman - Hoping you might have some suggestions.

As many of you know, I develop/maintain an Access database for about 30 users.

Split single backend, and individual front ends.

About half our users are using Windows 11 and Office 365, and half are using Citrix.

Citrix was using Server 2016 and Office 2016 64-Bit and was significantly faster than Win11, especially over VPN.

Citrix is in the process of upgrading to Server 2022 and Office 365 and the database is significantly slower than before, only maybe slightly faster than Win11 over VPN.

I don't remember issues on the local side when we went to Windows 10 or Office 365.

Do you have any suggestions for what to check?

 

[Marshall Brooks]

To clarify and give more information. The database initially loads slowly over VPN. That still occurs. It still loads quickly in Citrix.

What is no longer working quickly in Citrix is some of the VBA procedures. Copying files, generating pop-up messages, etc. Those used to run quickly but now they pause for maybe 3-5 seconds.

 

[Pat Hartman]

Sorry Marshall, I'm mostly retired and no longer have access to any Citrix installations or experts. My clients tended to keep their Citrix versions of software in line with what was running on the LAN. I remember some years ago that Access had problems when used on a LAN where the users were running different OS' and/or different versions of Office without Citrix being involved.

 

[Marshall Brooks]

@Pat Hartman - Okay, thanks for chiming in.

I'm puzzled by the slowdown b/c previously we were running Win 11 and Office 365 locally and Server 2016 (Win 8) and Office 2016 on Citrix and Citrix outperformed the local installation.

I would have expected running a common version of Office (O365) and more modern OS (Server 2022 (Win10) would have made it run faster, if anything.

 

[Minty]

Check things like the Trusted locations, set on the new Citrix virtual environments.
If they aren't set it can cause quite a slow down.

 

[Marshall Brooks]

@Minty - Thanks, I'll try that.

Stupid questions time: That would have to be set for each user, correct? Stored in their profile or else by group policy.
Trusted locations for the front end or the back end or both?

We had to set Trusted Locations for the front end or the database wouldn't open.

I set the back end folder as a trusted location for myself, but I didn't really notice any difference from before the change to after.

However, I'm not using the VPN today and I didn't have to do any actual work in the database, so it's still somewhat hard for me to gauge exactly where the sluggishness tends to occur.

 

[Marshall Brooks]

Follow-up: I had to use the database this morning after adding the back-end folder as a trusted location and it was still painfully slow.

I'm in the office today and not using VPN, so it is not VPN related. Then again, it wasn't before - i.e. previously it was fast whether I was using VPN or not, now it is slow whether I am using VPN or not.

 

[Pat Hartman]

I'm puzzled by the slowdown b/c previously we were running Win 11 and Office 365 locally and Server 2016 (Win 8) and Office 2016 on Citrix and Citrix outperformed the local installation.

I've always found that Citrix outperforms the LAN connection also. But then my clients run their own Citrix server.

You should not be using a VPN if you are using Citrix. Both the FE and the BE are on the the same server when the BE is Jet/ACE so there is no network latency. That is why the response is even better than it is for people using their desktop on a LAN.

When you use a VPN, the FE is on your local hard drive and the VPN connects to the BE over the internet. This is always slow for a Jet/ACE BE. It is better when the BE is SQL Server though.

 

[Marshall Brooks]

I don't understand exactly how with works, but I shouldn't have implied we were using the VPN over Citrix.

To try to re-word:

• Locally (non-Citrix), the database is a lot faster in-office than it is over VPN - but the main slowdown seems to be opening queries and tables, rather than running VBA.
• Citrix - I launch the virtual desktop server either locally or via VPN, but everything is run from the server. Opening queries and tables was faster than in-office and that part still seems to operate quickly. What I am seeing is a slowdown in the VBA part of the database. For instance, in some operations it copies files and creates folders on a shared drive. That used to be almost instant in Citrix. Now it's like "This should be done, but I still have the spinning circle - did I miss the confirmation prompt?" and then a few seconds later the confirmation prompt appears.

 

[Pat Hartman]

Do NOT use VPN

 

[Marshall Brooks]

Basically, I stopped using VPN - for speed reasons rather then potential corruption, but I understand your point.

I used Citrix when I was working remote b/c it was faster. It still is for queries and record access, but it no longer is for my VBA code.

 

[Pat Hartman]

I used Citrix when I was working remote b/c it was faster. It still is for queries and record access, but it no longer is for my VBA code.

Look to the differences in Windows and Office versions.
Did the location of your FE and BE change?
Are you sure that every user is using a personal instance of the FE. Many Citrix admins do not understand how Access must be set up and allow all logged in users to use the same copy of the FE. I don't know that this leads to slowness but it sure leads to corruption.

 

[Marshall Brooks]

Citrix admins have not changed. They do admit that they don't know anything about Access and its set up.

See above for Windows and Office Versions. Citrix was using Server 2016 and Office 2016 and was fast. Citrix is now using Server 2022 and Office 365 (and different virtual servers) and is slow. However, local install is using Win11 and Office 365 and is fast (if not using VPN ...).

Front End and BE locations did not change. (Although profiles were re-created so Front-End folder had to be added to trusted locations.) I don't think BE folder was previously a trusted location, but I added it for myself and performance did not change.

Every user is using a personal instance of the FE. FE won't open unless it is installed on their U:\ drive which is mapped in a certain way to their username.

 

[Marshall Brooks]

Follow-up. It seems to be mainly my VBA code that is running slowly. I'm guessing this is more likely related to Office 365 than Server 2022.

I tried to compare between my local and Citrix settings, but is there something besides Trusted Locations that would slow down VBA code?

 

[isladogs]

As recently mentioned in another thread https://www.access-programmers.co.u...auto-updating-front-ends.334286/#post-1967416, code run from trusted locations can be at least 20 times faster as it bypasses the Anti Malware Scan Interface (AMSI scan) which otherwise runs not once per app but every time you run any code. The effects are noticeable!

Trusting a document does not bypass the AMSI scan

For more details, see

Is Location Trusted?

This article includes updated code to check whether an Access app is running from a trusted location. It also explains why using a trusted location is better than just trusting the Access app itself.

isladogs.co.uk

The relevant part is section 4: Which is better? Trusted Document or Trusted Location?

One other thing that causes a massive slowdown is if you continue to do development work on a database which has been code signed.
Normally making changes will invalidate the certificate (with a warning). However, if the code certificate is available on the dev PC, it will revalidate the signature after every single code change! As you would expect, this results in slug-like performance

 

[Marshall Brooks]

@isladogs - Thank you. That SOUNDS like it could be the issue, but there are some significant differences in my case.

First off, in the trust center>Macro settings, by default we are using this option: "Disable all macros with notification." I think we have the ability to change it to enable all macros, but that didn't seem to help (and would not be recommended).

The linked thread mentions this pop-up:

And your page mentions this banner:

We previously got those messages. Some of the users ignored them and just clicked open or Enable Content and didn't report any significant slowdowns. It annoyed me, so I always fixed the trusted locations for them, if I knew about them having the issue.

After this update to Citrix, our users did not get the yellow banner above or the pop-up, they got a red banner, wording similar to "Macros in this file have been blocked since the file is untrusted".

Since the database uses macros at startup, they couldn't open the database until they added the front-end folder as a trusted location, yet the sluggishness remains, so that isn't the solution - at least not apparently.

After @Minty's suggestion, I added the backend folder as a trusted location for me, but that didn't seem to help with the sluggishness.

As a followup - I started checking for anything I could find different between my local O365 database install and Citrix and I found I did have the backend folder trusted locally as a fully verifed (???) location - i.e. <network name.com\path> instead of <\path> but setting that on Citrix didn't help. (And I'd rather not tell our users the path to the backend file, although with some determination, they could likely find it themselves.)

I don't use any sort of code-signing in the database. I was somewhat curious about that and wasn't sure how it worked, but I found your article here: https://isladogs.co.uk/vba-project-signing/index.html - I'm not sure if I have certificates available, but from what you said above, it sounds like I might end up exchanging one set of issues for different issues.

Another long-shot, but I saw: https://www.access-programmers.co.uk/forums/threads/performance-issues.332128/ and I checked and Citrix is running O365 2408 17928.20588 and locally, I'm running O365 2408 17928.20572.

So the issue might fix itself with the next O365 update (or cynically, I might start seeing the issue locally when O365 updates to the Citrix version).

 

[Pat Hartman]

Are you sure they actually added a trusted folder rather than just trusting the FE file?

 

[The_Doc_Man]

Holey moley, something I saw in your recent post flicked a switch. Your warning names this path

C:\Users\cridd\OneDrive\Desktop\QMV_v2.15.ACCDR

Before we go crazy on this, there is a question we have to ask: Are you actually running the DB from a cloud drive? I realize that names can be anything and can mean nothing, but... that particular name, if it is indicative of what it USUALLY means, is critical.

A lesser comment is that having extra DOT characters in a file name can SOMETIMES confuse issues under circumstances I don't recall at the moment. It relates to some older software not liking extra periods, though Windows itself is mostly OK with that extra embedded dot. Anything with a home-grown or non-standard filename parser might have issues. So would it make a difference to anyone if you used QMV_v2_15.ACCDR as the file name?

I'll admit, that extra dot is more of a quibble. The OneDrive question is very important. Before I jump on that too hard, I have to verify what, if anything, it actually means in your case.

 

[isladogs]

I wondered whether anyone would pick up on that point. A simple test would have answered all the questions in your post

I moved the ACCDR file to a convenient non-trusted location - in this case my local desktop folder purely to show the message that appears using ACCDR files in non-trusted locations.

I could have used any non-trusted location for that file e.g.

And no it doesn't make any difference whether or not the file name contains a dot/period

...or has no special characters whatsoever ...

I would never use a cloud location for any production Access database for reasons that should be familiar to most users here

 

[The_Doc_Man]

And no it doesn't make any difference whether or not the file name contains a dot/period

When I looked that up, it suggested that most of the time it wouldn't make a difference. I thought I pointed out that it might not matter because only specific cases ran afoul of the extra dot. But I was being overly cautious.