Solved Automated search for Database Corruption Assistance

[The_Doc_Man]

Is there more to it than ensuring the FE is installed on the Citrix server and each users runs their own copy of it?

The MSAccess.EXE app that is part of Office must be installed on that machine. But if multiple users will run on the same machine, there can be a licensing issue since the garden-variety copy of Office is single-user. @Pat Hartman has some experience with CITRIX sites and multi-user licenses so perhaps she can offer insight.

Could the front end being on one network folder and FE being on a different network folder (neither part of the Citrix Server that is called both of them) cause the issues I'm seeing?

I am not sure I understand the configuration you just described. Are you saying that the FE runs in machine A's memory but the actual FE file is on machine B's disk?

That should run but should have absolutely abysmal performance and your network guys would probably come after you one night with torches and pitchforks. For efficiency, an app must run where the physical file embodying that app is located. Otherwise, you swamp the network with program traffic (distinct from data traffic). You would have to load forms, queries, and modules with event code to even launch the app before it touches data. Then, each form action (opening or closing) ALSO triggers data movement from the BE file. If the FE is running locally, ONLY the BE data is transferred, which is normal and what most of the world of Access users see. But if neither the FE nor the BE are local to a machine, then EVERY ASPECT of the operation triggers multiple data bursts from multiple places (FE/BE).

Would the one user running his FE on Server 1 and another user running his FE on Server 2 (from folders on the Server) cause any issues? (I'm assuming not - conceptually this would be no different from me running the FE from my local computer and another user running the front end from their local computer.)

Here, you are correct. If the CITRIX/RDP user is running the FE from a private folder on the remote server, it is the same as a user running from a private folder on his desktop machine. Physically different location but logically identical isolation. (See above regarding legalities, though.)

 

[The_Doc_Man]

@The_Doc_Man - I'll wait for the next reply, but I'm confused on part of #199.

If the second post didn't answer things, then please specify what confused you.

 

[Pat Hartman]

The FE is either local or on OneDrive. Is that still an issue? I

Even copying the FE from OneDrive is a problem unless you have some fool proof method of ensuring that only ONE user can EVER open the FE at one time. If OneDrive is involved and some other user can open the FE, you have the issue of potentially clobbering each other. Do NOT use OneDrive at all.

Notice that every time you open an Access database, whether you update it or not, when you close it the date changed is updated. That should tell you all you need to know about the danger of how OneDrive works.

Let's look at the second part of this ... Half of our team (8 users total) operates with the FE on the users desktop on the local machine (C:\Users\username\desktop). The newest team member is using a SHORTCUT on the OneDrive Desktop to open the FE in the stated folder.

I have had multiple implementations where some users used Citrix and others used their LAN to share the same BE - both Jet/ACE and SQL server or even both in the same app. That isn't a problem.

I don't know how much support I might get trying to have the FE moved to a private folder on the RDP Server. I'm not familiar with Pat's solution. Generally when there has been an issue, the Citrix team has told me that Access is not supported on Citrix.

They are wrong. They simply do not understand how to do it. Even when the FE runs on Citrix, each user MUST still have his own personal copy of the FE. The way to do this is very simple. In the script that the Citrix folks create, all they need to do is to copy the master copy of the FE from the server to the user's personal Citrix directory. That way, each time the user logs in, they get a fresh copy of the FE AND the users do not share the same physical copy of the FE. It is sharing the same physical copy of the FE that you want to avoid and the situation is the same whether you are using Citrix, a LAN, or both at once.

For one of my apps, the BE was SQL Server and most users were at the Corporate location in Connecticut. But we had other users who connected via Citrix from as far away as San Francisco and Paris and Miami. The Citrix users had connection speeds as good as or even better than the local LAN users. The process was the same regardless of which connection was used, Each user had a desktop shortcut and the shortcut ran a script that copied the master version of the FE to a local directory (for LAN users) or a personal directory (for Citrix users).

 

[Pat Hartman]

Assume you have a LAN with 3 servers. A file server, a database server, and a Citrix server. Typically if the BE is Jet/ACE, both the BE and the master FE will be stored on the file server. If the BE is SQL Server, the master copy of the FE is stored on the file server and the BE is hosted on the database server. When the BE is Jet/ACE, you can give your Citrix users a boost by using the Citrix server to host the BE database. It doesn't matter to your local LAN users whether the BE is on serverA or serverC, the app still has to pull data across the LAN from a server. However, by hosting the Jet/ACE BE on the Citrix server, yu don't change anything for the LAN users but you make the BE "local" for the Citrix users. This is the same kind of speed boost you would get from running the app with both the FE and BE on your C drive. This works because your "desktop" runs in memory on the Citrix server so both the FE and BE are on local drives and there is no LAN latency to deal with.

Unless you have a very small company with virtual servers, you would never see the SQL Server and Citrix running on the same physical server.

 

[Marshall Brooks]

I'm good, but I'll reply to clarify.

The MSAccess.EXE app that is part of Office must be installed on that machine. But if multiple users will run on the same machine, there can be a licensing issue since the garden-variety copy of Office is single-user.

I don't think that presents an issue. All of the Citrix users have the ability to launch MS Access (along with MS Word, MS Excel, and various other apps.) The issue is getting them a unique folder that they alone have write access to. I know that CAN be done, but I don't have control to authorize it.

I am not sure I understand the configuration you just described. Are you saying that the FE runs in machine A's memory but the actual FE file is on machine B's disk?

Correct - technically 4 machines involved:

User opens their laptop and launches Citrix desktop which I am going to say is running on virtual server A - but there are several Virtual Servers and they load share and they have MS Access installed. From the Citrix server, users goes to \\network_B\<username>\Frontend.accde, which only they can access and they double click and it opens MSAccess on Virtual Server A and links remotely to \\network_C\<backend_folder>\backend.accdb.

Oddly, I tried opening the fe from \\network_B on my local computer and it was abysmally slow, but it eventually worked. Under Citrix it is faster then opening the front end on my local computer directly on the network.

The way to do this is very simple. In the script that the Citrix folks create, all they need to do is to copy the master copy of the FE from the server to the user's personal Citrix directory.

The issue is there is no user's personal Citrix directory - as far as I know. Is the setup I described above problematic.

Reply #204 went WAY over my head ... I don't think non-Citrix users could access the backend if it was on the Citrix servers and they change them around fairly often. I can't put the backend on Network B b/c it is limited to the individual user. I could create restricted folders on Network_C and have the FE and BE on the same machine, but I don't know if that gains anything.

 

[Pat Hartman]

Oddly, I tried opening the fe from \\network_B on my local computer and it was abysmally slow, but it eventually worked. Under Citrix it is faster then opening the front end on my local computer directly on the network.

Not odd at all. Read my explanation again.

There is ALWAYS a personal directory.
If the FE is not COPIED from the master directory to the users personal directory, then they are sharing the FE and that is a problem. Do not allow the Citrix people to bully you into allowing this. Put your food down. Tell them how to set up the application. Tell them it's their fault if the db is corrupted because they didn't follow your directions. Explain to them, in very small words, that you would never allow userA and userB to both open the same physical copy of Word.exe from a server folder. Access is no different.

You can tell if they followed your directions by checking the change date on the master FE copy. Unless it stays firmly at the date/time you uploaded it, someone is running the app incorrectly.

I can't post the batch files I use at the moment but if you find an old thread, you should see both the Citrix version and the LAN version. The Citrix version uses tokens to format the name of the personal directory.

 

[Marshall Brooks]

We handle things a bit differently. I've seen implementations where the FE is copied from the master directory to a local directory and then opened. What we do if have a version number in a table in the backend. If that number is greater than the number in the local table in the front end, than the new master version downloaded and opened.

There is ALWAYS a personal directory.

Okay - I don't know what you mean by it, perhaps. I rarely use Citrix, but I have Access to it. In Citrix, I am instructed to save my work in \\Network B\<username>\ - which is only accessible to me (and the network admins) and which I can also access from my local computer, so it is not on the Citrix server. There is also a C:\Users\<username>\Desktop folder on the Citrix server, but it is empty and I can't write to it.

If the FE is not COPIED from the master directory to the users personal directory, then they are sharing the FE and that is a problem.

They are not sharing the FE. They are all running their copy of the FE from their Network_B folder which only they can Access. Now, what concerns me is that I don't know if they are sharing the main Access program (not sure if that is possible) and also I'm not sure how the server memory is affected since the FE appears to be on a different computer than the Access program on Citrix.

Explain to them, in very small words, that you would never allow userA and userB to both open the same physical copy of Word.exe from a server folder. Access is no different.

Not following this. UserA and UserB open the same version of Word.exe from \\Server\ProgramFiles\Word - not good. UserA opens Word.exe from \\Server\UserA\ProgramFiles\Word and UserB opens Word.exe from \\Server\UserB\ProgramFiles\Word - seems to be more what is happening, but I'm not sure if that would create issues or not.

 

[Pat Hartman]

Now, what concerns me is that I don't know if they are sharing the main Access program (not sure if that is possible)

I explained how to tell. Look at the date/time of the master copy in the master folder. If it is static, they are not sharing that file.

You can certainly "handle things a bit differently". I'm telling you how to control the process but feel free to do it your own way since you don't want to follow my instructions.

There isn't an expert here who will tell you that sharing the FE is not dangerous. You absolutely do not want the same physical copy of an Access FE opened in memory on multiple computers at the same time. PERIOD. I told you how to control the process. I also check versions but for a different reason. The version checking has nothing to do with ensuring the users always have separate physical copies of the FE. There is no reason to not replace the FE every time the database opens if that method is easier to implement, especially with Citrix. Do what you want.

 

[Marshall Brooks]

I explained how to tell. Look at the date/time of the master copy in the master folder. If it is static, they are not sharing that file.

If I went with your implementation.

There isn't an expert here who will tell you that sharing the FE is not dangerous. You absolutely do not want the same physical copy of an Access FE opened in memory on multiple computers at the same time. PERIOD.

And that is not happening. The front end won't open unless it is in a directory that only that user has Access to.

The central question at this point is whether under Citrix there is an issue with the FE being on one server folder and the BE being on a different server folder - neither of which are part of the Citrix Server.

There is no reason to not replace the FE every time the database opens if that method is easier to implement, especially with Citrix. Do what you want.

I'm convinced either method works. The advantage to your method is the user ALWAYS has the latest version of the front-end and it is simpler to implement. The disadvantage (especially for the VPN users) is that the database has to download and open everytime you open it, even if it hasn't changed at all.

It would be trivial to implement your method under Citrix, except I don't have write Access to ANY folders on the Citrix servers. I would either need the Citrix admins to set it up, or I could write a file to download the latest version to the network share, but the database does that already when I do an update - and I'm still not clear if that (having the FE separate from the Citrix server) causes an Issue.

 

[The_Doc_Man]

There is also a C:\Users\<username>\Desktop folder on the Citrix server, but it is empty and I can't write to it.

This folder that you just described is a zero, a place-holder, someone's mistaken idea of giving users privacy. I'm thinking that an apt comparison is a "screen door on a submarine." It does NOTHING for you. It has no practical reason for existence if you can't write to it. But this is the kind of very short-sighted foolishness that an inexperienced admin will do to you. I was a system admin for the U.S. Navy for 28 1/2 years. I had to educate some of the security managers about Office and its true behavior. So if this configuration was what your IT guys did for you, they did so from lack of knowledge about Windows File & Printer Sharing protocol, known as SMB or Server Message Block, and its requirements. They also didn't know or forgot that the true purpose of an admin is to NOT get in the way of a user with a legit need. We admins SHOULD have the attitude of enabling the necessary and implementing the possible. Unless you are a malicious hacker, there should be an environment where YOU as a user don't even realize that you actually ARE sharing a machine. I learned that lesson my first year on the job when a program manager and I had a long discussion about security. We came away with a compromise because I still had security mandates that I had to follow and he still had goals for his people to meet.

If that C:\Users... folder is your default folder, you start life hamstrung. Yes, there are environmental variables that one can define to point your operations to a different folder. Yes, they will work. But not with complete simplicity.

I am instructed to save my work in \\Network B\<username>\ - which is only accessible to me (and the network admins) and which I can also access from my local computer, so it is not on the Citrix server.

I'm going to presume that B represents some form of network-attached storage (NAS). If that is where the FE file is located, then I'm not surprised by your comment

Oddly, I tried opening the fe from \\network_B on my local computer and it was abysmally slow, but it eventually worked.

Look in my earlier comments from a couple of posts ago. If the FE is not local to the PC, then you not only have data traffic but PROGRAMMING traffic going across your network. Each time you open a form, you have to reload the form. I hope it loads the class module with it. Every form, every query, every report, and every module that you reference has to be loaded from the FE location to the memory of the active PC. Forms and reports close when done, and when they close, that means that they must be reloaded if you open the form again.

 

[Marshall Brooks]

We are getting away from my central question - i.e.:

I'm still not clear if that (having the FE separate from the Citrix server) causes an Issue.

This folder that you just described is a zero, a place-holder, someone's mistaken idea of giving users privacy. I'm thinking that an apt comparison is a "screen door on a submarine." It does NOTHING for you. It has no practical reason for existence if you can't write to it.

I'm not tracking here. Locally, we are using C:\Users\<username>\Desktop. I can get to that. I have other folders named C:\Users\<otherusername>\ that I can't open.

What is think it does is since the database won't open unless it is located there, it prevent two users opening the database from the same folder (the master file location on the network - for example).

Are you saying it is a BAD idea to have the front end in this folder.

From what I have seen and been told - there aren't ANY folders on the Citrix (Virtual) Servers that end users have write permissions to. Not C:\Users\<username>\Desktop, but not any other folder either.

I'm going to presume that B represents some form of network-attached storage (NAS). If that is where the FE file is located, then I'm not surprised by your comment

Sounds correct, but I'm not sure. I know we have to request it. It is typically mapped to a local drive. I can access my directory on it and subdirectories. If I am on someone else's computer, I can access their folders and subdirectories on it, but not my own.

If the FE is not local to the PC, then you not only have data traffic but PROGRAMMING traffic going across your network.

Don't know the details, but under Citrix the virtual server opens the FE on the external network and the backend on the different network extremely quickly. So I don't know what to say here ...

 

[Pat Hartman]

The central question at this point is whether under Citrix there is an issue with the FE being on one server folder and the BE being on a different server folder - neither of which are part of the Citrix Server.

I already explained that with an example. Neither the FE nor the BE needs to be stored on the Citrix server and neither normally would be. Maybe my example of three servers was too simple.

I'm not sure how the server memory is affected since the FE appears to be on a different computer than the Access program on Citrix.

That is not relevant. When you open a Word document saved to a file server, is it opened in memory on the file server??? NO, it is not. It is copied from the file server to memory of your local PC and is opened there. This is EXACTLY the way Access works (sort of). It makes no difference whatsoever WHERE the FE is saved, when you run MSAccess.exe, Access extracts parts of the file and copies them from the server and loads them into memory on YOUR PC, NOT ON THE SERVER. Citrix creates a virtual computer. It mimics an actual computer. When you open a file in that virtual PC, it opens it in memory on the Citrix server since that is where the code is running. The difference between Word or Excel and Access is that Word and Excel work on "whole" files and load the entire file into memory at one time. If there isn't enough free memory to hold the entire file, then pagination is used and parts of the file are left on work space that extends the physical memory by swapping it with pages temporarily stored on disk. Many people confuse "memory" with "disk storage". They are very different and it is important to understand the concept to follow this discussion. Whereas, Access loads an index from the physical file which tells it the actual address where certain other sections start and where they end. So, if your code references code in a module that hasn't been loaded, Access looks in its index and sees that the code is in moduleX which starts at address 2020202 and ends at 2020555. Those bytes are copied into memory and control is transferred to the code there. This is a very simple explanation of how the "partial" file access method of Access works vs Word and Excel.

It would be trivial to implement your method under Citrix, except I don't have write Access to ANY folders on the Citrix servers

And you don't need write access to any Citrix folders. You need write access to the file server folder where the FE and BE are stored so you can load new copies as necessary. It is the user's startup script that copies the file from the file server to the user's personal directory so it uses HIS permissions, NOT yours.

You need the Citrix admin to set up this process for you. You have no permissions to do this, nor should you. You are the user, you know how the app needs to work it is the Admin's job (as Doc mentioned) to implement your request. I have never had a Citrix admin refuse to set up Access correctly once he understood the issue. Some of them would argue with me but even MS recommends the "personal" copy rather than a "shared" FE.

 

[Pat Hartman]

I'm still not clear if that (having the FE separate from the Citrix server) causes an Issue.

Please read #204 again.

If your users are using Citrix, there is absolutely no reason to be using a VPN. If you are using the VPN anyway, you access Citrix from within the VPN and the FE is NEVER downloaded to your laptop, EVERYTHING happens on the Citrix server. The only things that pass through the vpn are "keystrokes" going from the laptop to the server and "images" of the desktop going from the server to your monitor on the laptop. NO DATA, NO FILES ever get passed between the laptop and Citrix.

 

[gemma-the-husky]

The hard bit that even a lot of system admins don't really get, is that logins to a RDP server, or a Citrix set up, have to be directed to a unique folder for each user.

Although in theory a single access front end database is multi user, in practice there can be difficulties and we advise against it..

One reason is that it's easy and convenient for a developer to store information about a process in a local table (ie inside the front end). If more than one user is using the database, they might interfere with that local information, which will cause problems.

 

[Marshall Brooks]

I'm feeling a bit better, but still confused.

Essentially, @The_Doc_Man is saying what I have setup is horrible for network traffic and @Pat Hartman is saying everything is normal.

No two users are opening the same copy of the FE. It is not being shared.

Please read #204 again.

#204 wasn't clear to me that first time nor the second time I read it ... from 204:

Assume you have a LAN with 3 servers. A file server, a database server, and a Citrix server.

As I understand this, in our case, the BE is on the file server, the FE is on the NAS storage the @The_Doc_Man mentioned, there is NO database server, and the Access program is running on the Citrix Server, which is virtual.

Typically if the BE is Jet/ACE, both the BE and the master FE will be stored on the file server. If the BE is SQL Server, the master copy of the FE is stored on the file server and the BE is hosted on the database server. When the BE is Jet/ACE, you can give your Citrix users a boost by using the Citrix server to host the BE database.

Actually, the BE and the MASTER FE are on the file server. Each users FE is on the NAS - which I assume is also a file server.

Unless I'm missing something, you are saying I can host the BE database on the Citrix server, but above you just said I can't, shouldn't and shouldn't need to write anything to the Citrix server, so ...

If your users are using Citrix, there is absolutely no reason to be using a VPN. If you are using the VPN anyway, you access Citrix from within the VPN and the FE is NEVER downloaded to your laptop, EVERYTHING happens on the Citrix server.

I wasn't very clear here and I'm not sure how it works. The non-Citrix users need the VPN to access the BE files on the file server. I'm not sure if the VPN is required to access the Citrix Server or not. I think it is, but I agree with the rest of the statement. Nothing happens on the laptop storage - not sure about memory.

 

[The_Doc_Man]

No two users are opening the same copy of the FE. It is not being shared.

Whatever else is going on, this part is exactly right.

My comments about having the FE directly available to the end user's PC have to do with efficiency and performance, but if you have to load things remotely, it will work. It will lose races to snails on a salted track, old turtles, and cold molasses... but it would work.

The central question at this point is whether under Citrix there is an issue with the FE being on one server folder and the BE being on a different server folder - neither of which are part of the Citrix Server.

No issue on that part of the configuration. A split FE/BE is a case of a file being in one place and a different file in another place. As long as the hosting systems in question can see each other and exchange data, no problem with connectivity. The efficiency of a particular connection is an issue. The earlier business about OneDrive is potentially an issue, though you've been attacking that. And your corruption events are another issue.

Locally, we are using C:\Users\<username>\Desktop. I can get to that. I have other folders named C:\Users\<otherusername>\ that I can't open.

That might simply be Windows "isolation" security kicking in. Old rules existed back in the days when computers were gradually infiltrating government offices on a more frequent and invasive basis. Something referred to as "The Orange Book" defined certain standards such that if a computer complied with a certain set of rules, it would have a particular security level. The U.S. Navy mandated that any computer used in any Navy office would be not less than "C2" level. Under C2 security, certain rules are relevant. BTW, the Orange Book has been superseded by a more comprehensive (and therefore less comprehensible) book so don't be surprised if you can't easily find it.

1. Fine-grained discretionary access control (DAC) - i.e. you can control specific actions for specific files and different actions for other files in the same folder. Detailed network permissions are part of DAC. The idea that permissions apply according to the current username is part of DAC as well. Object permissions and device permissions are another part of that.
2. Session login procedures are needed to establish credentials/abilities/file access rights
3. Object auditing (forensic but not necessarily fault-recovery-level audits). Recovery audits are an applications thing, most often.
4. Objects cannot be re-used without first being "sanitized" - in most common practice, erase memory before granting to a user; erase a disk block before allocating it to a user's file.
5. Isolation of resources - in practice, task A cannot interact with task B unless there is a predefined interface between them.

In case anyone was wondering, Windows NT and subsequent versions either fully complied out of the box or only needed minor option tweaks to be C2 compliant.

 

[Pat Hartman]

Although in theory a single access front end database is multi user, in practice there can be difficulties and we advise against it..

Prior to A2007 and the deprecation of ULS, this was much less of a problem. Access no longer supports multi-user updates to Objects. Data is a whole different process. The FE does not contain data - at least not data that users can change. The FE contains only application objects. I remind you again, that simply opening an Access database updates it. MS has never told us why but it does. We do know that if a user changes the sort sequence of a form for example, it can get saved unless you have not gone through the trouble to prevent the form from being saved. Therefore UserA has updated the object and when convenient, Access updates the physical .accdb. The problem is that UserB potentially has that very same object open in memory on a second computer when UserA changes the object in the physical database. You really don't want this going on.

the FE is on the NAS storage

When did the NAS come into play? Is the NAS drive defined as a standard LAN server or is it Web enabled? If it is the former, it is identical to using any other LAN file server. If it is web enabled, it is an accident waiting to happen because of everything Doc has said about protocols.

 

[gemma-the-husky]

@Pat Hartman

Pat. I often work inside the front end. For instance if I am preparing a quotation, I work on temporary tables in the front end. If the user cancels, there's no issues. If the quote is finalised, then the temporary tables get appended to the back end. So in this case having multiple users sharing the front end would cause an issue.

In another app, I have a dashboard type report using a temporary table in the front end, that gets filled by running multiple queries. Again having a shared front end database with multiple concurrent users might interfere with that.

 

[Pat Hartman]

@gemma-the-husky I don't store user data in the FE. I use a "side" BE for temporary data or use a permanent table if the BE is SQL Server where I can add/delete without causing bloat. In this case, you just have to include a UserID with each record to keep the user data separated. Also, since my distribution method always replaces the FE, no data or change can persist once the app closes. Having a "side" BE is more delicate when you need the temporary data to persist in case the user didn't finish it in one session.

But it is object updating which is what causes the update conflicts. And certain settings can get saved by Access and that updates an object.

 

[Marshall Brooks]

When did the NAS come into play? Is the NAS drive defined as a standard LAN server or is it Web enabled? If it is the former, it is identical to using any other LAN file server. If it is web enabled, it is an accident waiting to happen because of everything Doc has said about protocols.

See Reply #210.

I'm not sure if it is web-enabled or not. I tried to access it from the address bar in Google Chrome and was not able to do so. However, I was able to access the folder that the BACKEND of the database is located in through Google Chrome. Is that an issue?

Remaining questions:

• If that is an issue - I'm not sure where IT could host the backend. Is it possible for one folder on the server to not be web-enabled, but the folders above it to be web-enabled?
• Under Citrix and/or for corruption issues - is there an advantage to having the FE and BE on the same server? The current setup is working pretty well (maybe?) and I can't put the backend on the NAS b/c there isn't a folder there that all users have access to, but I could probably setup or have IT set up individual user folders on the same server as the backend is on. The database is very fast under Citrix, so I'm thinking the answer is no, but ...
• Still need to figure out what is going on with records being messed up if not related to the above.

Thank you again!!!