I searched into the Xojo documentation, sqlite.org, the internet… with no real answer.
The situation:
A ProBono application is running in two computer accessing each one its own sqlite db containing the same data (when they add Records, they copy the db file to the other computer).
The user does not know he uses Cloud on these WIndows machines told me: “When I create a pdf document on one machine, I have a copy in the other one” …
After some questions, it appears he uses Cloud drive without knowing it.
Now, He ask me to be able to do that with that ProBono application.
I do not found a way to lock the file while adding a Record, so the other machine can read, but not add a Record until the Record is validated (or so).
The user is a power user (on his mind), but know nothing about what is inside (how an application works behind the curtain). Example:
Q. “The application creates two Records with the same name, why ?”
A. “Because you made a typo while typind the name.”
It appeared there was two spaces in a two words Name (think for example “Manuel Dos Santos”).
I finished to add code to remove multiple spaces in field at Record Creation WIndow… And so on for dates (Popup, so no fantasy, all dates on YYYY-MM-DD model), Country: a Popup filed by a Text file, etc.
The only place errors can be made is in a Childs large entry: and of course I saw all and everything (bad dates, names in no order, father or mother only, sometimes the two) and one day he asked for stats on childs…
But, he asked questions, and my answer was: sort the field and I will be able to add something. He never done that. I added a window that display only these data, so it is easy to normalize them. No return, so I stopped asking about that.
And, of course, I do not have the hardware to do that.
Cloud syncing has delays, and its not an instant sync, and the sync and caching process creates a lot of moving parts. You should not sync a database file to two computers this way, and you may see some success doing it anyway but it will create huge problems and data loss.
Copying the file to the other computer after making changes works. Copying over network based on timestamp perhaps, tho this one also created problems with an insurer I worked at, because they wanted more than one person accessing at the same time, and didn’t want to use the SQL server they purchased .
Best option (with what I can gather from here) would be to host the database and create a single point of entry for edits (api or UI).
As long as this person ISNT using multiple computers at the same time you might be able to do something like this
do NOT put the DB ON OneDrive for regular use
the way services like dropbox, one drive etc work its pretty much guaranteed to ruin the DB IF you try to use it from such a shared drive
have the application periodically backup the in use DB to onedrive
when the app starts it COPIES the db from OneDrive to a working location (like Application Support) and uses that one for all inserts, deletes, etc
IF they are possibly using multiple computers at the same time this will NOT work and you DO need a server of some kind
I always forget about CubeSQL
Would still need one computer to be configured as the server so others could access it
But not a bad option for a small set up
There is no easy solution for this user story.
You will always run into troubles with file locks, missing data or even corrupt db file.
Maybe it’s time, your “power user” should start to think about using a terminal server?
Or making one of his existing machines to one with Thinstuff (https://www.thinstuff.com/)
.