I'm going to toss in an explanation that
@Umpire and
@Zany can use for their IT departments in terms that they will understand. It is long but it has the details needed to correctly respond to IT arguments.
Access uses a protocol that is also used by Windows for File Sharing and Printer Sharing. It is called Server Message Block or SMB. Version 1 of SMB was the worst about this but SMB2 and SMB3 still have the similar problem. SMB is a member of the TCP family of protocols (as opposed to UDP, which is more web-oriented.) Being in the TCP family means it is NOT a connectionless protocol. SMB
does use connections. With TCP connections, you get various security features including that message headers have sequence numbers so that you can re-assemble multiple packets in proper order. The connections are negotiated as would be the case for all TCP-family network sockets. You can also verify that nobody is inserting messages into your data stream because the sequence numbers are always, well..., sequential.
When you use SMB over WiFi, operation becomes critical when you have a WiFi "drop" event. At that moment, the connection breaks. The network driver will know very quickly due to loss of carrier. TCP technically allows a "reconnect" (a restoration of the network session) to occur if the network's security policies allow it. However, it is common for IT departments to disallow a reconnect. For instance, the U.S. Navy didn't allow reconnect events EVER. (Not limited to Access, not even limited to Windows systems.) This restriction is imposed to prevent an opportunistic hacker to reconnect to someone else's session, so it is an understandable restriction.
if session reconnection is disallowed then that connection is gone and the sequence numbering continuity is gone. The system has to start over again - but that means that any transfer that was in-progress is now forever interrupted. There is no getting it back, there is no chance of finishing any transmission that had been interrupted. Whatever was GOING to be sent cannot be sent now.
The corruption that we often discuss in association with WiFi is simply that with a network drop, you might actually do a PARTIAL update of a set of disk buffers. This partial update leaves the DB in an inconsistent state because PART of that buffer has been updated with new data but part has remained as old data. If the buffer in question contained structural meta-data (like an index table, for example) then that table's index is now totally corrupted and unusable. If a table was being compacted or if a massive update was underway at the time, that table is toast.
It is a fair question to ask "Why does Word or Excel work better over WiFi?" The answer is the pattern of their updates.
- Word doesn't write back anything until you do a SAVE or the automatic-update timer has expired, at which point the update (of the ENTIRE FILE) occurs in bulk. I think the Word default used to be 10 minutes, but to be honest, I never worried about that because I never used Word on a remote file anyway.
- Excel operates the same way. It does not do partial updates. Don't know if it has an auto-update timer, but doesn't matter. However, last I checked, Excel really DOES NOT LIKE file sharing with shared WRITE abilities. In our office, if you were updating an Excel file that was being shared, nobody else was allowed to do that while you were busy.
- Outlook USUALLY keeps a local .PST file so SMB isn't an issue unless someone has delegated shared authority to their post office or calendar. For the Outlook calendar, any updates are - in my experience - rare and not done piecemeal.
- PowerPoint ? Damned if I know its pattern intimately, but I know it saves on demand and perhaps it also saves when you switch to a new slide. Don't know if it has an automatic periodic save. But its save pattern is NOT nearly as frequent as the Access pattern.
- Access does updates whenever a user takes an action that would commit a change, and it DOES perform partial updates because that is how it preserves the ability to share back-end files. Therefore, Access has greater exposure to WiFi "drop" events because it is busier doing partial updates as needed to maintain data sharing in real time. That frequent partial SAVE pattern for Access is what gives power to its shared back-end database format, which allows smaller departments to do real database operations on a relatively smaller, inexpensive system.
There is a tendency for people to denigrate Access for its sensitivity to network drops. Hate to break it to them, but I've worked with a "big boy" database management system - ORACLE - and if there is a network drop between the ORACLE server and its network-attached storage device, you STILL have the same exact headache - a corrupted DB. If SQL Server is using network-attached storage, I guarantee it will have the same problems. Because Access is using a
file-oriented protocol to "diddle around" in the back-end file, it should be compared to a system that has a database on a network-attached storage device. Therefore, make your comparison in an "apples-to-apples" manner. Don't compare Access on Wi-Fi to Word or Excel. Compare it to ORACLE or SQL Server with a flaky connection to network storage. Or compare it to a system which has Wi-Fi connections to network-attached storage.
That should be enough ammunition to make the case for hard-wiring an Access system.
