Detect BE connection lost and handle it (1 Viewer)

[Babycat]

Dear all

I have an Access app which splited BE abd FE within LAN.
User1: FE + BE
User2: FE only

When user1 turns off his PC, therefore User2 FE has no BE and sometimes it causes Access app on User2 hang up.
What could be the proper way to approach and handle this issue?

I used to deal with this in microcontroller programming by using interrupt from watchdog timer to exit infinite loop/hang up issue.
But how in Access?

Thank you.

 

[The_Doc_Man]

Unfortunately, that "watchdog timer" method you described won't work because you cannot get to the actual code that would go bonkers. It is not your event code but rather is some chunk of code in the MSACCESS.EXE program image that we can't get to. Access is very much like a "black box" that we don't get to examine internally.

The only real solution is to enable error trapping and see what error number you get when that happens, then trap the error and cause the application to quit more gracefully on you. However, it is possible that you would be unable to get to everything because you would potentially have several recordsets open at the time. Cleaning up all of those loose ends after a BE file disconnect can be a nightmare.

Also note that you have built your dependency into this structure. Is there a computer in your office that is never turned off and that you could use as a file server? That is where your BE should go for sharing purposes.

 

[Babycat]

I was hope Access could have something like interrupt event to handle urgent/higher priority tasks.
My company does not have "server computer". The user1 has always go to work early in order to get BE ready for other users

 

[Edgar_]

Never done it, but you could probably add a "folder exists" check before some form events like before update, maybe also form open. If the network folder does not exists, then just close the app.

Function isAvailable() As Boolean
    Dim folderPath As String
    Dim objFSO As Object
    Dim objFolder As Object
   
    folderPath = "\\PATH\to\backend\folder"
   
    Set objFSO = CreateObject("Scripting.FileSystemObject")
   
    If objFSO.FolderExists(folderPath) Then
        isAvailable = True
    Else
        isAvailable = False
    End If
   
    Set objFolder = Nothing
    Set objFSO = Nothing
End Function

And use it like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not isAvailable Then
        Cancel = True
        Msgbox "Can't continue, the app will close because the backend folder is not available..."
        Application.Quit
    End If
End Sub

 

[Gasman]

Don't switch off the computer?

 

[The_Doc_Man]

@Edgar_ - your method might prevent opening the 2nd FE when the BE isn't there in the first place, but I took the question as trying to gracefully shut down after the BE vanishes while the 2nd FE is open.

I was hope Access could have something like interrupt event to handle urgent/higher priority tasks.

There is very little that you could do. In Windows there is such an interrupt event, but the problem is that MSACCESS.EXE has to intercept that event and pass it along to your app, and then YOU have to detect the event, which will probably count as an error trap of the "On Error Then..." variety.

The good news is that there is at least one likely error for this situation, which means Access DOES have a trap for that. But the bad news is that every form you create needs to have a Form_Error event routine to assure that you intercept the error, plus any other code you write in the ordinary event routines will have to consider this "path lost" scenario.

Depending on the design of your DB, the difficult part is the "have an error trap ready to catch the error." You would need pervasive error trapping to be set up everywhere in your app. There is no telling which of several possible errors you would get, either. You might need to set up a test scenario in order to determine the error number you get when that happens.

Here is the worst part. If you experience this event, it is already too late to clean up. You can't flush the "pending I/O" buffers because by the time you get the error message, their output target is already gone. This will potentially result in database corruption if you ever lose the BE in the middle of a long-winded update or append query.

 

[tvanstiphout]

If you're using ODBC to connect to your BE, then this applies to you: https://support.microsoft.com/en-us...ess-2019-f52c5317-3494-4105-9c56-5a2abb8e0f87
See the section ODBC connection retry logic.

 

[The_Doc_Man]

Tom, from the description provided in posts #1 and #3, I interpret the situation as using native Access rather than ODBC. Having the ODBC option available would make it at least slightly easier to recover gracefully, though.

 

[Edgar_]

@Edgar_ - your method might prevent opening the 2nd FE when the BE isn't there in the first place, but I took the question as trying to gracefully shut down after the BE vanishes while the 2nd FE is open.

Indeed, me too, that's why I suggested before update or form open. Maybe that little check can prevent a user from seeing that nasty, vague, untrappable error message. If so, that would let you notify the user, close connections, close forms or the app and the user would at least know for certain what happened.

Since the backend freezes when the backend is offline, presumably trying to resolve the path, I guess it's just a timer instance. If we can anticipate that timer instance from initiating, maybe we can trap that error. But that's all it is, a guess.

 

[The_Doc_Man]

Edgar_, I suspect that your suspected timer instance is actually a device-driver time-out. The problem is multi-fold.

First, only the process code that triggered an I/O request (to a driver) has enough data to be able to cancel it, so that time-out countdown is GOING to happen - because we can't get to the specific I/O request to kill it.

Second, you might get multiple time-out countdowns because what is PROBABLY happening (and it is a guess on my part) is that in the File >> Options >> Current Database dialogs, you can set the automatic requery. There is probably a requery pending on every linked table to try to update the FE's data on each table. The pending I/O events cannot interrupt each other (because one I/O failure event will be at the same priority as another I/O failure event) so you would be waiting for a cascade of crud to hit the fan all at once when those timers hit 0.

Third, that doesn't even count any recordsets related to open forms, open reports, or active queries, all of which are doing the same requery actions.

Fourth, I/O failures are relatively high priority events if I recall correctly, so trying to get some sort of constructive trap catcher inserted in that sequence will be tricky at best. Your user-level process code has to be high enough priority to actually run, and in that context you are highly disadvantaged. If you can't catch it right away, (like, before it happens) you aren't going to catch it later.

 

[Babycat]

Dear all.
My Access app has many form/report, so we never know which form/report user is opening. Therefore, handling disconnection event in every single form/report is nightmare.
I dont have good IT background, I just tried to google about VPS. So my intension is:

1. Buy a VPS from service provider
2. Set up BE location on that VPS, example: C:\AccessDB
3. From user1, 2...n of my company PC, using Window Remote Desktop Connection connects to that VPS
4. Map C:\AccessDB (on VPS) to be Network Drive
5. Local FE connects to this mapped Drive which contains BE

Is that possible and what is the pros and cons?

 

[Edgar_]

I think @Pat Hartman is the one who knows a lot about remote desktops. Another user also commented at some point that you can use WinHost for this.

I have multiple VPS servers from different providers around the web, I can tell you this much:
- It will receive automated attacks in a matter of minutes (this is a given, you can't stop it, only attenuate it)
- Based on previous note, you need Firewall, intrution prevention like Fail2Ban, no root user and an SSH key pair protection
- Do not place any database in that VPS without at least the previous measures

How complex is your database? maybe you'll be better off using a REST API version of it.

 

[The_Doc_Man]

If you were planning to buy a VPS server to act as a file system host, but you were still going to be on a LAN, this MIGHT be overkill. Any ordinary PC, whether we are talking mini-tower or full-sized tower, would do - as long as you have a good anti-viral package or internet security package installed. I would hesitate to use a laptop as a file server host only because some laptops have heat dissipation issues that might make them less stable than something that has a decent internal cooling system, or at least a good set of fans.

Using RDP does not solve the problem you described. It is a diversion from the real problem. Adding a dedicated but relatively inexpensive stand-alone PC that can be allowed to run untended would be a more direct solution.

 

[Pat Hartman]

I agree with Doc. You don't need an actual server to run Access. Any solid desktop will do. It needs to be out of the way and it needs to be on 24/7. Someone will need to be responsible for rebooting it at least once per week to make sure updates get applied and memory gets cleared. Hide the keyboard to keep people from using it as a workstation. If you add the host PC, you don't need RD. The reason for installing RD or Citrix is because you want to enable people to work remotely. RD and Citrix probably do need an actual server to work effectively. I use them, I don't install them, although back in my mainframe days I once had to install CICS and the OS for a client because his lead operator had just finished training on IBM hardware/software. He had been a Burroughs guy. So, I followed the directions and got two basic systems set up. You are out of your depth if you try to set up a remote server. You can do it as I did by following the directions but this is not a task for an amateur as @Edgar_ pointed out. There's lots of bad guys out there and they're always looking for a weakness.

If you decide you need to set up a server that will allow your people to remote in, hire a professional.

 

[Babycat]

Dear all
Currently, user1's local PC is still playing well "server computer" role. User2 FE hang up issue is not often and not critical.
I think about VPS because of future system scaling, one day my company might expands 2nd 3rd offices in different locations... These days, I really can not use LAN BE sharing anymore as i am doing now.
I recall my ex-company uses Citrix to access a "virtual PC" which contains confidential documents. Since I am not familiar with Citrix and VPS definition, my understanding is that:
1. VPS to setup a cloud BE location.
2. Citrix or RD to create vitual machine for each user. These "virtual machines" locate on VPS, thus all user can work remotely while use same BE on VPS.

If true, then I have to buy both VPS and Citrix?
I have heard about TS-Plus which mentioned by someone in our forumm not sure if it is what am looking for...

 

[Babycat]

How complex is your database? maybe you'll be better off using a REST API version of it.

It has about 30 tables and 40 forms.
Unfortunately, I am not familiar with REST API, seem it is long way to go for me.

 

[Pat Hartman]

What you DON'T want is a "cloud" drive. Those will NOT work for Access. Doc has written extensively.

Citrix and RD create "desktops" that are hosted on the server. From the perspective of the user, it is just like sitting at his computer as it is in the office. He "sees" every drive he would see from there and everything works as it does from a local PC connected to a LAN.

Behinds the scenes, both the FE and the BE are on the same server. Each user still has his own personal copy of the PC so the FE is not shared. But because the two parts are on the same drive, there is NO network latency so Citrix and RD are always the fastest way to use an Access application. My London and Paris users had performance that on some days was better than what my local users in Farmington, CT got. Everything runs on the server. NO data is ever transmitted across the LAN or across the WAN. Keystrokes are sent from the logged in PC to Citrix/RD and Citrix/RD sends back images of what would be displayed on your local monitor.

 

[Babycat]

Dear Pat

Thank for your detail, I understand with Citrix and RD we are not sending any Access data but sending key trokes and screen.

Citrix and RD create "desktops" that are hosted on the server.

Where does this server locate? Will I need a VPS or I can use my local PC with static IP?
Sorry for dumb question, but as mentioned before, I dont have IT background...

 

[Pat Hartman]

I don't know what a VPS is.
I don't believe you can host Citrix on a regular PC. You will need a server. For RD, you have two options. A server that will run all the desktops. OR, if your office has desktops that stay on-site and on, each user could connect to his personal office PC using RD and he would see his desktop as if he were at the keyboard in the office

For the second option of RD, I think the office PC needs to have either a Windows pro version or you have to buy a separate RD license for ~ $100 each. I've only used the second option from the perspective of a user. Some of my clients have assigned a PC to me in their office even though I might be thousands of miles away and I remote into that PC using RD.

You seem to have switched your focus to remote access. That isn't going to solve any problem if the database is hosted on a PC that someone might turn off.