Access on Citrix reality check and reassurance

[CJ_London]

Anyone have some experience with Citrix?

One of my clients has decided they are going to use Citrix and wants to put both front and backends (both access 2010) on the server.

I've had databases run on Citrix before (front and backends) without any real issues however this one is a little bit different in that in order the db can do what it needs to do requires the creation of temporary tables and also the manipulation of forms (hiding and/or repositioning controls). I also have the situation that they are using 64bit office (not a problem in principle but included for background) and now have a mixture of Windows7 and Windows8 operating systems and some users are using Office/Access 2010 and some are using runtime.

The temporary tables were a bit of a bugger but is now solved. Before they were created in a temporary db in the same directory as the front end (on the users machine), now they are are created in the users personal space in Citrix. And I can no longer create a temporary linked table to the temporary table but have to build a connection string to use in the form or control recordsource.

My concern is primarily to do with the forms and the mixture of operating systems.

Forms first: My understanding is that when a users opens Access on Citrix they are opening an instance of it, so since each user has their own instance any changes to a form e.g. recordsource (changes are not saved) does not affect any other current user or new user. Is this correct or have I more work to do?

Operating Systems: Is there any chance that different operating systems and the use of Access 2010 or runtime can affect things like references in VBA which can affect other users. In a 'standard' setup with the front end on the users machine this would not matter, but just a reality check.

Something I haven't tried yet so don't know, but is it possible to have the front end on the users machine as before but linking to the back end on citrix? This would require an additional login to citrix as part of the opening the db process. If so it would make me happy though it does depend on whether it affects performance and the willingness of the IT manager to go this route.

Finally, has anyone any suggestions how I can mimic a citrix environment on my office setup which is a simple network of 4 pc's/laptops and small server. Would it be as simple as creating a password protected partition on a drive somewhere?

Many thanks in advance

 

[James Dudden]

Hi CJ,
I have worked with Citrix a little and have found that Access works pretty much the same as if it were on a remote server with a shared front end i.e. each user has their own 'instance' of the front end so changing things like the record source would be specific to that user.

As far as different versions go I think you'd have the same issues you would on a normal network with a shared front end i.e. not really feasible.

One other thing that was a negative was the speed was quite slow with multiple users accessing the same front end and in the end my client moved their systems to a hosted remote server. This may have been more to do with the hardware though.

 

[Pat Hartman]

You have to instruct the person setting up the citrix environment that every user MUST have his own copy of the FE. Otherwise, they will set it up to use a shared copy. Impress on them the importance of each user having a copy. I had a lot of trouble convincing my guys that I was right. Sounds like this may have been the problem that James experienced.

but is it possible to have the front end on the users machine as before but linking to the back end on citrix?

No. You could do this with VPN but most people report slowness when using Access this way due to the amount of data Access pulls over the net. Citrix doesn't have this problem since everything runs on the server so technically everything is "local" and only pictures are sent to the desktop.

You can't really mimic Citrix since there are environment settings etc. You should have your own account to test with. You also need to work out how new copies of the FE will be distributed. Usually you would have to send them to the admin and he would take care of getting them published the next time folks logged on.

Technically different OS's won't be a problem since each PC is running a browser based app. The Access application runs on the Citrix server so it doesn't have a mixed environment. Also, the version of Office installed on each PC is irrelevant, again because the app is running on Citrix. Whatever is installed in the Citrix environment is what they will use. I'm hoping for your sake that the setup is consistent. One of the benefits of a Citrix environment is ease of maintenance for the IT staff so although every user needs a license, they all use the same software.

If it turns out you have to deal with a 64-bit version of Office, you will need to modify any APIs you use to use pointer safe variables. If you have to support a mixed environment, you either need two completely different versions of the app or you need to use conditional compilation so that the 64-bit code executes in that environment and the 32-bit code executes in that environment.

 

[CJ_London]

Hi James,

Thanks for the feedback, I've been doing some more research and as you say the risk of front end corruption is the same as if you have a shared front end in a 'standard' network.

I have discovered the recommended solution is to put a copy of the db in a directory unique to each user so they all have their own copy - some systems have a 'home' directory for when working from home which is actually located on their PC so not to be used otherwise there can be performance issues.

I've now created a 'redirection' db which does the following:

[FONT=Calibri][COLOR=#1f497d][COLOR=#1f497d][FONT=Calibri]a.       [/FONT][/COLOR][COLOR=#1f497d]Checks for the existence of a directory called xxx in the users space [/COLOR]
[COLOR=#1f497d][FONT=Calibri]             i.      [/FONT][/COLOR][COLOR=#1f497d]If it doesn’t exists, create it[/COLOR]
[COLOR=#1f497d][FONT=Calibri]b.      [/FONT][/COLOR][COLOR=#1f497d]Check for the existence of a file name matching the name of the master front end versioned number in the xxxx directory[/COLOR]
[COLOR=#1f497d][FONT=Calibri]             i.      [/FONT][/COLOR][COLOR=#1f497d]If it doesn’t exist[/COLOR]
[COLOR=#1f497d][FONT=Calibri]                       1.       [/FONT][/COLOR][COLOR=#1f497d]Delete any files in the xxxx directory named 'like' the name of the redirection db[/COLOR]
[COLOR=#1f497d][FONT=Calibri]                       2.       [/FONT][/COLOR][COLOR=#1f497d]Copy the master version to the xxx directory[/COLOR]
[COLOR=#1f497d][FONT=Calibri]c.       [/FONT][/COLOR][COLOR=#1f497d]Open the front end copy in the user space[/COLOR]
[COLOR=#1f497d][FONT=Calibri]d.      [/FONT][/COLOR][COLOR=#1f497d]Close the Redirection db[/COLOR]
[/COLOR][/FONT]

This also neatly takes care of versioning and setting up new users- every time the user logs on the the server the redirections db checks for a new version and updates it before opening

I'm also going to modify the front end to have a message appear on the switchboard form - every time it is refreshed it checks the two file names, if there is a change it will display a message saying there is an update available and invites the user to log out and in again.

I'm feeling a lot more confident now. Once again many thanks!

 

[Pat Hartman]

It's been a while since I had to do this and I don't have the database handy but in one instance I needed to be able to detect whether the app was running "local" or in Citrix and I used an environment variable to tell me. What I don't remember is who created the variable. I think the Citrix admin did.

If Environ("somevariablename") = "Citrix" Then

 

[CJ_London]

Hi Pat,

Feeling doubly confident now Will probably be my downfall

They have been using 64bit for some time so hopefully a mixed environment will not be an issue, (if it is I can modify my redirection db), so it is already pointer safe. The only issue has been that some users have been on a full version and others the runtime version (which played havoc with the shortcut menus). In the Citrix environment it will either be the same or they will have installed full versions for all users, either way the issue has been addressed.

It has been tested and seems to work OK, the only issue seems to be when creating PDF reports (using docmd.outputto ...acFormatPDF...) which works fine if created to the server but poor if the uses selects a location on the 'normal' network.

Still trying to figure out why, but guessing at the moment that it linked to network speeds. The speed difference is quite horrific - 40 minutes to produce 10k documents on the server, 80 hours to the network! May have to modify the routine to create to the server and copy completed files to the network.

Thanks again

 

[Pat Hartman]

I would have them create the pdfs on the server drive and then copy them using FTP or VPN.

 

[CJ_London]

I would have them create the pdfs on the server drive and then copy them using FTP or VPN

Hmm, not used those before in a vba environment, just used FTP 'manually; to copy stuff to a webserver.

The users are hopelessly none technical, they struggle to even change the default printer. Would I have to 'pull' the data from Citrix to their network, or can I 'push' it from Citrix?

The users will only have access to the front end so that acts as a portal to the other files and directories on Citrix - primarily the document store, but if they use the windows navigator opened from within the front end they cannot see the Citrix server at all, only the directories on their machine and network.

I can set it up so that they select a location they can see, the createpdf writes to Citrix and then I can filecopy to their selected location - but would FTP or VPN be significantly quicker than filecopy?

Your thoughts are much appreciated

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?p=1302223#post1302223
This may be of interest to you on the new Access Web folder.
This new folder was recently created to include Citrix discussions.

Wanted to list it with tags Citrix, Bat File, ...
This was invaluable to me several years ago. Took me a long time to dig it back up.

 

[CJ_London]

Took me a long time to dig it back up

I appreciate you taking the time, I've actually just created something similar which works in a normal network environment, will be trying it on Citrix next week.

The client's IT dept have set up a shortcut on each registered users machine to open the front end on Citrix so I couldn't use versioning in the db name, it always had to be the same name. So the way this works is I will rename the front end with a version number, and create a new db named the same as the front end but without the versioning suffix. The new db has the following code, called from either an autoexec macro or a startup form - probably the former.

The requirement is that the directory can contain whatever files are required but the new db and the front end have the same name with the front end having a version suffix and there are no other files that start with the redirection file name (they also both need to be the same file type but this could be modified) e.g.

myFrontEnd v4.13.accde
myFrontEnd.accde

The only thing that needs to be decided is the name of the directory to use or create in localappdata.

The code also automatically caters for new users and updates - all the IT dept need to do is add the required shortcut to the users desktop. More detailed security is handled within the front end.

Option Compare Database
Option Explicit
 
Public Function UserAppExists(Optional AppDir = "AppDir") As Boolean
Dim WS As Object
Dim FSO As Object
Dim UsrDB As String [COLOR=seagreen]'name of existing user front end in user application directory[/COLOR]
Dim UsrPath As String [COLOR=seagreen]'required path to user directory (localappdata) where user front end is located[/COLOR]
Dim DBPath As String [COLOR=seagreen]'path to application directory where master front end and this db are located[/COLOR]
Dim DBName As String [COLOR=seagreen]'name of db without versioning - i.e. the name of this db[/COLOR]
Dim VerName As String [COLOR=seagreen]'name of db with version[/COLOR]
Dim UsrApp As Access.Application
 
On Error GoTo RedirectionErr
 
    [COLOR=seagreen]'get required path to user application directory[/COLOR]
    Set WS = CreateObject("WScript.Shell")
    UsrPath = WS.expandenvironmentstrings("%LOCALAPPDATA%") & "\" & AppDir
    Set WS = Nothing
 
   [COLOR=seagreen]'get path to application directory[/COLOR]
    DBPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
 
    [COLOR=seagreen]'get application name without version[/COLOR]
    DBName = Mid(CurrentDb.Name, InStrRev(CurrentDb.Name, "\") + 1)
 
    [COLOR=seagreen]'get application name with version[/COLOR]
   [COLOR=seagreen]'note this assumes there is only the latest version in the directory[/COLOR]
[COLOR=seagreen]   'and previous versions have been deleted or archived[/COLOR]
    VerName = Dir(DBPath & Replace(DBName, ".", "?*."))
 
 
    Set FSO = CreateObject("Scripting.FileSystemObject")
 
    [COLOR=seagreen]' if user application directory does not exist, create it[/COLOR]
    If Not FSO.FolderExists(UsrPath) Then FSO.CreateFolder UsrPath
 
    UsrPath = UsrPath & "\"
 
    [COLOR=seagreen]' if application does not exist or does not match the current version delete and copy the new version[/COLOR]
    If Not FSO.FileExists(UsrPath & VerName) Then
 
       [COLOR=seagreen]'delete any existing versions of the front end in the user application directory[/COLOR]
        FSO.DeleteFile Replace(UsrPath & DBName, ".", "*.")
 
       [COLOR=seagreen]'copy the new version from the application directory to the user application directory[/COLOR]
        FSO.CopyFile DBPath & VerName, UsrPath & VerName
 
    End If
 
    [COLOR=seagreen]'open the front end in the user application directory[/COLOR]
    Shell """" & SysCmd(acSysCmdAccessDir) & "msaccess.exe" & """" & " " & """" & UsrPath & VerName & """", vbNormalFocus
 
    Set FSO = Nothing
    Application.Quit
    Exit Function
 
RedirectionErr:
    Select Case Err
        Case 53: [COLOR=seagreen]'file not found, can be ignored[/COLOR]
            Resume Next
        Case Else
            MsgBox "There is an untrapped error - Err " & Err & vbCrLf & Err.Description & vbCrLf & "Please report this error code and description to your administrator" & vbCrLf & "This application will now close", vbOKOnly, "REDIRECTION ERROR"
            Set FSO = Nothing
            Application.Quit
    End Select
 
End Function

 

[Pat Hartman]

but is it possible to have the front end on the users machine as before but linking to the back end on citrix?

Let me revisit this question. If the Citrix server is on your LAN, then folks on the LAN can link directly to the BE that is on the Citrix Server. Folks who are not on the LAN would use Citrix to open the FE. So, one of my clients had most of their staff in Farmington, CT but remote users in San Francisco, DC, Boston, Paris, and London. Paris and London were a little slower than the East Coast but not terrible. The Californians didn't care

 

[CJ_London]

They are all remote - the citrix server is in Manchester and the users are all in Oxfordshire (UK!).

However the proposed solution seems to working!

 

[Rx_]

LOL California didn't care...
Yes, I always said California users always thought the programs ran much, much faster than the rest of the users.

They should be able to push out a Citrix client that adds a menu of applications (such as your Access FE). Let me highly suggest you have a myFrontEnd and a myFrontEnd-TEST
Each should have its own copy of the Back End.

Copy the production back end every so often so the Test is up to date. It is very useful for testing or for users to try-before-you-release into production.

If you are using SQL Server, backing up the production into the Test back-end is very simple.

My Production Launch name is always the same. I might suggest taking the old version and naming it myFrontEnd_RetiredOn2013_10_20

I just had a new Office DLL library updated automatically on my development workstation. The IT staff has been a little late to match the update on Citrix.
My last release failed because the Tools References point to a newer version of a library than what is on the Citrix MSAccess. By having the "Retired" naming, I was able to very quickly replace the old working version.

Mental Note to Self: Check all your Tools - References on your development workstation and then make sure they are the exact version running on Citrix.

Someday, I need to write some code to do that automatically.

One more thing. When Citrix runs - it checks a function to see "Is User Rx" - if it is True then of course I get to see some menus/ objects that nobody else ever gets to see. In those rare cases where citirx is behaving a little different than the development workstation, that can come in handy.

 

[Rx_]

FYI: Just posted another thing for you to consider on the Access Web forum.
This is a show-stopper for me at the moment on my Citrix deployment.

 

[CJ_London]

Thanks Rx.

I've just deployed and everything seems to be working OK at the moment. Had to tweak the code I posted a couple of posts back.

Everyone is on Office 2010 with full versions at the moment and front ends automatically copied to the localappsata directory so I expect I am going to hit this issue at some point, so good to have the solution to hand!

 

[andrrivalle]

I like your comments that everyone is on Office 2010 with full versions at the moment and front ends automatically copied to the localappsata directory so I expect I am going to hit this issue at some point, so good to have the solution to hand!