File Sync and Share and File Server Terminology Clashes

I was thinking about file sync issues, and realized there are some problems discussing or describing problems, because the terms "share" and "open" mean different things in different situations.

Some applications share data by opening a file in a shared folder on the server. They are all working in the same file, and the server's operating system mediates the process.

This is entirely different from people "sharing" files via a file sync service. These "shared" files are actually copies of files. People edit each file independently, and the service performs the "sync". If two people edit a file simultaneously, and save, the service will note that there's a conflict, and rename the files.

On a shared folder, two computers may access one file, and both "open" the file. Most people's experience with "opening" files is in Microsoft Word or Excel, which don't allow two people to open a file at once - and it'll tell you. So there's a perception that two people cannot open a file at the same time.

Unfortunately, this is inaccurate - it's Word and Excel that won't allow it, because each is trying to lock the entire file. MS Access happily allows multiple people to open the same file at the same time, because it doesn't try to lock the entire file. (You just cannot alter (most of) the design of the database.)

There are hundreds, perhaps thousands, of Windows applications that work by sharing a file on a file server. Prior to the rise of MySQL and low cost database servers like SQL Server Express, it was the primary method of implementing multiuser databases. Even after the rise of the low cost database server, I noticed that the Express versions of MS languages all encouraged the use of JET .mdb files. (Likewise, the popular SQLite DB uses shared files.) It's not just a Windows issue - OS X and Unix can do this as well - but Windows did it better than the Unixes, so it was more popular. (Unix software quickly went to client-server because they had better networking libraries.)

The main implication for file sync is that there are a small subset of files that cannot be placed into a file sync service. This includes databases, accounting software, workflow management software, and pretty much anything that allows two people to work on the same data at the same time, over the LAN, and doesn't require the installation of a database server.

Is it a big problem?

Not really. I didn't find many things online, and an MS moderator even made an erroneous statement that you could sync. Here's a person with the problem:

Question MS Access and Dropbox

The problem is that the files are synced when they are closed. MS Access holds the file open when the application is open. It's normal for a file to be held open for hours or days. So if two parties in two places have the file open, and add data, it's the last one to close it who "wins", and the first to close loses all their work.

I suspect this problem doesn't manifest itself frequently because the two people using the MS Access database are using it on their LAN. You don't often have the situation where two people in two different places are using the same MS Access database.