Future of Access (3 Viewers)

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

 

[Pat Hartman]

One method would be to just close the form in order to close the connection.

I like that one. It should be easy enough to implement.

In the timer event, when your code discovers that the connection has dropped, it simply closes the form. Then the user must open the form to use it again.

 

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