Future of Access (1 Viewer)

[The_Doc_Man]

And then there's this:
View attachment 121153

Regarding this post:

I was catching up on this thread and ran across this post. Specifically to the comment in the post regarding a lost ODBC connection, there is a reason for that problem. We start with the fact that network traffic generally breaks down into UDP and TCP families. ODBC is a member of the TCP family of protocols - one of many. UDP is generally used for "connectionless" protocols most commonly used with web sites. TCP usually involves "sessions" or persistent logical connections between the participants, often called Sockets.

At least 20 years ago there was a security directive regarding network options configuration. Short answer: The ability to RECONNECT to a TCP-family session was determined by the U.S. Dept. of Defense to be too big of a security risk.

The reason is because if someone was monitoring a particular military network and was using a Sniffer or other network real-time analyzer tool, and if that network died for a moment, the sniffer had enough information to take over the socket while the network was in flux. Basically, all you need is found in the packet header, including the socket ID, session ID, and connection sequence number provided by the prior network partner. That is, you don't need login-level credentials to do a RECONNECT because that login occurred previously and established the session-layer structure needed for the transactions.

The "very strong recommendation" was therefore disseminated that we should edit all of our network config files to ALWAYS contain "RECONNECT=NO" from that time on. For what it is worth, the SMB protocol used by Access is another member of the TCP family and is another reason why Access is so sensitive regarding network drops.

 

[RonPaii]

I kno that. I was referring to the C# you mentioned. It's a lot easier for a team to work with external code modules versus code encapsulated in accdb containers.

Each team member work on their own copy of the FE and pushing out changes just like an IDE like .NET. Each export only exports the changed objects, not the entire FE. Commits are small and often.

One problem with Access is it's tendency to update all named objects with the same name if you change the case of any 1. This causes more objects to be exported with only case changes which can confuse a merge. To combat this I changed my naming convention for ALL named objects to Pascal Case SomeVariable, were before functions and properties were Pascal Case and variables and parameters were Camel Case someVariable.

For Example if add a function with a local variable userInput then export, only that module is exported. Then in another module I add another function with a local variable UserInput, the first variable is changed. On the next export both modules will be exported even though logically the 1st module has not changed.

 

[AHeyne]

Since you are talking about the desire for a more stable ODBC connection or improved behavior of Microsoft Access in the event of a connection loss,
hasn't this already been improved since Access 2019?

What's new in Access 2019 - Microsoft Support

support.microsoft.com

ODBC connection retry logic​

When Access loses the ODBC connection to an external data source, users have to close and re-open their Access databases in order to continue working with objects that use data from those external data sources. Access doesn't attempt to re-establish the dropped connection even if the connection is valid again and there is nothing that users can do to make Access try and reconnect to the external data source.


We've improved this experience so that now if Access loses the ODBC connection to an external data source, Access tries to reconnect to the data source automatically. In this scenario, you'll now see the following experience:

• If Access loses the ODBC connection to an external data source you're trying to use, you'll see an error message describing the failure.
• Access now attempts to reconnect to the data source automatically when performing operations that require a connection to an external data source. If the retry is successful, you can continue working with the data and database objects without having to close and re-open Access.
• If the retry fails, you won't be able to work with objects that use the dropped external data sources but you can continue working with other areas of your database that aren't dependent upon the unreachable data sources.
• If you attempt to use data or other objects again that rely on the external data connections, Access tries again to reconnect to the external data source.

 

[CJ_London]

Seems to me you are arguing about the difference between a bus and a train. Both transport people and each have their own advantages and disadvantages. Which one you use depends on what matters to you. And sometimes you don’t have a choice.

If you don’t want to use the train because it doesn’t go where you want to go and if Access is the bus and you don’t like the uncomfortable seats, use a coach or a car

 

[The_Doc_Man]

In a previous post I mentioned that Access' file server architecture and SMB are part of the reason why Access is intolerant to network noise and latency, but I was told SMB was irrelevant.

You may have gotten that answer but you didn't get it from me (unless I was trippin' at the time... but I don't do that.)

Yes, because SMB (any version) is a TCP-based protocol as opposed to UDP based, Access IS sensitive to network drops. Switching to SMB 2 and later to SMB 3 simply means the session can more easily buffer packets and optimize its use of allocated memory resources. A feature of most protocols is the ability for the original sender to retry after a time-out. There can be a retry limit. In the final analysis, either the session layer keeps on going or the entire virtual connection collapses.

And for the above reason is why ODBC always requires login when re-connecting to a db server?

While the exact reason depends on whatever the network gurus said it was and I didn't see that particular dispatch, I would say that it is a VERY HIGHLY LIKELY reason for the "login when reconnecting" requirement. The details of any protocol would be "ordained" by the IETF (Internet Engineering Task Force) and/or IEEE (Institute of Electrical and Electronics Engineers). If you looked up IETF + ODBC, you might get a reference to RFC 2371, the current standard for database transactions under the title "Internet Transaction Protocol". However, as it turns out, ODBC is actually just an API that embodies an implementation of ITP. There is no RFC document for ODBC.

 

[Steve R.]

I am a team member of four Access developers and we are concerned about the stability and future of Access.

Getting back to the original (future) concern. Obviously, MS Access primarily survives due to the grace of Microsoft. But going to the next level (in terms of the future of MS Access); is MS Access acquiring users or losing users? This website, to a degree, can indirectly answer that question. Should more people be implementing MS Access solutions, it can be expected that they will find this site. As such, is the number of users of this website (or others) growing, staying stagnant, or declining?

 

[The_Doc_Man]

Is the ODBC login requirement really because Access uses SMB?

Pat and I sometimes disagree on technical matters due to our different backgrounds. I'm a hardware, device driver, and networks person. Pat came up through more of an applications oriented starting point. We sometimes see things through different shades of rose-colored glasses. I offered my opinion based on my background, she offered hers.

The truth is that when it was originally designed (back in the 1990s?), whatever Access used had to have a connection consistent with TCP standards because database work in a local-area network environment was (and still is) primarily session-oriented. That requirement is because TCP/IP has a fixed upper limit on data packet size and database transactions rarely fit completely in a single network packet. Therefore, you have to establish a framework to maintain continuity of context (to detect that a packet was lost.) And it is the "sessions" requirement that involves TCP-family protocols into the picture. Because SMB is a TCP-family member, it has the ability in its handshake to say, "I got packet #12454 with a transmission error... please retransmit #12454." OR the listener side could say "Acknowledge proper receipt of packet #12454... send next packet in sequence"). Note that a retransmission request is NOT a network drop, because the handshake is still active.

Because it is a file-sharing environment, you have to use a protocol that allows random access to arbitrary PARTS of a file. (Diverge for explanation: Excel does NOT use such a protocol, which means that any number of folks can simultaneously READ an Excel file but only one user at a time can WRITE to it. But that won't work for a shared multi-user database app.) SMB protocol allows tasks to selectively grab file pieces-parts over a LAN. As long as two "grabs" don't overlap, SMB is perfectly content - and so is Access.

So... does that mean that because Access uses SMB that it has that RECONNECT problem? Is there anything else that Access could have done?

No, nothing else. Access uses SMB because that is the protocol that allows shared writes in a given file in a session-oriented local area network environment. It is also known as the Windows File Sharing and Printer Sharing Protocol. But it is called SMB for short or Server Message Block for long, and proprietary implementations include Samba. Whatever you call it, SMB was there first! Which is the chicken and which is the egg?

The issue actually comes down to this: It is the requirements of the app that make it susceptible to connection loss issues and anything you design that doesn't involve witchcraft will be susceptible to session loss. SMB happens to be the name of the protocol that was used. It is the security requirements of the application that lead you to where we are now.

 

[The_Doc_Man]

I think Doc is saying that the application cannot even request a reconnect. Could it open a new connection?

The other thing you could do is to force the bound form to close if it has been idle for a few minutes.

The problem with "RECONNECT" actually isn't whether you can restart the session. (You can't.) It is that after a drop, you can't finish an incomplete transaction. You can protect against total loss by always using BEGIN/COMMIT transactions, which means that a Compress & Repair could bring the DB back to a consistent state - but the partially complete transaction is always lost.

Remember that TCP/IP has to break up data streams into packets once we start talking networks - LAN or WAN. If you take a network drop after you have received half of the packets needed for a complete transaction AND it was not done with a formal transaction context, what you now have is a half-updated table. IF you could do a reconnect (different from a retransmit during a continuous session), you would have to be able to bring both sockets together and determine the last validly transmitted buffer - and hope the next one was still available. Can the app open a new connection? Absolutely. Would it have the ability to ask for the last part of the previously broken transmission sequence? I don't know of ANY protocol that does that. Doesn't mean it can never happen, but I've never seen that work.

It is the "Half updated table" that leads to the "Inconsistent database" error message that reveals a corruption event. THAT is the main mechanism of database corruption.

 

[The_Doc_Man]

When the form is truly idle and Access thinks (incorrectly) that it is still connected, my only question has to do with dealing with the old connection. In order to start a new connection, Access has to first let go of what it thinks it still has. But when it tries to do something with that connection, it ain't there no more. NOW we are talking mechanical issues inside of Access.

One method would be to just close the form in order to close the connection. IF the "close the channel" handshakes work correctly, then all else is clear sailing. You certainly could then attempt to open the form again. If an error occurs, it would likely be in the Form_Unload step. This method would be visible to the user as the form blinking out and returning and of course would have to be performed from another form - like a dispatcher form or something similar.

There is another method to consider. I believe if you attempted to reload or otherwise manipulate the form's .RecordSource, you would get an automatic disconnect attempt of the prior recordset before it tried to open the updated .RecordSource - whether an actual query or a named query or a named table. From what I remember, the form's CONTENT (in the bound controls) might blink but the form itself would stay open. That disconnect, now behind the scenes, is again the wild card in this mix.

Perhaps doing the .RecordSource manipulation in VBA with an "ON ERROR RESUME NEXT" in force during the disconnect/new connect sequence would be adequate. Then all you would have left to worry about is whether you wanted the re-opened recordset to assume its prior position, which I suppose is possible using a search through the .RecordSetClone to help you establish the correct bookmark.

The issue with formal handshakes is that folks think about establishing a connection but don't always remember that the protocol includes closure standards as well as new-connection standards. My remarks are based on understanding of TCP protocols but since Access is NOT OpenSource, I have no clue about that critical clean-out step of closing a failed connection.

Does that help?

 

[The_Doc_Man]

Okay, but why is the dropped packet not detected by both SMB and the listener (Access), discarded, and retransmitted?.. It's not the case that the whole packet was dropped, rather Access received part of the packet, or the packet contains garbage caused by interference, and now the accdb file is corrupted. Similar damage to when the power plug is pulled from a PC while its running Windows.

Did you forget that in this hypothetical situation, we have dropped the network connection? There IS no retransmission if there is no network. And in this NO RECONNECT situation, there never will be. That data stream is now disrupted and unavailable.

As to network drivers, trust me on this. If your 'puter gets a half of a packet, it will NOT be accepted. At the very least, it would fail the checksum tests because the content checksums are at the tail end of each packet. But regarding the MOST probable detection method, the device driver will probably detect "loss of carrier" (which is a hardware condition) and not worry about anything else like checksums or protocol gaps.

Sorry to disagree with you, but even if a WHOLE PACKET was received, packet lengths are typically about 1500 bytes. Access typically transmits one disk buffer at a pop and those are typically 4 Kbytes - the size of what is also called a disk cluster or a disk allocation unit. It would take two full packets and one full but short packet to send one complete disk buffer of 4K bytes.

If I have a network drop during an update of a single disk block I still have only 1 chance in 3 of having sent a complete buffer even if I really DID send a complete packet.

 

[The_Doc_Man]

The accdb may already be in a corrupt inconsistent state even if you were to close and reopen the form. What about if the problem happens while an update query or internal housekeeping is in progress? Being that Access is closed source, what would you suggest to the MS team for making Access more tolerant to network connection problems?

To the "What if" question... you corrupt the DB.

To the "may already be corrupt" part of the question, I think you would be unable to reopen the form.

To the "What would you suggest" question, you are asking a question that might not be answerable. IF you have a DB app that doesn't protect all updates by using a Transaction BEGIN/COMMIT sequence, your DB might not be recoverable after a net dropout. But this is a case of knowing that there is a risk and then not taking mitigating action. Every small-system DB I have ever seen was vulnerable in this way, and we CANNOT forget that Access was/is a small-system utility. The "big boys" have large memory models to play with that would allow them to automatically treat every network update as a transaction. The problem, of course, is that transaction-based systems take a little longer if you have to wait for the whole transaction to be set up before finally being committed. It's a price to pay for doing business.

Most people play the odds and don't ask Access to do what it wasn't designed to do - i.e. work over a WAN or work in an incompatible cloud.

 

[The_Doc_Man]

You get what you pay for. If you want a "real" database engine (members: NOTE "REAL" IS QUOTED HERE) then you should expect to pay a big-boy price for it. SQL Server, ORACLE, DB2, SYBASE, ... you pays your money and you takes your chances.

In the meantime, Access does what it does for less than $1000 for a perpetual license.

 

[The_Doc_Man]

Doc, you're making this harder than it needs to be.

I'm answering BlueSpruce's questions. He's the one asking why SMB doesn't detect and correct and retry. In fact, the transmission layer DOES all three of those things, but in the end analysis, at some point when all of the countdowns and timers run to zero, the connection has to let go. There, is that short enough for you?

 

[The_Doc_Man]

So can the disconnection be detected sooner, reconnection happen more quickly, and continue where it left off, without having to close/reopen the form, or the FE?

If there is an actual hard disconnect, the carrier signal will be lost and that is detected immediately. In that case, the network driver sets a software countdown timer (standard = 30 seconds) before notification of the disconnect, because network drops are actually not that infrequent. It is not unknown to have a momentary glitch. If the carrier returns within the countdown, everything reverts to normal operation. The device driver is written to automatically resync and resume operations without your intervention. If a partial header came in, the network driver would send a NAK packet with the sequence number of the missed traffic packet. It would tick a counter in the device's error count and go on about its business. You would never see that recovery behind the scenes because it is handled by the ISO Transport (End-to-End) layer transparently to you.

If the disconnect is "softer" due to a Windows issue or a software bug in the network driver or electrical noise on the line (point-to-point layer issue), the driver gets the end of the message but still has the carrier signal. In response to this other error the network driver will trigger a NAK/RESEND sequence. Again, if it all happens within the driver's timeout level, the recovery is handled by the "network stack" software and you never even know it happened unless you ask to see the network driver's statistics, which would list error counts for each type of recognized error.

When you get an actual disconnect message, your connection was down longer than the network device's timeout. IF you see a timeout, there IS no RECONNECT because at that point the end-to-end network context has now failed.

Can you adjust the timeout? Yes, YOU can - but if your network partner isn't set to the same timeout, it doesn't matter. The shortest timeout wins (... well, actually, LOSES).

You are looking to see the issue sooner - but the operating system doesn't work that way. What I am about to describe is true for several operating systems. The I/O operation is a self-contained pseudo-task within the operating system. You give an I/O request packet to the O/S, which gives it to the driver and establishes an O/S level virtual thread on your behalf. At this point, you have NO VISIBILITY to the state of that thread - as a matter of security, because it is running with O/S-level privileges. That thread is now out of your control and the only early thing you can do is, if your I/O was "I/O & release" rather than "I/O & wait" then you COULD do an I/O Cancel. To the best of my knowledge, this is true for Windows, all UNIX flavors, VAX/VMS and Open/VMS, RSX-11M, and TOPS-10. I believe it is true for RSX-11S, DOS-11, and RSX-11D, but won't swear to those.

While that I/O packet is still considered valid and not yet satisfied, you DO NOT have a way to ask "Is my I/O request still active?" unless you are running with elevated O/S privilege AND know where to look to find the I/O request that is pending on the network device. For most users, you can't get there from here. The design of the O/S says that you have to wait for the I/O response and there is no valid way of anticipating. It is done that way to keep your mitts out of the works of the O/S to keep you from breaking something.

 

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

 

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

 

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

 

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