Future of Access (7 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!

 

[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?

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

It will depend on how you opened the additional DB.

IF the DB is opened as an application object, then it is in a separate task and the communication between the two DBs is limited. This app object DB has a life of its own in the sense that if your opening DB exits without commanding the app object to also exit, you have a dangling task. The app object database is a "slave" to the opening DB in a way analogous but not identical to RDP. To my understanding of task structure, that app object's control channels (Keyboard, Video, Mouse or KVM) are a one-way street, master to slave only, because the separate tasks are subject to the "task isolation" rule. The slave cannot reach into the master because they are in separate chunks of physical memory. The slave cannot access the master's KVM channels because those are already open non-shared and not subject to external interference. The master CAN reach into the slave to trigger actions and change things, but not vice-versa. If you flip the script and have the monitoring task open the desired task, then the KVM of the desired task has the same limitations.

If the DB is opened as a DAO Database object, it is part of the same task as its opener, which means anything you did with it is single-threaded and I'm not sure but I don't think that such a DB opened in that particular way has active events. It doesn't have its own workspace and doesn't "know" that it is open. It doesn't have a separate KVM path... or in fact, ANY KVM path.

If the DB is opened by launching a hyper-link, it is very much like an app object as described above except the control path is different. In that case it would have the default KVM for any launched task that has a window. But it still counts as a separate task for isolation purposes. If you launched the DB from Windows explorer, it is the same as launching a hyperlink. (OR launching the hyperlink is the same as launching from an icon. Potato, patahtoe.)

Did I leave out a method of opening a DB? Offhand, I don't know of another way to open a DB.

 

[BlueSpruce]

It will depend on how you opened the additional DB.

IF the DB is opened as an application object, then it is in a separate task and the communication between the two DBs is limited. This app object DB has a life of its own in the sense that if your opening DB exits without commanding the app object to also exit, you have a dangling task. The app object database is a "slave" to the opening DB in a way analogous but not identical to RDP. To my understanding of task structure, that app object's control channels (Keyboard, Video, Mouse or KVM) are a one-way street, master to slave only, because the separate tasks are subject to the "task isolation" rule. The slave cannot reach into the master because they are in separate chunks of physical memory. The slave cannot access the master's KVM channels because those are already open non-shared and not subject to external interference. The master CAN reach into the slave to trigger actions and change things, but not vice-versa. If you flip the script and have the monitoring task open the desired task, then the KVM of the desired task has the same limitations.

If the DB is opened as a DAO Database object, it is part of the same task as its opener, which means anything you did with it is single-threaded and I'm not sure but I don't think that such a DB opened in that particular way has active events. It doesn't have its own workspace and doesn't "know" that it is open. It doesn't have a separate KVM path... or in fact, ANY KVM path.

If the DB is opened by launching a hyper-link, it is very much like an app object as described above except the control path is different. In that case it would have the default KVM for any launched task that has a window. But it still counts as a separate task for isolation purposes. If you launched the DB from Windows explorer, it is the same as launching a hyperlink. (OR launching the hyperlink is the same as launching from an icon. Potato, patahtoe.)

Did I leave out a method of opening a DB? Offhand, I don't know of another way to open a DB.

Sub SendStatusFromSlaveToMaster()
   Dim Connect    As Object
   Dim sDatabase1 As String
   Dim sDatabase2 As String

 
   sDatabase1 = "C:\Master.accdb"
   sDatabase2 = "C:\Slave.accdb"

   Set Connect = CreateObject("ADODB.Connection")
   With cn Connect
      .Provider = "Microsoft.ACE.OLEDB.14.0"
      .ConnectionString = "Data Source='" & sDatabase2 & "'"
      .Open
      .FuncSendStatus
      .Close
   End With

End Sub

 

[The_Doc_Man]

Since I don't use ADO, I'm not sure - but if it is at all like DAO, it will have the same issues as opening with DAO. You send the status as a file operation, not a DB operation. Where is .FuncSendStatus defined? This looks like air code since your WITH variable, cn, isn't defined.

I remain skeptical. I return to Pat's and my suggestion that at some point it is less convoluted to just instruct folks how to shut it down and start with a freshly loaded copy. And if that fails during its restart, the network wasn't ready yet.

 

[BlueSpruce]

Since I don't use ADO, I'm not sure - but if it is at all like DAO, it will have the same issues as opening with DAO. You send the status as a file operation, not a DB operation. Where is .FuncSendStatus defined? This looks like air code since your WITH variable, cn, isn't defined.

I remain skeptical. I return to Pat's and my suggestion that at some point it is less convoluted to just instruct folks how to shut it down and start with a freshly loaded copy. And if that fails during its restart, the network wasn't ready yet.

No pain, no gain. If we don't experiment, we don't progress! Linux supports IPC (Inter Process Communication), Shared Memory, and all governments use it, so why wouldn't they use Windows if it had those capabilities?

Sub SendStatusFromSlaveToMaster()
   Dim Connect    As Object
   Dim sDatabase1 As String
   Dim sDatabase2 As String

   sDatabase1 = "C:\Master.accdb"
   sDatabase2 = "C:\Slave.accdb"

   Set Connect = CreateObject("ADODB.Connection")
   With Connect
      .Provider = "Microsoft.ACE.OLEDB.14.0"
      .ConnectionString = "Data Source='" & sDatabase2 & "'"
      .Open
      .FuncSendStatus
      .Close
   End With

End Sub

 

[The_Doc_Man]

In the final analysis, all I can suggest is that if you want to try this, go ahead. The best that can happen is that you have found your way through the maze and win the prize. The worst that can happen is that it doesn't work. I've given you the background to understand the most probable network behavior of this process. I think you are striving for a really complex solution when a simpler solution is available. I remain skeptical but I can (and DO) wish you good luck in the attempt, if you choose to make it.