# Multi User Access to SQLite on Network Drive **URL:** \ **Category:** Databases\ **Created:** [July 31, 2014, 9:25am UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271 "2014-07-31T09:25:10Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Urs\_Jggi](https://forum.xojo.com/letter_avatar_proxy/v4/letter/u/c0e974/32.png) [@Urs\_Jggi](https://forum.xojo.com/u/Urs_Jggi)\ **Post date:** [July 31, 2014, 9:25am UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/1 "2014-07-31T09:25:10Z")
Hi I have written a program that accesses single SQLite database (files) on a network drive under Windows. The program can be started by multiple users on multiple computers. At the moment I do the following: I write into the database name, date and time when the user starts editing (needs to press a button). When another user opens the database I read out those fields an say something like ‘database is edited by mister x. you can only ready.’ However, in tests the modifier user has problems to close the database when another user opens the database (only read operation, no write operations). What’s the best solution to grant only one person read-write access to the database and all others only read. According to [http://documentation.xojo.com/index.php/SQLiteDatabase.MultiUser](http://documentation.xojo.com/index.php/SQLiteDatabase.MultiUser) I shouldn’t enable multiuser: “WAL does not work over a network filesystem” ---
**Author:** ![Markus\_Winter](https://forum.xojo.com/user_avatar/forum.xojo.com/markus_winter/32/144_2.png) [@Markus\_Winter](https://forum.xojo.com/u/Markus_Winter)\ **Post date:** [July 31, 2014, 1:23pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/2 "2014-07-31T13:23:37Z")
Have a look at Brad Hutching’s StudioStable Database ---
**Author:** ![Norman\_P](https://forum.xojo.com/letter_avatar_proxy/v4/letter/n/858c86/32.png) [@Norman\_P](https://forum.xojo.com/u/Norman_P)\ **Post date:** [July 31, 2014, 3:20pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/3 "2014-07-31T15:20:07Z")
[quote=116274:@Urs Jäggi] According to [http://documentation.xojo.com/index.php/SQLiteDatabase.MultiUser](http://documentation.xojo.com/index.php/SQLiteDatabase.MultiUser) I shouldn’t enable multiuser: “WAL does not work over a network filesystem”[/quote] [http://sqlite.org/faq.html#q5](http://sqlite.org/faq.html#q5) ---
**Author:** ![Kenneth\_Lossman](https://forum.xojo.com/user_avatar/forum.xojo.com/kenneth_lossman/32/2875_2.png) [@Kenneth\_Lossman](https://forum.xojo.com/u/Kenneth_Lossman)\ **Post date:** [July 31, 2014, 3:37pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/4 "2014-07-31T15:37:15Z")
CubeSQLServer is also a good alternative… ---
**Author:** ![Russ\_Lunn](https://forum.xojo.com/letter_avatar_proxy/v4/letter/r/5f9b8f/32.png) [@Russ\_Lunn](https://forum.xojo.com/u/Russ_Lunn)\ **Post date:** [July 31, 2014, 3:44pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/5 "2014-07-31T15:44:12Z")
have you checked that file caching is turned off on the server? look at properties of the share, advanced settings -\> caching. make it so no files are allowed to be cached. might make it work better. I’ve used it with fox pro tables in the past. ---
**Author:** ![Russ\_Lunn](https://forum.xojo.com/letter_avatar_proxy/v4/letter/r/5f9b8f/32.png) [@Russ\_Lunn](https://forum.xojo.com/u/Russ_Lunn)\ **Post date:** [July 31, 2014, 3:45pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/6 "2014-07-31T15:45:46Z")
also mssqlserver has a free version that works well ---
**Author:** ![scott\_boss](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/c5a1d2/32.png) [@scott\_boss](https://forum.xojo.com/u/scott_boss)\ **Post date:** [July 31, 2014, 4:22pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/7 "2014-07-31T16:22:41Z")
network shares use caching (most of them at multiple layers) and is not good for having multiple people edit or potentially edit the file. As a storage admin/engineer, that is the biggest pain in the backside that we have when it comes to network filesshares. if you need multiple people editing/viewing/adding to a common database, I would either use a RDBMS (like PostgreSQL, MySQL, MS SQL, etc) or something like cubeSQL. I use cubeSQL a lot and works very well. good luck!! ---
**Author:** ![Brad\_Hutchings](https://forum.xojo.com/user_avatar/forum.xojo.com/brad_hutchings/32/143_2.png) [@Brad\_Hutchings](https://forum.xojo.com/u/Brad_Hutchings)\ **Post date:** [July 31, 2014, 8:21pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/8 "2014-07-31T20:21:09Z")
[quote=116328:@Markus Winter]Have a look at Brad Hutchings’ [sic] StudioStable Database [/quote] Please don’t do this, Markus. The recommendation requires explanation and motivation. In the past, empty recommendations like that have gotten me angry users who don’t understand why shared database file access on a network is a disaster. ---
**Author:** ![Aurelian\_N](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/b4bc9f/32.png) [@Aurelian\_N](https://forum.xojo.com/u/Aurelian_N)\ **Post date:** [July 4, 2017, 8:54am UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/9 "2017-07-04T08:54:09Z")
Well what if the case requires that only, for example we have 4 teams with a very restricted access and they can access only the shared drive via the vpn, so no servers no databases no mysql, no postgresql , what we can do in this case ? I was thinking to use a method same as the File Lock system which means if one of the team need to access the db to open it, save it in the Memory and create a file on the drive stating who is using it, then once the db is updated and closed it will dump all the in Memory db to the file overriding the shared file db and updating the file that it finished accessing the db so that the next one can use it. i know it`s overkilling but in this way they can still do something. Eventually with this method they can even open the db in memory as a read only if anybody else is accessing the db that time. Any other ideas on this ? Unfortunately that is the only option for me the Shared drive and i have to use it as much as possible. ---
**Author:** ![Phillip\_Zedalis](https://forum.xojo.com/user_avatar/forum.xojo.com/phillip_zedalis/32/1151_2.png) [@Phillip\_Zedalis](https://forum.xojo.com/u/Phillip_Zedalis)\ **Post date:** [July 4, 2017, 9:18am UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/10 "2017-07-04T09:18:35Z")
Doing network shared SQLite file over VPN would be even WORSE than over a local network. You absolutely do not want to do this. Use cubeSQL (or now Valentina Server) to provide a true client/server capability for remote clients. ---
**Author:** ![Jean-Yves\_Pochez](https://forum.xojo.com/user_avatar/forum.xojo.com/jean-yves_pochez/32/21769_2.png) [@Jean-Yves\_Pochez](https://forum.xojo.com/u/Jean-Yves_Pochez)\ **Post date:** [July 4, 2017, 10:08am UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/11 "2017-07-04T10:08:49Z")
often the shared drive is on a NAS, and you can most of the time install postgres or mysql on it. if it’s not the case, change the shared drive ASAP. ---
**Author:** ![anon20074439](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/ac8455/32.png) [@anon20074439](https://forum.xojo.com/u/anon20074439)\ **Post date:** [July 4, 2017, 10:43am UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/12 "2017-07-04T10:43:36Z")
If you don’t have any option to install a service next to your shared folder then I would use a cloud based DB over a shared drive option in a heartbeat. It will not be a case of IF my database corrupts, but WHEN. Corruption will happen, especially over a transient (external) connection where you can’t control the connection. All that being said, a way to do it would be: File locking, the first person to open the database drops a file telling all other clients they can only read from it. If someone performs an update/insert, drop a file with the current date/time containing the sql query they would have performed. Get the person who has the lock on the database to run the sql form that file and delete the file when its complete, then move onto the next file if one exists. When the person is finished with the db, they remove the lock. The only issue you have to make a contingency for is having the lock file left when/if the person who owns it having just lost their internet connection. Put a timeout into the lock file that tells other connections when they can remove the file and lock it themselves as you haven’t seen that user for X hours so they must be having a problem. This will all be pretty clunky but pretty robust, you could still corrupt the db if you lost internet in the middle of doing something important though, so keep a rolling backup using your preferred method (GFS etc) See [https://sqlite.org/lockingv3.html](https://sqlite.org/lockingv3.html) for more information on locking. ---
**Author:** ![Aurelian\_N](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/b4bc9f/32.png) [@Aurelian\_N](https://forum.xojo.com/u/Aurelian_N)\ **Post date:** [July 4, 2017, 12:22pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/13 "2017-07-04T12:22:05Z")
Well i guess i`ll use it just for storage as the data is not updated all the time so the way i`ll work i guess will be getting the db every other day, so one day for one team to update , the next day other team and so on, and the drive will be for storage only for the meantime. Thanks again for all the advices. ---
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [July 4, 2017, 6:14pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/14 "2017-07-04T18:14:03Z")
People considering having many users access an SQLite database over a network should read: [http://www.sqlite.org/whentouse.html](http://example.com) In particular, the section on when NOT to use SQLite. See under “Client/Server Applications” where the advice is: > [@](#): > > File locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it. IOW, you cannot fix it in your application, whatever you do. ---
**Author:** ![Jean-Yves\_Pochez](https://forum.xojo.com/user_avatar/forum.xojo.com/jean-yves_pochez/32/21769_2.png) [@Jean-Yves\_Pochez](https://forum.xojo.com/u/Jean-Yves_Pochez)\ **Post date:** [July 4, 2017, 6:47pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/15 "2017-07-04T18:47:55Z")
> [@338997:@Tim Streater](#): > > File locking logic is buggy in many network filesystem implementations (on both Unix and Windows) pretty sure it’s also buggy under macos ! ---
**Author:** ![Louis\_D](https://forum.xojo.com/letter_avatar_proxy/v4/letter/l/cab0a1/32.png) [@Louis\_D](https://forum.xojo.com/u/Louis_D)\ **Post date:** [July 4, 2017, 6:54pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/16 "2017-07-04T18:54:45Z")
With Postgre and free versions of every major RDBMS (MSSQL, Oracle, DB2…) plus other choices such as Valentina, Firebird, etc., one really does not have much justification to use SQLite in networked, multi-user scenarios. Every warning was made. It is just a plain bad idea to use SQLite in this context. Even more so when (more) capable and free or low cost alternatives abound. The learning curve is there, but major RDBMS are well documented. Pick the one that suits your situation best and run with it. Forget SQLite in this context. ---
**Author:** ![scott\_boss](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/c5a1d2/32.png) [@scott\_boss](https://forum.xojo.com/u/scott_boss)\ **Post date:** [July 5, 2017, 10:36pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/17 "2017-07-05T22:36:09Z")
> [@339005:@Jean-Yves Pochez](#): > > pretty sure it’s also buggy under macos ! MacOS falls under UNIX in this quote. ---
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [July 7, 2017, 9:17pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/18 "2017-07-07T21:17:45Z")
> [@339122:@scott boss](#): > > MacOS falls under UNIX in this quote. Sure. And like consumer disk drives that lie about whether they’ve written the data to disk or not, I expect that consumer level file systems lie too, in order to get better performance. ---
**Author:** ![scott\_boss](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/c5a1d2/32.png) [@scott\_boss](https://forum.xojo.com/u/scott_boss)\ **Post date:** [July 8, 2017, 12:18am UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/19 "2017-07-08T00:18:17Z")
> [@339436:@Tim Streater](#): > > Sure. And like consumer disk drives that lie about whether they’ve written the data to disk or not, I expect that consumer level file systems lie too, in order to get better performance. yes yes they do. ---
**Author:** ![Stam\_Kapetanakis](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/919ad9/32.png) [@Stam\_Kapetanakis](https://forum.xojo.com/u/Stam_Kapetanakis)\ **Post date:** [April 11, 2020, 11:16pm UTC](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271/20 "2020-04-11T23:16:29Z")
Hi all, sorry for reviving this old thread, but i find myself is a similar situation as the parent poster - trying to implement a multi-user solution in a corporate environment that absolutely does not allow any server installation or access, where the only option is to have a solution on a shared drive. However as stated, I can’t just have an SQLite file just sitting on a shared drive as while multiple users can read, any multiuser write will eventually lead to database corruption. Can the database write functions be delegated to a helper/console app on the shared drive? [Next page](https://forum.xojo.com/t/multi-user-access-to-sqlite-on-network-drive/18271.md?page=2)