Can't open Microsoft Access Database inside a Microsoft SQL Server Filetable Folder

Question

We recently started to migrate the Data on our Fileserver to SQL Server (Version 14) Filetables and I encountered a problem with MS Access Databases (.accdb, Access 2007).

When the .accdb file is located in a "normal" Windows shared folder on the server, I can open it with no problems. When I copy the same file into the Filetable folder, i get an exception that the Database is locked by an unknow user and can't be opened or locked.

All other filetypes I tested work fine on the Filetable share.

The users log into the Fileshare with local accounts on the server, I've added permissions for this accounts to SQL Server. Is there something different to take account for with MS Access databases that are located in Filetables regarding permissions?

Answer 1

You can't work with multi-user Access databases on a non-SMB network storage (and in fact, until this bug is fixed, you can't reliably work with Access databases on SMB network storage either unless you avoid leasing).

Access databases require that many small writes to files and partial locks on files are allowed. Most network storage solutions, however, only support locking the entire file and don't support small writes or have terrible performance while supporting it, or cache data locally and cause terrible concurrency issues when multiple users are trying to use the same database.

This is very different from other types of files, where it's very usual to lock the entire file, not write to it at all until you save it, and overwrite the entire file when you save it (e.g. Excel). These kind of files do fine on a filetable.

Unfortunately, you will have to rethink your approach, as I don't think it will ever work. You might want to have a dedicated file share for Access that uses SMB with leasing disabled (or just migrate your Access data to SQL server and use linked tables and local copies of the database for every user)