Solved Database sluggish after Citrix Update

[isladogs]

As I said, a quick test of creating an ACCDR file and saving to a non-trusted location would have instantly answered all those questions.

 

[Pat Hartman]

Access does have problems in certain cases with extra dots in file names. But not with extra dots in the application database name itself. I used to get .csv files from a Unix system and those people were dot crazy. I couldn't open the files using any standard import method. I had to use FSO to rename the files to remove all the extra dots. Then I could use standard import methods. So, that tells me that the Access.exe itself doesn't have a problem but code inside the Access.exe that runs when my app uses VBA to execute standard import methods is/was never updated to ignore extra dots.

 

[isladogs]

I don't have a Unix server handy but any previous problems importing from csv or xlsx files with a dot in the name have been fixed at some point.

However, attempting to import data from text files with some Unicode characters in the name can still cause problems.
For example: 日本語のキーボード= Japanese keyboard

• attempting to import from 日本語のキーボード.csv fails
• importing from the same file name as an Excel file 日本語のキーボード.xlsx succeeds

 

[NauticalGent]

they got a red banner, wording similar to "Macros in this file have been blocked since the file is untrusted".

Have you removed the "Mark of The Web" flag?

 

[KitaYama]

attempting to import from 日本語のキーボード.csv fails

I'm sure you know this, and though it's irrelevent to OP's case, but for others who see this FAIL, I'd like to add this:

In short terms, a Japanese system doesn't fail to import 日本語のキーボード.csv

Spoiler: In Details:

In Windows, adding a language, and changing the locale is very dfifferent.

By adding a languge mostly 4 section changes:
Display language (changes the language of menus, settings, and interface)
Keyboard layout (lets you type in that language)
Speech and handwriting recognition (Adding auto correct)
Regional formatting preferences

Changing OS locale:
The system locale affects how non-Unicode programs display text.
It determines how non-Unicode legacy apps (e.g., older software or games) interpret and display text.
It sets the code page used by these programs (e.g., Shift-JIS for Japanese, Windows-1251 for Russian).
It affects some system-wide formats like currency, date, and sorting in certain legacy contexts.

As you see, adding a languate is more of a visual change. And it doesn't change OS behavior.
That's whay your system fails to import a text file (csv) in Japanese. Because you have only added some visual preferences to Office and the engine behind Office (OS) is still the same.
But for us, on this end of the world, who have changed our locale to our language, it doesn't fail.

 

[isladogs]

Hi @KitaYama
Yes I was already aware of those details but I’m sure they will be useful to others

The interesting thing for me is not that the CSV file fails when importing to e.g. en-gb but that importing from an identically named Excel file succeeds.

 

[isladogs]

Have you removed the "Mark of The Web" flag?

Good point. Strange that point got overlooked at the time.

 

[NauticalGent]

Good point. Strange that point got overlooked at the time.

Happens to us all the time. I found a PS script that removes it from all files in a Folder and any sub-folders.

 

[isladogs]

I use a right click context menu item to unblock files

 

[Marshall Brooks]

Have you removed the "Mark of The Web" flag?

No and that seems likely to be the issue. From the Front-end or the back end or both? And how do I do that? I was having issues with Excel files also and it ended up being an issue with having protected view enabled, but one suggestion was copying to a local folder and checking unblock.

The file didn't have MOTW issues with the previous OS, but I don't know that that means anything.

More questions: For the Excel file, there was an option when you right-click and under General Security click an Unblock checkbox to unblock the file, but I didn't see this on a shared drive, only when the file was copied to a local folder. I don't see the option under Citrix and I don't have a way to copy the file to a local drive under Citrix. I'm not sure if unblocking the file on a local drive under Win11 and moving it to the shared folder for Citrix would work. (I just checked and I don't see a unblock checkbox for the .accde file, even when it is in a local folder in Win11.)

Is there a way to remove MOTW for all users, or would each user have to change the context menu themselves?

Could it be done by the Citrix admins via GPO?

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

Positive:

• First off, I'm sure I added the trusted folder and I'm experiencing the slowdown.
• Secondly, as I said previously, in this case, there was no yellow banner with an option to trust the FE file, there was a red banner that content was blocked, suggesting MOTW. That seems like a likely cause, but I don't know how to resolve it.

***
Long shot - I got temporary access to the old Citrix servers and the database still runs fast there. I did a comparison of Trust Center Settings between the two OS's and there were a LOT of trusted Publisher certificate stores under the old OS and none under the new OS. The database does not use code-signing, but could this be an issue? (I'm not sure how to add trusted publishers and might need the Citrix admins to add them.)

Thanks again!

 

[NauticalGent]

No and that seems likely to be the issue. From the Front-end or the back end or both?

I would check both, and it is really easy to do. Rather than re-invent the wheel, give this article a read:

Mark-of-the-Web (MOTW) Details

Microsoft announced that VBA will be blocked soon in all files from the web. Let's dive into how Windows manages this "Mark of the Web."

nolongerset.com

TL;DR

 

[NauticalGent]

I use a right click context menu item to unblock files

I use PS as an installer/FE updater. My issue is that sometimes one of the other Dev's will push the local FE they are worked on to the Folder and forget to check/remove the mark. Adding the "unblocker" to my script takes care of that...

 

[Marshall Brooks]

MOTW was apparently the issue. I was skeptical b/c I didn't see an Unblock option on the Properties menu, and the Powershell script didn't give any confirmation, but I ran it and everything seems to be working okay.

I only ran it on the FE, but that seems to have worked.

Question: If I run the PS script on all the files in my development folder and create a new .accde from my .accdb, will the new .accde be non-MOTW, since the .accdb file was, or will I have to unblock the new file also with the PS Script?

 

[isladogs]

As the ACCDE file is created on your PC, you won't need to unblock it again.
Indeed you won't see the Unblock text or checkbox in the Properties dialog.

However if you distribute it via email or the internet, others will still need to unblock it

 

[Marshall Brooks]

@isladogs - We distribute via a network drive. I never saw the Unblock text or checkbox under Properties - even when I was having the issue, but I ran the Powershell script and it seems to have fixed the issue (for me, at least).

I ran the unblock on my shared folder and then copied it to the network drive. Haven't heard if it resolved the issue for others.

So there are now 2 questions:

• If I create the file in the development folder, do I still need to unblock it?
• If I unblock the file and copy it to the shared folder, do the other users need to unblock it still, or will it be unblocked for them like it was for me?

 

[isladogs]

As already stated, files only become blocked if downloaded from the internet or saved from an email attachment.
Therefore the answer to both questions is No

 

[Marshall Brooks]

Something happened, b/c this database was never on the internet nor sent via E_mail, but running the PowerShell script resolved the issue.

 

[Marshall Brooks]

At this point, I'm not sure if it was the Powershell script or the Trusted Publishers update. Apparently, both were done before I re-tested.

 

[NauticalGent]

Something happened, b/c this database was never on the internet nor sent via E_mail, but running the PowerShell script resolved the issue.

In my case, the Devs (3 of us), work on a copy on our local drive. When one of us needs to update the FE, after clearing it with the others, we copy it from our local machine to the centralized folder. That action alone causes the dreaded MoTW which is why added the code to my PS installer/FE Updater script.

 

[Marshall Brooks]

Waiting to hear back from my users.

I know in the past, I've done the same thing and not had the sluggishness I initially reported. And our local users, didn't report it, only the Citrix users.

And what I did earlier probably would NOT fix it from what @NauticalGent says - i.e. I ran the script on my network folder, then copied the file to another network folder and had our users re-download the file from there.

I'm thinking adding the Trusted Publishers was the fix, but it is working fine again for me, and we'll see for the other users.