Future of Access (3 Viewers)

[RonPaii]

No, but unless there actually is an outage, the drop is temporary. Doc, you're making this harder than it needs to be. The system times out. The app doesn't discover the problem until the user tries to do something else. Then even finding out that there was a blip takes minutes. THAT is the problem we are trying to solve. Assuming the OP uses a timer to find out that the network has dropped and closes the open form, will there be a long time required when the user comes back to do something and opens the form again. I'm trying to figure out how to shorten the user's wait time and I don't have a network to do it on so I can't try to create a test.

You can quickly detect the drop by using WMI to setup a network monitor then close all forms or Access when detected.

 

[BlueSpruce]

I guess you didn't understand my question/explanation. Think of what I described as parallel processing. Form is open doing nothing, user is having lunch. Time is passing for him but he isn't waiting for the computer to do something. Meanwhile back at the ranch, the timer is running, tick, tick, tick and it discovers there has been a timeout and so the code closes the form to clear it out. Did the user know this was happening? No, he was happy doing his nails. Now, he comes back to his desk and sees his form was closed so he opens it. --- DOES ACCESS STILL NEED TO WAIT before opening the form or is quick like the initial open usually is? Who knew this question would be so difficult to understand. Did using the timer to identify a time out while the form was idle save the USER time? Of course time was spent to identify the dropped connection and close the form but if the user was doing something else - HE DOESN'T CARE. What he cares about is trying to use the form after leaving it idle for an hour and having to wait for three minutes before he gets an error message. That wait happens on his conscious time line and since waiting for computers to do stuff is annoying, no matter how fast the response is, HE CARES.

I get it, but based on what you and @The_Doc_Man have explained, plus my comments about the FE and/or BE possibly becoming corrupted, perhaps it's better to close and reopen the FE. What you propose is to use the On Timer event, the same way we implement auto exiting the form, or the app, after n ticks of inactivity?

 

[The_Doc_Man]

I stated earlier that you could close the form and re-open - IF the connection has by that time re-established itself. Which is a possibility. The related question "DOES ACCESS STILL NEED TO WAIT" can only be answered by testing the connection to see if you have one. (See @RonPaii remark regarding WMI). If you have a connection but the previously open form has now gone bonkers, try to close it and find out. That comes back to whether the form is still around or whether it will respond to a DoCmd.Close of the form, or whatever other method you try.

The real headache in this question is whether Access itself has actually EXITED because of the lost connection between the FE and the linked tables in the BE. That is going to depend on factors such as the way the network was set up and whether the App's error handling (provided by the app's author) intercepted the error that would possibly be fatal to Access itself. I have seen cases where Access died because it lost contact with a linked BE table. Therefore, in answer to the "NEED TO WAIT" question, I have to reply, "Damned if I know. It depends on too many factors."

You can quickly detect the drop by using WMI to setup a network monitor then close all forms or Access when detected.

This presumes that Access is able to deal with that. Remember that Access is (a) single-threaded for code execution and (b) your code is not always in control if no relevant event has been recently triggered. This implies timer operations, and the problem there is that a complex timer routine is going to slow you down a lot. A MAIN program clearly could set up event interceptors to tell you what is going on, but you don't control all of the event vectors for MSACCESS.EXE, the MAIN program. That kind of hampers the detection process.

 

[BlueSpruce]

I stated earlier that you could close the form and re-open - IF the connection has by that time re-established itself. Which is a possibility. The related question "DOES ACCESS STILL NEED TO WAIT" can only be answered by testing the connection to see if you have one. (See @RonPaii remark regarding WMI). If you have a connection but the previously open form has now gone bonkers, try to close it and find out. That comes back to whether the form is still around or whether it will respond to a DoCmd.Close of the form, or whatever other method you try.

The real headache in this question is whether Access itself has actually EXITED because of the lost connection between the FE and the linked tables in the BE. That is going to depend on factors such as the way the network was set up and whether the App's error handling (provided by the app's author) intercepted the error that would possibly be fatal to Access itself. I have seen cases where Access died because it lost contact with a linked BE table. Therefore, in answer to the "NEED TO WAIT" question, I have to reply, "Damned if I know. It depends on too many factors."



This presumes that Access is able to deal with that. Remember that Access is (a) single-threaded for code execution and (b) your code is not always in control if no relevant event has been recently triggered. This implies timer operations, and the problem there is that a complex timer routine is going to slow you down a lot. A MAIN program clearly could set up event interceptors to tell you what is going on, but you don't control all of the event vectors for MSACCESS.EXE, the MAIN program. That kind of hampers the detection process.

What if an additional accdb could be setup to act as a network monitor that periodically checks the status of the connection and the status of Access app? It could force via automation the close and reopen of app if close/reopen of the form fails, or the instance simply doesn't respond? The additional accdb could launch the app, monitor it, and would be totally unaffected if the main app looses the connection, or even crashes.

 

[RonPaii]

I stated earlier that you could close the form and re-open - IF the connection has by that time re-established itself. Which is a possibility. The related question "DOES ACCESS STILL NEED TO WAIT" can only be answered by testing the connection to see if you have one. (See @RonPaii remark regarding WMI). If you have a connection but the previously open form has now gone bonkers, try to close it and find out. That comes back to whether the form is still around or whether it will respond to a DoCmd.Close of the form, or whatever other method you try.

The real headache in this question is whether Access itself has actually EXITED because of the lost connection between the FE and the linked tables in the BE. That is going to depend on factors such as the way the network was set up and whether the App's error handling (provided by the app's author) intercepted the error that would possibly be fatal to Access itself. I have seen cases where Access died because it lost contact with a linked BE table. Therefore, in answer to the "NEED TO WAIT" question, I have to reply, "Damned if I know. It depends on too many factors."



This presumes that Access is able to deal with that. Remember that Access is (a) single-threaded for code execution and (b) your code is not always in control if no relevant event has been recently triggered. This implies timer operations, and the problem there is that a complex timer routine is going to slow you down a lot. A MAIN program clearly could set up event interceptors to tell you what is going on, but you don't control all of the event vectors for MSACCESS.EXE, the MAIN program. That kind of hampers the detection process.

It's setup as a callback which raises an evert on my main form of the change. You don't need any timer and the main form would know how to shut down the other forms and/or the FE. BTW if you are seeing drops on a wired network, there is a hardware problem that needs to be fixed. I don't allow connections from WIFI or WAN.

'Class ClassWMI header'
Private oWMI_root_WMI  As SWbemServicesEx           ' Service Object "\root\WMI"
Private WithEvents oSinkDisconnect As SWbemSink     ' Disconnect Event object
Private WithEvents oSinkConnect As SWbemSink        ' Connect Event object
Public Event Changed(strChanged As String)          ' Raised when NIC changes
Private m_bMonitor As Boolean                       ' True if network monitor is active

'Setup callback called by class init'
Private Sub WMI_MontiorNetwork()
    On Error GoTo errWMI_MontiorNetwork
    Set oWMI_root_WMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\WMI")
    Set oSinkDisconnect = New SWbemSink
    Set oSinkConnect = New SWbemSink
    oWMI_root_WMI.ExecNotificationQueryAsync oSinkDisconnect, "SELECT * FROM MSNdis_StatusMediaDisconnect"
    oWMI_root_WMI.ExecNotificationQueryAsync oSinkConnect, "SELECT * FROM MSNdis_StatusMediaConnect"
    m_bMonitor = True
doneWMI_MontiorNetwork:
    Exit Sub
errWMI_MontiorNetwork:
    m_bMonitor = False
    'Me.WriteLog MODULENAME & ".WMI_MonitorNetwork", "Could not setup monitor. Error:( " & Err.Number & " ) " & Err.Description
    Resume doneWMI_MontiorNetwork
End Sub

'--------------------
' Shut-down network monitor on program close
Private Sub Class_Terminate()
    On Error Resume Next
    If Not oSinkDisconnect Is Nothing Then
        oSinkDisconnect.Cancel
        Set oSinkDisconnect = Nothing
    End If
    If Not oSinkConnect Is Nothing Then
        oSinkConnect.Cancel
        Set oSinkConnect = Nothing
    End If
    m_bMonitor = False
    If Not oWMI_root_WMI Is Nothing Then Set oWMI_root_WMI = Nothing
End Sub

'-----------------
' SWbemSink Event on disconnect of a NIC
Private Sub oSinkDisconnect_OnObjectReady(ByVal objWbemObject As WbemScripting.ISWbemObject, _
                                          ByVal objWbemAsyncContext As WbemScripting.ISWbemNamedValueSet)
    On Error Resume Next
    Dim strNic As String
    Dim iNIC As Integer
    strNic = objWbemObject.Path_.RelPath
    ' Refill the NIC arrays
    'WMI_Fill_Nic
    ' Strip down to only name of NIC
    ' ex. MSNdis_StatusMediaDisconnect.InstanceName="Realtek PCIe GBE Family Controller"
    iNIC = InStr(1, strNic, "=")
    If iNIC > 1 Then
        strNic = Mid(strNic, iNIC + 2)
        strNic = left(strNic, Len(strNic) - 1)
    End If
    ' Let form know something changed
    RaiseEvent Changed(strNic & ", has Disconnected")
End Sub

'-----------------
' SWbemSink Event on Connection of a NIC
'
Private Sub oSinkConnect_OnObjectReady(ByVal objWbemObject As WbemScripting.ISWbemObject, _
                                       ByVal objWbemAsyncContext As WbemScripting.ISWbemNamedValueSet)
    On Error Resume Next
    Dim strNic As String
    Dim iNIC As Integer
    strNic = objWbemObject.Path_.RelPath
    ' Refill the NIC arrays
    'WMI_Fill_Nic
    ' Strip down to only name of NIC
    ' ex. MSNdis_StatusMediaDisconnect.InstanceName="Realtek PCIe GBE Family Controller"
    iNIC = InStr(1, strNic, "=")
    If iNIC > 1 Then
        strNic = Mid(strNic, iNIC + 2)
        strNic = left(strNic, Len(strNic) - 1)
    End If
    ' Let form know something changed
    RaiseEvent Changed(strNic & ", has connected")
End Sub

'Main Form ---- Always open'
Private WithEvents oWMI As ClassWMI  ' Check Network status Handles ClassWMI events

'-------------------------------
'   Handle Changed event when WMI see a network change
'   Check status of network change on ClassWMI Changed event
'   Shut down application on WIFI, VPN or no Network
Private Sub oWMI_Changed(whatChanged As String)
         MsgBox whatChanged & vbCrLf & _
                "No network connections, found." & vbCrLf & vbCrLf & _
                "Closing the application.", _
                vbCritical, "No Active Connections"
End Sub

 

[The_Doc_Man]

What if an additional accdb could be setup to act as a network monitor that periodically checks the status of the connection and the status of Access app?

Windows security rules say that unless a formal mechanism is set up to allow task A to influence task B, that they will otherwise have nothing to do with each other. They will share no memory areas (like a common buffer area). They will not be able to reach into each other's memory even if they are both being run under the same username. They cannot take an action that would alter the other process's state (Run, Wait for I/O, Wait for memory, Wait for CPU availability, etc.). They will not be able to definitively know that the other process even exists at a given moment unless you start using all sorts of API tricks. Since this is potentially the same user, API tricks MIGHT work because of user permissions matching up.

That inter-task isolation has been the case ever since WinNT came out, and that isolation was built into WinNT because that feature was the only way that the U.S. Dept. of Defense would allow Microsoft to propose ANY technology based on Windows. All predecessors to WinNT would have been disqualified on the "task isolation" and "object re-use" rules. MS would have been disallowed to respond to the request for a proposal using Windows. Think about the economics of THAT little situation. The other two big security rules were in the "maybe" category but those two task rules were essentially a hard "go/no go" factor. You KNOW that MS fixed that ASAP.

I'm not saying you can't do anything about this problem, but you are now reaching the point where you might do better to just publish instructions for your users to manually close and restart Access when certain "network connection lost" messages crop up. We are approaching "severely convoluted programming" issues for a relatively simple problem to fix with a short manual procedure.

See also Pat Hartman's response, #180 of this thread.

 

[BlueSpruce]

Windows security rules say that unless a formal mechanism is set up to allow task A to influence task B, that they will otherwise have nothing to do with each other. They will share no memory areas (like a common buffer area). They will not be able to reach into each other's memory even if they are both being run under the same username. They cannot take an action that would alter the other process's state (Run, Wait for I/O, Wait for memory, Wait for CPU availability, etc.). They will not be able to definitively know that the other process even exists at a given moment unless you start using all sorts of API tricks. Since this is potentially the same user, API tricks MIGHT work because of user permissions matching up.

That inter-task isolation has been the case ever since WinNT came out, and that isolation was built into WinNT because that feature was the only way that the U.S. Dept. of Defense would allow Microsoft to propose ANY technology based on Windows. All predecessors to WinNT would have been disqualified on the "task isolation" and "object re-use" rules. MS would have been disallowed to respond to the request for a proposal using Windows. Think about the economics of THAT little situation. The other two big security rules were in the "maybe" category but those two task rules were essentially a hard "go/no go" factor. You KNOW that MS fixed that ASAP.

I'm not saying you can't do anything about this problem, but you are now reaching the point where you might do better to just publish instructions for your users to manually close and restart Access when certain "network connection lost" messages crop up. We are approaching "severely convoluted programming" issues for a relatively simple problem to fix with a short manual procedure.

See also Pat Hartman's response, #180 of this thread.

What does that have to do with one accdb opening up another accdb application and automating it?

You're telling me the accdb that opened the other accdb cannot find out if a form IsOpen, close it, or close the app, even though it created that instance? This has been done before. The accdb monitor can also interact with the windows filesystem, run shell commands, wmi api's, powerscripts, etc. to monitor the connections and the main app. Your telling me windows has no inter_process communications, shared memory, semaphores, like unix has?

 

[NauticalGent]

It's setup as a callback which raises an evert on my main form of the change. You don't need any timer and the main form would know how to shut down the other forms and/or the FE. BTW if you are seeing drops on a wired network, there is a hardware problem that needs to be fixed. I don't allow connections from WIFI or WAN.

'Class ClassWMI header'
Private oWMI_root_WMI  As SWbemServicesEx           ' Service Object "\root\WMI"
Private WithEvents oSinkDisconnect As SWbemSink     ' Disconnect Event object
Private WithEvents oSinkConnect As SWbemSink        ' Connect Event object
Public Event Changed(strChanged As String)          ' Raised when NIC changes
Private m_bMonitor As Boolean                       ' True if network monitor is active

'Setup callback called by class init'
Private Sub WMI_MontiorNetwork()
    On Error GoTo errWMI_MontiorNetwork
    Set oWMI_root_WMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\WMI")
    Set oSinkDisconnect = New SWbemSink
    Set oSinkConnect = New SWbemSink
    oWMI_root_WMI.ExecNotificationQueryAsync oSinkDisconnect, "SELECT * FROM MSNdis_StatusMediaDisconnect"
    oWMI_root_WMI.ExecNotificationQueryAsync oSinkConnect, "SELECT * FROM MSNdis_StatusMediaConnect"
    m_bMonitor = True
doneWMI_MontiorNetwork:
    Exit Sub
errWMI_MontiorNetwork:
    m_bMonitor = False
    'Me.WriteLog MODULENAME & ".WMI_MonitorNetwork", "Could not setup monitor. Error:( " & Err.Number & " ) " & Err.Description
    Resume doneWMI_MontiorNetwork
End Sub

'--------------------
' Shut-down network monitor on program close
Private Sub Class_Terminate()
    On Error Resume Next
    If Not oSinkDisconnect Is Nothing Then
        oSinkDisconnect.Cancel
        Set oSinkDisconnect = Nothing
    End If
    If Not oSinkConnect Is Nothing Then
        oSinkConnect.Cancel
        Set oSinkConnect = Nothing
    End If
    m_bMonitor = False
    If Not oWMI_root_WMI Is Nothing Then Set oWMI_root_WMI = Nothing
End Sub

'-----------------
' SWbemSink Event on disconnect of a NIC
Private Sub oSinkDisconnect_OnObjectReady(ByVal objWbemObject As WbemScripting.ISWbemObject, _
                                          ByVal objWbemAsyncContext As WbemScripting.ISWbemNamedValueSet)
    On Error Resume Next
    Dim strNic As String
    Dim iNIC As Integer
    strNic = objWbemObject.Path_.RelPath
    ' Refill the NIC arrays
    'WMI_Fill_Nic
    ' Strip down to only name of NIC
    ' ex. MSNdis_StatusMediaDisconnect.InstanceName="Realtek PCIe GBE Family Controller"
    iNIC = InStr(1, strNic, "=")
    If iNIC > 1 Then
        strNic = Mid(strNic, iNIC + 2)
        strNic = left(strNic, Len(strNic) - 1)
    End If
    ' Let form know something changed
    RaiseEvent Changed(strNic & ", has Disconnected")
End Sub

'-----------------
' SWbemSink Event on Connection of a NIC
'
Private Sub oSinkConnect_OnObjectReady(ByVal objWbemObject As WbemScripting.ISWbemObject, _
                                       ByVal objWbemAsyncContext As WbemScripting.ISWbemNamedValueSet)
    On Error Resume Next
    Dim strNic As String
    Dim iNIC As Integer
    strNic = objWbemObject.Path_.RelPath
    ' Refill the NIC arrays
    'WMI_Fill_Nic
    ' Strip down to only name of NIC
    ' ex. MSNdis_StatusMediaDisconnect.InstanceName="Realtek PCIe GBE Family Controller"
    iNIC = InStr(1, strNic, "=")
    If iNIC > 1 Then
        strNic = Mid(strNic, iNIC + 2)
        strNic = left(strNic, Len(strNic) - 1)
    End If
    ' Let form know something changed
    RaiseEvent Changed(strNic & ", has connected")
End Sub

'Main Form ---- Always open'
Private WithEvents oWMI As ClassWMI  ' Check Network status Handles ClassWMI events

'-------------------------------
'   Handle Changed event when WMI see a network change
'   Check status of network change on ClassWMI Changed event
'   Shut down application on WIFI, VPN or no Network
Private Sub oWMI_Changed(whatChanged As String)
         MsgBox whatChanged & vbCrLf & _
                "No network connections, found." & vbCrLf & vbCrLf & _
                "Closing the application.", _
                vbCritical, "No Active Connections"
End Sub

Impressive!