I was reading this article about using an access database over a network.
My understanding of what he writes is that when records are requested they are loaded in to RAM on the computer that requests them and removed from the mdb file.
The problem then is that if the network goes down the file cannot be reassembled as the records cannot make it back and the database gets corrupted.
Is this really what happens? I would have thought the records would just be copied out and perhaps flags placed on those records in the database.
I am likely the author of that article. The diagram being referred to is this one:
In above we see pictures in which "blocks" of data are pulled from the disk drive and into the computers ram.
So the original records of course remain on the hard drive. I supposed that diagram should have more "shading" in where those holes are. However it done this way to help with the explain. So the "orginal" data + records DOES exist on the disk drive - they are NOT removed.
So it is NOT JUST the act of pulling the records. This issue really only exists when the records are in your computer ram (the above right side) and you modify those records. The "holes" in above left side are this showing records that are in your computers ram that need to be updated (the updating occurs in your ram).
In MOST cases a break in the connection at this point in time will NOT cause corruption if records are not in fact flowing back from your ram over the network and back to the hard drive.
However, once the data starts to flow, then yes, a break in a connection is VERY much like a broken transporter in Star Trek. And yes then the above picture and diagram does much convey this issue.
So JUST pulling records and breaking the condition? No, not likely to cause corruption.
Just pulling some records and they are "pending" (dirty) and need to be written back? Again likely a network break at this point should not cause corruption.
However once the records start making their trip back then a break in that network conneciton is rather serious and then yes those missing "holes" in the database become an issue - corruption becomes a real high probability because "returning" records are going to push and shove around EXISTING records on that disk drive.
And using transactions in Access will not help. The MAJOR reason for this is that Access when using a file based back end does not write RECORDS to the disk drive but ONLY writes out a Windows file. So a file is something that can contain Power Point or Excel or Access data. The computer does not know or care what are writing out, but ONLY that you are writing out a file to the disk drive.
So Access with a file based back end is writing to a Plane Jane file windows file. We are working at the file level and NOT the record level. This is of course why when using SQL server that no file level corruption is possible from Access. Access NEVER directly writes to the file, SQL server on the server side does).
So cutting the network connection is much like cutting the connection to the disk drive. You are not cutting the writing out of records so much as you are cutting out the writing out of PARTS of the actual file on the disk drive.
When using SQL server then cutting the network connection means records stop flowing from the Access client but he disk drive writing to the actual SQL server file occurs server side and is NEVER affected nor cut.
So the major issue as to why corruption can occur is simply that the client side is using the windows file system OVER the internet. This does not occur when using server based technology and you shutting down your computer are not going to affect the book ordering system on www.amazon.com. The exact same reasoning applies with Access when using server based system – cutting Access does not affect things server side. You might not get the record(s) send down the network pipe, but that only means some records are never sent back to the server to be saved to a local file (but the saving to the local file occurs server side – not by Access).
It is the use of the windows file system OVER the network that is the reason for the risk of corruption. Use of transactions etc. will not help nor does it remove the fact that you not writing out records to the disk drive but actually track and sectors at the hard drive FILE level.