Is access reliable? (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:24
Joined
Feb 28, 2001
Messages
27,001
I noticed the computer that has the most trouble is connected via wifi and regularly drops out. Im starting to think that this is the problem, but I would hope that access would not be so fragile as to lose the database every time a computer crashes or a wifi signal drops.

Here is the problem. The world of TCP/IP has multiple ways to characterize things, but one particular dichotomy stands out for this particular class of problem. There are protocols that operate via a formal connection to a socket and protocols that do not use sockets, they use connectionless protocols supplemented by cookies. Simple examples: FTP and its family are connection-based protocols; HTTP and its family are connectionless protocols.

Various physical connection types will be more or less suitable for one type over the other. As it happens, any wireless connections do better with connectionless & cookie setups, whereas the hardwired physical connections support the protocols that use persistent socket-like connections.

Access normally uses SMB or Server Message Block protocols for file transfers because that is the Microsoft "preferred" protocol for File Sharing. For better or worse, this is a socket-based protocol that is going to be much better when traversing hard-wired physical connections. The protocols establish sequence numbers in the message headers so that they can tell when a message was lost. By contrast, web-based connections have a quick exchange but the contents of the most recent cookie act like a bookmark so that the outbound data call from your system can "tell" the server where it left off in the most recent data exchange.

This distinction is of incredible importance when you have network prone to data loss, and wireless connections are the MOST prone to connection loss. What happens is that you are pumping a lot of data across your link, perhaps several records at a time, or several HUNDRED records at a time if it is a big SQL statement. When you send a very brief transmission, it costs you nothing to pass in a cookie or two as bookmarks.

But when your protocol does not use cookies, then the information on your current progress is in the socket, not in a cookie. If you lost the connection, you lose your place because TCP/IP rarely if ever allows a reconnection. (You can thank your site's security managers for that restriction, even though it is probably a good one in general.) So in essence, you lose your bookmark from the socket's data structure and can never get it back due to the "reconnection" rule.

Now, mechanically, what happens here is that you do a bulk update via SQL and Access has to read in the table, perform the update, and write back the updated records. But if in the middle of this you drop your signal, you now have written back a fraction of the whole table. And as a result, that half-written table has become corrupt.

Bottom line? Your wi-fi connection is not well-suited for a database using SMB protocols. Find another connection or disenfranchise the wi-fi users. They are almost certainly the source of your data corruption. There is no current solution for this problem unless you can use Citrix or some other remote connection manager software. And even with Citrix the solution is still not 100% solved - it is just improved.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 19, 2013
Messages
16,553
as mentioned in post #13 - but very briefly:D

I've never tested it in this sort of scenario, but using begintrans, committrans may provide some protection in that the transaction is rolled back if not committed. But personally would use SQL Server/express/MySQL
 

Users who are viewing this thread

Top Bottom