Solved Automated search for Database Corruption Assistance (2 Viewers)

[Pat Hartman]

I have no idea what your setup is. You have now introduced NAS drives at post #210!!! You also referenced VPN's which would not be used with Citrix

Maybe, you need to get an accurate description of how your Citrix environment actually works from your IT people.

If, you can access the NAS from your home computer using Google Chrome, then YES, it is web enabled and you can't use it to host your Access BE.

Under Citrix and/or for corruption issues - is there an advantage to having the FE and BE on the same server?

I explained this to you and why it is advantageous in my example way back. The advantage has nothing to do with corruption, it has to do with eliminating network latency as a factor in data transfer rates. If a file resides on the computer where an application is running, then you don't have to traverse a LAN to pull data from it.

An NAS drive is not an actual server. It cannot run applications like Citrix. It is essentially a single purpose device which has enough intelligence to serve up files, either locally or via web access.

 

[Minty]

@Pat Hartman I frequently use a NAS drive at home to host BE files on, and I can get to it and the files via a web based app that runs on it, no issues. It's a Synology unit.

I wouldn't try and link to the BE files from Access via the web portal route, and I think that is the key difference you are getting at?

I use it replicate customers set ups, and shared folders to check operational speed, as when everything is on my local SSD the access times are negligeable, so it's handy as a performance check.

 

[Marshall Brooks]

Let me explain the setup in as much detail as I can. I don't know if we are using NAS Drives. @The_Doc_Man mentioned them and that seems correct.

We have two classes of users. One group uses Citrix. The other group uses local resources and uses VPN (Pulse Secure/Ivanti) when working from home.

The BE file is on <Network A>\folder structure\BE.accdb. Using VPN, I can get to this folder from Google Chrome. I assume that means it is web enabled and not acceptable. You never explained, can this folder be made non-web-enabled, or do I have to find a completely different server location? The BE has been in this location for twenty-some years and there are other database backends in the same server, but we've had intermittent data issues for that long also.

Also - if it makes a difference, nobody uses a web-based interface to get to the BE files. Not sure if that matters or not ...

The non-Citrix users have the database FE on their local machines in C:\Users\<username>\Desktop\. The database checks if it is in this location on startup. And the non-Citrix users are using Access M365 64-bit.

The Citrix users have the database FE in <Network B>\Data\<username>\ I don't know if this is NAS or not. I have a folder on this server and I know that I can't get into other users folders and they can't get into mine. This network does NOT appear to be web-enabled - i.e. I can't get to my folder in Google Chrome.

I'm not sure how Citrix is set up. I go to a Citrix Portal website and click a Desktop shortcut and it opens a virtual server. There are multiple virtual servers and the virtual servers are running Windows Enterprise 2016 and Access 2016 64-bit.

If, you can access the NAS from your home computer using Google Chrome, then YES, it is web enabled and you can't use it to host your Access BE.

I'll need to look into getting this changed. Again - can I disable the BE Folder from being web-enabled, or do I need to find a totally different server?

I explained this to you and why it is advantageous in my example way back.

You did and I misunderstood twice. At first I thought you meant I should load the FE on the same virtual server that Citrix is running Access on. Then I thought you meant Citrix loads both the BE and the FE into memory so it really doesn't matter.

Either way, Citrix operates VERY quickly. Now if I could speed up the VPN operation ...?

 

[Pat Hartman]

at home

You are obviously not sharing the file with another user. SHARING is what this thread is about.

You never explained, can this folder be made non-web-enabled, or do I have to find a completely different server location?

Minty says it can as long as you are not using the web to access it. I will leave it to him and Doc to explain the details. All I know is that if you are using a cloud drive, which this emulates, each user when he opens the BE will download the ENTIRE BE database and work with it locally. Then when he closes the app, the ENTIRE BE with only his updates will be placed back on the cloud drive where it clobbers any work done by a concurrent user. How this works with the way Access opens and closes the BE multiple times during a session - unless you create a permanent connect, I can't say. If you update one record and close the form, does the changed BE get put back on the server at that point? And then you open a new form with a new query, and Access opens the BE again, is it downloaded from the server again. I don't have a network set up where I can test this. I also no longer have the NAS drive since I mailed it off to my client.

Using a BE through a VPN is very slow. If that is your only option, you have no choice.

However, if you are hosting Citrix yourself, EVERYONE can connect via Citrix whether they are in the office or not. OR the users can connect directly when in the office but use Citrix when out of the office. I would not involve a VPN when I have Citrix as an option. PS - the overhead when using a VPN is lower if the BE is SQL Server since SQL Server sends ONLY the data you request so the data transfer is minimized unless you have made the mistake of binding your forms to tables or queries with no where clauses and are filtering locally. That is the slowest method of all and you defeat an important reason for even using SQL Server.

 

[Marshall Brooks]

I don't have a network set up where I can test this.

Is there a way I can test it? It doesn't SEEM to be working as you describe. For example - we have multiple users in the database. I haven't heard of anyone saying their changes weren't saved b/c someone else updated a record in the same table. Also, someone can open a record and save it and someone else viewing the same record will see the changes. We do have issues where two users change the same record WITHOUT saving it and then Access asks if they want to overwrite the other changes or discard - which I think is normal.

Basically, we don't have issues with changes being dropped. We have issues with records being duplicated or disappearing.

I would not involve a VPN when I have Citrix as an option.

Thanks - Generally I have found Citrix faster then in-office which is much faster than VPN. The downside of Citrix is you have to wait for the Virtual Server to load, which takes almost as long as loading the database.

 

[The_Doc_Man]

OK, we have to clear the air a bit.

"Web-enabled" means that a web server has been given information about where to find the files associated with a web site. Anyone coming to that computer using HTTP / HTTPS protocols would probably find a socket waiting to serve their connection. But the host system's disk is just a disk. It has folders, file owners, permission codes, and files just like any other disk.

A Network Attached Storage (NAS) disk is just a disk, too. However, it rides a network connection rather then an ATA or SCSI or FIBER channel and it also has a microprocessor acting as its controller, with a very basic device driver, maybe just barely above the smarts of the BOOT ROM disk driver that is used when you reboot a system. The ONLY thing an NAS disk's processor can do is disk I/O and network I/O. Nothing else... for the most part.

If it is a "smart" NAS disk (high-end device) then it MAY have been set up with some sort of exclusivity with regard to inbound protocols. That is actually no different from a locally attached disk whose host system may have been set up regarding exclusive accessibility on that disk. Cloud drives often have this exclusivity and that is why putting a back-end file on a cloud drive often has protocol compatibility issues. But note that a cloud drive can be an NAS disk or a system-hosted disk. So we have to watch out about mixing labels.

An NAS drive can be set up to look like it is accessible by other protocols than HTTP/HTTPS. If it is accessible to SMB protocol, which is the Windows File and Printer Sharing protocol, then a BE can comfortably reside there. I've used that kind of drive myself many times with the Navy. The key is what protocols its controller recognizes from the network to which it is attached.

 

[Marshall Brooks]

So the BE is okay to be on a web-enabled network drive if the drive supports SMB protocol, correct? How would I verify whether it does or does not support that?

 

[The_Doc_Man]

You would have to have two things: A knowledge of what configurations the drive will support (owner's manual, probably available from the drive vendor online) and a knowledge of WHICH configuration was selected to configure the drive.

I don't know offhand of a simple experimental test because the definitive test involves something called a Sniffer, which is a network analysis appliance you hook on a network to see what traffic is on the wire. It ain't cheap but it can solve that question in seconds.

Indirect tests have the issue that as long as two users aren't sharing the BE file simultaneously, an internal lock collision won't occur anyway, so a "wrong protocol" wouldn't notice the difference right away. You need a fatal interaction to prove the point.

 

[Marshall Brooks]

Okay - I don't know even where the drive is - and I'm not sure who does, much less how it is configured. I can check.

May be able to get the sniffer set up.

You need a fatal interaction to prove the point.

How would I set that up? I'm thinking if I created a "test/dummy" BE on the network drive and had new copies of the FE to access that file instead of the real one ...

 

[Pat Hartman]

How would I set that up?

You need three people who are not on your LAN to open the FE and connect to the BE on the NAS via the internet. Someone who has set up the NAS to work this way would have to guide you. Persons 1 and 2 open the app and make the connection. Person 1 updates a record and closes the form. Person 3 opens the BE and looks to see if he sees the change made by person 1 and then he closes the db. Person 2 then makes a change to a different record and closes the form. Person 3 opens the BE and looks at both records. Are both changes visible or just one? Person 1 then goes back to the first record he changed. Is the change still there or is it gone?

 

[Marshall Brooks]

We are mixing terms again. The network that the FE is located on MIGHT be NAS, but is not web-enabled. The network that the BE is located on is web-enabled. I don't know if it is NAS, but nobody accesses it from the internet.

I asked someone in IT if the network the backend is on is configured for SMB protocol.

 

[Minty]

You are obviously not sharing the file with another user. SHARING is what this thread is about.

Yes I am, it's on the home wired network, and is accessed by other users(mainly the wife!) on other machines when I am testing. It certainly doesn't download a copy to the local machine. It acts just like a file server.

 

[Pat Hartman]

@Minty. This is not my area of expertise so I really don't want to argue with you but I just want to be clear. There are two ways to install this device. One is as a standard network drive. That is how I installed the one I bought for my client. I plugged it into my router and it was available to all the computers on my network and it worked just like any other file server. It doesn't have its own IP address. It is not available via the internet.

The second type of install is as a cloud server. Using this method, the drive can be access via the internet from outside your home network. The first method is not a cloud drive. The second is a cloud drive and according to Doc, the protocols used for accessing are quite different and the cloud protocol is not compatible with how Access files are shared.

Are you saying that you managed to install the same drive using two different methods?

 

[Minty]

@Minty.
Are you saying that you managed to install the same drive using two different methods?

No I think it was a standard install. it works as both a File server, and has a Web based management interface, that I believe if I opened the ports on my router would allow external (User/Password protected) internet access to the same files.

Locally (on my home network) I can access the files via both methods. I could turn off the web based access as I don't actually need it, but I'm pretty sure it was enabled by default. You wouldn't be able to connect to the web instance of the file, as I am pretty certain Access wouldn't see it as a valid connection. When I'm not busy (I'm preparing to go on holiday tomorrow) I'll have a play and see what it says if you try.

 

[Marshall Brooks]

Okay - To clarify some things:

• IT replied back and confirmed the server that the BE is on is SMB. It appears to be web-enabled, but none of our users access it from a web interface.
• I spoke with someone else in our department and I think I misunderstood and the FE for Citrix users is not on an NAS. I think it is on a typical shared network drive, although it is configured so that users only have access to one subdirectory.

I suspect the issues that I'm seeing (duplicated records, deleted records, improperly copied records) are primarily network connectivity issues. I have three issues to address/ask about:

• Citrix for us is configured to shut down after a set time. It's a rather long set time (3-5 or maybe 8 hours), but it is not an idle timeout. IOW, you can be in the middle of working in Citrix and a purple bar goes across the screen with "Shutting down in 2 minutes unless you click OK." I'm not sure how elegantly it shuts down - i.e. does it close all running apps and shut down, or does it just remove the virtual server? If it does the latter and one of our users has a bound form open, would that potentially cause issues? If so, I'm not sure what I can do about it. We've asked the Citrix support group before, but haven't been able to discern much.
• There are 4 ways that our non-Citrix users connect to the database:
  • In the office via wired Ethernet, which is fast but can be spotty - i.e. The network connection was lost, please close and re-open the database.
  • In the office via wireless Intranet - same as above. For some users the wireless in-plant is more stable than the wired. Typically not much of a discernable different.
  • From home via VPN (Pulse Secure/Ivanti) - Very slow. Connectivity varies, but personally, I'm not sure it loses the network connection more often than in the office.
  • Technically, all of our non-Citrix users do HAVE the ability to open the database in Citrix, either from home or in the office. I used to do this from home when I was working intensive tasks in the database. I've started doing it from home whenever I need to use the database. But I don't think just suggesting that users do this will work. Questions:

Using VBA, how can I determine whether a user is using VPN or Intranet? I thought this or similar might help: https://stackoverflow.com/questions...ffice-intranet-or-office-wifi-using-excel-vba but that only verifies that they are connected to EITHER Intranet or VPN, not which one?​

​

If I determine that they are using VPN, I'm not sure how to force them to use Citrix. Currently, the database only opens locally from the desktop or from the shared drive (Citrix). If I wanted to ONLY allow Citrix usage, I could disable the desktop. Technically, that doesn't FORCE them to use Citrix, but I tried once and it took about 8 minutes for the database to open from the network path outside of Citrix.​

• I'm not crazy about it, but I'm considering adding an idle kickout to the database that would occur after say 20 minutes of inactivity. If I did this, would it be okay (from a corruption standpoint) to just close all bound forms and return them to the switchboard rather than closing the database? This might help with the Citrix timeout issue, although users could still open the database 15 minutes before Citrix timed out and still have problems.

Thanks for sticking with me!!!

 

[The_Doc_Man]

I'll answer what parts of that question that I can.

If your CITRIX session dies simply by breaking the connection then you have a "dangling" process wherever Access was physically running. When that dangling process gets resolved (if it ever does), then is when corruption can occur. However, if in the interim you have a lock collision with another user working the specific area where the dangling process was working, that can hasten the demise of the situation.

The network status of VPN vs. ordinary Internet/intranet connection is going to be tricky because that is in the data link layer (below the end-to-end layer, which is layer 3). Only the network driver itself will know. I found one reference that involves writing a function to return T/F based on VPN or not using the Win Management Interface to query the connection properties.

Determine if connected to VPN or Office Intranet or Office Wifi using excel vba

I have the following code with which i am successfully checking if i am connected to VPN from home to access company network folders. Sub doit() If ConnectedToVPN Then ' run other code to ...

stackoverflow.com

Do not use an Idle Kickout without taking into account the possibility that there might be something active. In a properly secured DB, this something else can only be a form, which can have its own timer. What I did was I had a Public flag in a general module and it was, essentially, a "permission to run" flag. If I wanted a shutdown, I set the flag ahead of time and waited. Every form had a timer event that tested the permission flag every 10 minutes. If it ever saw "no" then it forced the form to start shutting down by determining whether anything was running. If the form was currently dirty, it forced a timed popup that exited after 10 seconds. It ran a Me.Undo and then told the form to close. The timer was on a form that knew to check for other forms and not release itself until the Forms collection showed no more active forms (than itself). At which point it did an Application.Quit for Access itself.

 

[Marshall Brooks]

@The_Doc_Man - Thank you - I'll take some time to digest this and see where it takes me!