Running Access in a Citrix Environment

[rjgriffin46]

We are looking to move a small database to a Citrix environment with all 5 of the users sharing the same front end. I've always understood that all users must have an exclusive copy of the front end. I'd like to hear from anyone currently operating a multi-user Access database in Citrix if they are using a single front end file, a copy for each user or have a procedure that deploys a separate copy whenever a user opens the database in Citrix. Finally, what problems, if any, have been encountered, how were they resolved.



Thanks

[isladogs]

ALL split databases should ALWAYS be setup with each user having their own copy of the FE on their own hard drive.
Sharing a FE WILL cause corruption at some point using a LAN and is even more likely using Citrix as latency times will inevitably increase

Fix your current setup as a priority.
After all you wouldn't allow all your users to drive the same car at once as it will crash. Same with Access.

[CJ_London]

I don't at the moment, but have in the past.

We are looking to move a small database to a Citrix environment with all 5 of the users sharing the same front end. I've always understood that all users must have an exclusive copy of the front end.

Cannot emphasise strongly enough, regardless of the environment, each user must have their own copy of the front end. In Citrix, each user has their own 'space', equivalent in concept to their local drive, put a copy there. Ultimately, Citrix is just a server like any other so the procedure for deploying copies to each user is the same, just the directory names change - there are plenty of examples on this and other forums. Since the front end is static, subject to updates, I use a routine to check if there has been an update and only copies the front end across if there has been one.

On problems - I have code which checks the monitor size/resolution of the device that is using access and adjusts form size and layout accordingly - there is a big difference between using a 26" monitor and a 10" tablet if a user needs to be able to work effectively. When using citrix this is not possible - it does not know what device the user is using, so I have modified the login routine to give the user an opportunity to select the device they are working from - on first use, questions are asked about size, orientation and resolution and this data stored to be used when required.

[rjgriffin46]

I am most appreciative of your comments.

ridders52: you stated that Sharing a FE WILL cause corruption at some point using a LAN and is even more likely using Citrix as latency times will inevitably increase. I would like to do some testing and see for myself what might happen if for no other purpose than to demonstrate to our IT staff. I mentioned that there are only 5 users and their usage will be intermittent. So I have these followup questions:

1. How long might it take before corruption occurs?
2. What kind of activity should I attempt in a test environment that would be most likely to corrupt the front end?
3. Is there any risk of the back end being affected?

Thanks

[isladogs]

In answer to your questions.
1. Impossible to say. Depends how often multiple users are running it simultaneously.
2. From 2 or more workstations, you could for example try opening the same form to the same record and edit the data then close the form.
Try compacting the front end on one PC whilst still open on the other.
3. Yes there is every chance the backend data will get corrupted.

Create backups of FE and BE first and preferably test on these backup copies.

[rjgriffin46]

Will do, thanks Colin!

[isladogs]

I think people will be interested in your results - specifically what triggers FE corruption and what causes BE corruption.

Another thing you could try is renaming objects such as forms or queries or table fields whilst another copy is open.
I've never done a systematic test like this but I NEVER have a shared FE

[CJ_London]

think you will have a right old time trying to prove anything. The best you will do is perhaps prove a reason rather than identify all reasons, but would be interested in what you discover.

An access file is opened 'live', unlike files from other apps. A simple example, note the datemodified value of an excel file, then open it then close without saving - the datemodified value will be the same. Do the same with an access file (don't open a table, form report etc, just close it) and the datemodified will have changed - the simple action of opening the file changes it. You could use some file comparison software to see what has changed but don't know whether you could translate that into something useful.

Assuming your access file is a front end, you open a form - what changes to the underlying file are made? perhaps you set a sort order or filter, either manually or using code. I know that will be saved on a yes prompt when the form is closed - but perhaps its saves the changes as you make them and then undoes them on a no prompt? I don't know, just speculating, any event might be modifying the actual file. And two users modifying apparently unconnected objects might in some way 'unbalance' the file so it becomes corrupted.

The problem with corruption is it can be insidious - everything seems OK, your get an error, you compact and carry on. Then it happens again and this time compact does not work - so you revert to a backup. Trouble is that backup carries the original corruption - and so it goes on.

You ask how long - you will find threads on this and other forums where it is a matter of days or weeks. At the other end of the scale I took on a client who had not split the db and about 10 users were using it daily. It had worked well for 8 or 9 years, then corrupted totally, their last backup was at the year end about 8 months previously. I was brought in to retrieve the situation - it took over a month to rebuild 8 months data, split the db and provide users with their own front ends.

[rjgriffin46]

Thanks again for this useful information.

I will report my results.

[rjgriffin46]

Tests Results.zip

After a long hiatus I have an update to this thread.

At the suggestion of our IT staff, we have tested a configuration where a single front ends is accessed via Citrix by every user. The back end property attributes allow full editing but the front end is read only. Right now they both are located on a network share drive but the long term goal is to move all files to cloud storage.

In testing (see attachment for results), with 2 users opening the same front end at the same time, one from a Windows PC and one from a Chromebox, I did not experience any problematic behavior and in fact it seemed as if each user had their own copy of the front end.

Note that this is a relatively simple application. I don't have any code that modifies forms or their properties. I did have to make 1 accomodation for the front end to operate read only and that was to eliminate creation of a local temp file via a query that is a report record source.

You may note in the attachement that I've asked for documentation on the workings of Citrix to better understand what happens when more than 1 user opens the front end. But based on these test results I would be comfortable moving the 5 users to this set up.

As always, your comments would be welcome and appreciated.

[CJ_London]

with 2 users opening the same front end at the same time

2 users using the same front end at the same time runs the risk of corruption. Maybe not today or tomorrow, maybe not next month. But it will happen at some point, and it may well creep up on you - a small scratch here, another one there and then the wheels fall off. Although not possible, it is much the same as if two users ran the same copy of Excel at the same time - although the front end is a file it is also an application.

However since the front end does not contain data, you can simply replace the file in the event of corruption. Just be aware that corruption could affect the backend. You can protect yourself better if you make the front end a .accde rather than a .accdb but the overriding advice is that users each have their own copy of the front end - just as they have their own copy of excel.

I don't have any code that modifies forms or their properties

you may not, but that does not mean there aren't modifications behind the scenes. For example a user applying a filter or sort will modify the form properties.

I'm not convinced that an access FE opened read only is actually read only. The only way to be sure is to make the directory in which it resides read only.

In summary, use a .accde and you will minimise the risk of corruption, but that risk is still there. Ensure you take plenty of backups of the backend and keep a clean copy of the front end safe somewhere.

[pbaldy]

I used to develop in this environment. I had quirky problems until I changed my updater to put the front end in user-specific subfolders. It may seem like your users have their own copies, but they don't (or not completely anyway).

[Micron]

The only way to be sure is to make the directory in which it resides read only.

My thinking is that this is not possible. User would not be able to create, edit or delete .laccdb or .ldb files.

Regarding the recent comment (which seems to be overlooked) about the cloud: there was an excellent reply over in AWF regarding using db's on cloud services like One Drive and why this is really a non-starter. Wish I had bookmarked it as the notion seems to be coming up at least once per week now.

Surprised I found it because AWF search tool isn't the greatest. Best to read all the comments, but post 12 is the one I was thinking of.

https://www.access-programmers.co.uk....php?p=1602987

[rjgriffin46]

Reponeses to your comments which are most appreciated:

1. The front end file is read only, per the file properties. I don't know if that is set at the folder level or at the file level. I will confirm.
2. I am asking our IT team for clarity on the question of whether or not or to what extent each user has their own unique, separate "working" copy of the front end opened via Citrix.
3. I'm not sure the AWF discussion is relevant as we are only using Citrix at this time and Cloud deployment has yet to be addressed.
4. I attempted publishing as an Accde but get an "..unable to create..." error with the following help text:
   
   "This error is usually associated with compiling a large database into an MDE file. Because of the method used to compile the database, a considerable number of TableID references are created for each table. The Access database engine can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
   There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used."
   
   I wouldn't have thought the database has enough objects, maybe around 180 counting tables, queries, forms, reports, macros and modules that appear in the navigation pane.
   
   
   

[pbaldy]

2. They don't if they're opening the same copy of it. Might still work, depending on what you're doing, but I wouldn't trust it.

4. The usual suspect is code that won't compile. In the VBA editor, click on a Debug/Compile... and fix any errors found (keep compiling until no errors are returned).