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.
@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.
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.
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?
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 ...?
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.
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.
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.
"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.
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?
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.
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 ...
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?
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.
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.
@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?
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.
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:
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.
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.
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.
Okay, I'm having a weird issue. I have the idle logout code working. I didn't take into account the form possibly being dirty and I probably should, but I don't think that is causing the issue.
I did the logout code differently than most examples. Our database has a switchboard and 7 data forms typically in use. I wanted code that would close the data forms after 15 minutes of inactivity, leaving the switchboard and the database still open.
With the idle logout code running, we are getting a "Type Mismatch" error whenever the MSO FileDialogFilePicker prompts to select a file or folder. Usually it gives an error the first time, and then it runs correctly.
Skeleton code looks like this:
Code:
Sub Error()
Const msoFileDialogFilePicker As Long = 3
Dim objDialog As Object
Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
With objDialog
If .show = -1 Then 'Ok Pressed
' Do Something
Else ' Cancel Pressed
Exit Sub
End if
End Sub
I commented out lines and figured out the Type Mismatch is from the line If .show = -1, although that seems to be correct.
I tried rewriting it to:
If .show = 0 ' Cancel pressed
Exit Sub
Else ' OK Pressed
Do Something
End if
That seemed to work so I changed most of the database (that code is used many places), only to find out that it also fails initially and works the second time.
I could avoid the issue by removing the IF-Then block, i.e.
.Show
' Do Something
and just hoping that users don't click Cancel, but that is a rather crude workaround.
The idle logout code looks like this on each data form:
Code:
Option Compare Database
Option Explicit
Dim lngActivityCounter As Long
Code:
Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
lngActivityCounter = 0
End Sub
Code:
Private Sub Form_Timer()
' https://www.accessmvp.com/JConrad/accessjunkie/kickoff.html - Item 23 Kicks off if no mouse movement or Keyboard movement. and T2 Database - MB 8-Jul-2024.
On Error GoTo form_timer_err
Static last_ctl As String
Dim curr_ctl As String
curr_ctl = Nz(Me.ActiveControl.Name, "No Active Control")
Me.TimerInterval = 30000 ' 30 seconds
strResult = ""
If last_ctl = "" Or last_ctl <> curr_ctl Then
last_ctl = curr_ctl
lngActivityCounter = 0
Else
lngActivityCounter = lngActivityCounter + 1
End If
'15 minutes = 900 sec (15 min x 60 sec/min)
'After 15 minutes (900 sec/min) of no activity on this tab,
'close this tab
'TimerInterval=30 seconds, so 30 counts
If lngActivityCounter >= 30 Then
strResult = Dialog.Box(Prompt:="The <form name of inactive form> will close due to inactivity.\n\nClick OK to close immediately.\n\nClick Cancel to cancel closure." & "", Buttons:=(1 + 48), TITLE:="Inactivity Timeout", AutoCloseSec:="30")
If strResult = vbOK Then
DoCmd.Close acForm, Me.Name, acSaveNo
Else
lngActivityCounter = 0
End If
End If
form_timer_err:
If Err = 2474 Then
Resume Next
End If
End Sub
I'm not seeing why I am getting the Type Mismatch with the timer code in place and not without, and I'm not seeing why the code works the second time I run it, but not the first.
More disclosure. The entire with block looks like this:
Code:
With objDialog
.AllowMultiSelect = False
.TITLE = "Please browse for Draft PDF Document to send to review."
.ButtonName = "Select"
.Filters.Clear
.Filters.ADD "PDF Files", "*.pdf"
' Typically Access will open the Documents folder and then the last selected folder. The code below makes it open the writers folder initially and then the last used folder. Otherwise, initialFileName would ALWAYS open the writer folder.
If FileDialogDisplayed = False Then
.InitialFileName = Nz(ELookup("[Network_Path]", "[tblTeam]", "[Assignee] = '" & Assignee & "'"), "")
End If
If .show = 0 Then ' Cancel pressed
Box ("No Files Selected. Exiting")
Set objDialog = Nothing
Exit Sub
Else
FileDialogDisplayed = True
AttachFile = .SelectedItems(1)
' https://stackoverflow.com/questions/12687536/how-to-get-selected-path-and-name-of-the-file-opened-with-file-dialog
Dim FileNameOnly As String
FileNameOnly = Dir(.SelectedItems(1))
' If UCase$(Mid$(objDialog.SelectedItems(1), InStrRev(objDialog.SelectedItems(1), ".") + 1, Len(objDialog.SelectedItems(1)))) <> "PDF" Then
If UCase$(Mid$(FileNameOnly, InStrRev(FileNameOnly, ".") + 1, Len(FileNameOnly))) <> "PDF" Then
Box ("Selected file MUST be a .PDF File. Exiting.")
Exit Sub
End If
End If
End With
I thought the FileDialogDisplayed = False block could be doing it, but I still get errors without that.
Initially, I had the idle timer checking once a second instead of every 30 seconds and I thought I was getting errors more frequently with that, but it might have just appeared that way.
This is less of a problem question, and more of an understanding question. I stole the code below: Dim fd as Office.FileDialog FileDialog. Set fd = Application.FileDialog(msoFileDialogFilePi...
stackoverflow.com
Replacing "If .show = -1 Then" with "If .show Then" still gives the Type Mismatch error also.
I can avoid the error by turning off the timers before the file picker dialog opens and then turning them back on after it closes.
I'm not sure why that is required, but if someone if running this procedure, then they aren't idle.
I did find out I will need to turn off the timers for ALL the open forms ... i.e. this code is associated with Form A, but if Form A and Form B are both open, I need to turn off both timers or I will potentially get the error.
