Future of Access (1 Viewer)

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

 

[BlueSpruce]

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.

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.

 

[BlueSpruce]

I realize that but what we're talking about here is a form left in an idle state not pending completion of an update. Nothing can ever be done if you loose the connection mid update unless the update is actually inside a transaction.

Can the OP solve that specific problem by starting a new connection? I think if the users can be trained to always close the open form and go back to the menu, the reconnection can be handled more gracefully.

Most db servers support atomic transactions, but Access ACE engine doesn't. It also doesn't have transaction logging with record rollback, or make use of a before image, or checkpoints.Even if you were to link an Access FE with a db server backend, the FE is still going suffer damage with a dropped packet.

 

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

 

[BlueSpruce]

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

 

[BlueSpruce]

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?

I don't think you can rely on the increase or decrease of AWF members, posts, to determine if more or less people are using Access. I know several Access developers and users who have never joined AWF or any other forums. Then there's millions of Access apps out there, including many mdb's that have been running unchanged for decades, and will continue running like that. I don't know how well MS is tracking actual Access usage. Many consumers and corporations have Access installed, but never use it. Others run it on legacy Windows versions and hardware that's never connected to the internet.

 

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

 

[BlueSpruce]

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

And that's the reality of it. Here we are trying to extend an X-Base tool to do things that commercial grade db servers with transaction monitors are capable of doing.

 

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

 

[BlueSpruce]

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?

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?

 

[BlueSpruce]

Thank you for repeating my question after changing it because you don't want the easy answer of closing the form. Apparently I was writing in gibberish.

Even if all I metioned in my last post were possible, the FE and BE might already be in a corrupt inconsistent state and a CR would have to be done. Each of our workstations have a desktop shortcut that copies the master FE and launches the clean FE copy. ReLaunching the FE copy is actually better than trying to repair an FE that lost connection to the BE. Our Master FE has clean temp tables. We CR our Master FE anytime we make development changes to it. If the open form weren't encapsulated within the entire accdb FE, we wouldn't have corrupt accdb's.

 

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

 

[BlueSpruce]

Before the user comes back and tries to do something. Remember this is a timeout situation. The user is not actively using a form. It is simply lying open. The OP is looking for a way to minimize the delay which happens when the timeout isn't "discovered" until the user tries to do something else. I'll try the question one more time. If the the timer "discovers" the disconnect and simply closes the form will this end up being faster than what is happening currently? If the timer closes the open form, does that speed up the reconnect because Access "knows" it is a fresh connect and so doesn't have to wait to find out for itself that there was a timeout? This essentially transfers the wait time to discover the time out to "idle time" when the user is not actively trying to do anything.

So there's no way for speeding up recovery unless the device driver or the OS layer is modified, and that wouldn't happen anyway because it would deviate from standards?

 

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