I put this post here due to the fact that I’m not a professional programmer and while I feel comfortable programming with Xojo it is on a personal level. I have been tasked by my place of employment to create a Windows application that will use an access database or sqlite DB. I know that there will be no more than 3 concurrent connections to the DB so I’m leaning towards sqlite. Just a warning that I’m sure I will be asking many questions while building this application. While I was tasked for this application by my employer I was able to choose what environment I program it in and obviously I’m choosing Xojo. Just wanted to give fair warning to everyone that in the next month I will be asking questions while I run into them
Be aware that “concurrent connections” implies “network access to a database” and this is a recipe for problems if you dont run some kind of “database server”
placing a shared database (a file) on a network file share is not going to work well at all whether its access OR sqlite
just FYI
Norm,
thanks for the input
Maybe my knowledge and terminology is incorrect. There will be 3 techs reading and writing to the DB but not all at one time so no not concurrent but you are right as in there could be at some time. For this task there is zero database server to consider 
It is a project that can grow but for now any type of database server is out of the question and I figured SQLite using WAL would suffice for the birth of this project and then it can grow from there. It is basically go with this starting point or let the project die and I would rather go for it 
Concurent access isn’t the issue here
its quite literally where will this common database be located ?
Since SQLite & Access are both file based (you open a database FILE) for these people to share it the database file would have to be on a file server of some kind
Access and SQLITE using shared files on a server CAN be very problematic whether there is one or 10 people using it
The issue is that many network file systems dont implement locking quite right and a shared file databases like Access & SQLITE rely on file level locks to be able to do “concurrent” operations (actually ANY operations regardless of how many users there are)
http://sqlite.org/faq.html#q5
http://www.techrepublic.com/article/get-it-done-the-reasons-behind-access-corruption/
So a “server” for this is really required in order to NOT end up with a corrupted database all the time
Plain & simple
Now a “server” doesn’t have to be rocket science
Its really JUST an application running on a machine that other machines can connect to
Its just running the application that make the database accessible to others
Heck I run mysql and postgresql on my laptop and you’d never know they exist
Marco’s CubeSQL is exactly such an app for sqlite and its free for 3 users
You can basically build it almost exactly as if it were a standalone app then use the cubesql plugin to turn the sqlite db into a multi user one
Point your “task master” at the articles I pointed out and have them read them
I know a lot of people hear “database server” and their eye glaze over with “OMG thats a new expensive machine + software and OMG huge bills and headaches”
So dont call it a “database server” - call it a “database sharing application that we need to run on the file server” 
My advice - use a “database sharing application”
Anything else is just masochism
Another thing to consider… sure your employer has specified THREE(3) techs today… but what about in the future?
If you fail to design scalability into the solution, you will find your self back at square one at some point
Brian,
You should also consider Microsoft’s SQLExpress. It’s a free version of their SQL Server product designed for exactly what you are trying to do. If or when you guys outgrow it’s capabilities, you just buy a sql server license and install the bigger version. You can backup the SQLExpress version and restore into the full version.
The nice part is that it gives you many of the advantages of a real database server without the startup cost. They merely limit CPUs, memory, db size and connections.
If you scroll down on this page, you can see what it includes, vs the other products.
https://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/
I don’t see any problems using SQLite for such a task. Not every company grows most healthy companies stay the same size for years and decades.
You can always move to CubeSQL after development has finished or if at some point there will be many many more users. Up to three (or five) users it is even free.
[quote=240201:@Eli Ott]I don’t see any problems using SQLite for such a task. Not every company grows most healthy companies stay the same size for years and decades.
You can always move to CubeSQL after development has finished or if at some point there will be many many more users. Up to three (or five) users it is even free.[/quote]
Reread my fist post about using a stand alone shared sqlite database on a network file share
Thats a bad way to start out
IF he already knows he has to share it between 3 techs today then start out right
Besides the adjustment of the connection properties you don’t have to change anything when moving from SQLite to CubeSQL. And it is far easier to develop with a local SQLite database.
developing as a stand alone sqlite is definitely “easier” but dont make the mistake of deploying it that way
and there are things that unless you test them with multiple users you wont run into
Say two users modify the same record at the same time (update it, delete it etc)
You need to think about concurrency issues right up front otherwise the first time you deploy & have several users you’ll run into problems
I wanted to thank everyone for taking the time to provide valuable insight and information. I will spend some time researching everything. CubeSQL does sound interesting and could be a great option. I’m a contractor at an Air Force installation and my options of running/installing a “database server” is not an option that is why I was leaning towards using sqlite and putting it on a shared drive.
Lot’s to think about and thank’s everyone again for the input.
don’t confuse a “database server” with a dedicated “server level” computer.
A database server could just as easily be a background process running on any one of the computers for the 3 techs, or any other computer already on the network.
This is almost true, but the cubeSQL implementation of PreparedStatements is significantly different than the “standard” Xojo database mechanisms. You definitely need to have different code for using them…
[quote=240291:@Dave S]don’t confuse a “database server” with a dedicated “server level” computer.
A database server could just as easily be a background process running on any one of the computers for the 3 techs, or any other computer already on the network.[/quote]
Exactly why earlier I said DON’T call it a “database server” as some people will just go “OMG thats so much work & cost & …” and panic
Its just an app running on a computer that other computers can talk to
[quote=240286:@brian franco]I’m a contractor at an Air Force installation and my options of running/installing a “database server” is not an option that is why I was leaning towards using sqlite and putting it on a shared drive.
[/quote]
Will the three techs be using it from the SAME computer ? or from different computers ?
If all three techs are never there at the same time & all share one computer then you could just use that one computer with a shared file on it
Otherwise a “database sharing application” is the safest option
A shared file on a network drive is a problematic setup and always has been
That is very true, but as I understood it, this is an in-house app he wants to develop. Not every application needs to be implemented on a level of one being sold to customers.
I have written a dozen smaller applications for in-house use (up to a non-auditable double entry accounting system used as a “shadow” system along the one our tax advisor uses). All these applications were always as lean as possible. With SQLite databases on network drives (OS X Servers). Up to 10 to 20 users at the same time. In almost ten years now for the oldest ones I’ve never encountered any problems with it.
My two very large application written on a more professional level were built completely different they have been licensed to other companies so they better were. At one point I was tempted to re-develop the smaller applications and use everything I had learned writing these large apps and the libraries of them. Shortly after I had started working on it I stopped it was an overkill. Many advices like: keep your SQL code database independent, develop your application as scalable as possible, etc. are superfluous for smaller apps IMHO.
Ignoring the advice of the vendor who writes the sqlite source code means you’ve been lucky
I’ve done the same with Access Database many many years ago on Windows shares and since both are from the same vendor you’d think this should work well.
It can. And it does.
Right up until you get the “Hey why doesn’t the app work today” when you arrive for work only to find that the DB is 100% corrupted.
YMMV - but I’d rather not rely on good luck
Marco @ http://www.sqlabs.net also has a sale on the CubeSQL licenses at times. A while back you could buy a license for $99.00. That’s a heck of a deal for a really reliable database server.
I wrote an app for the school my wife teaches at to track student attendance. Well over 50 teachers update the database every hour and they’ve never had any issues in 3 years and the school was able to purchase a license for $99.00.
Brian, for the Windows environment, the advice to use SQL Server Express is the best you received as far as I am concerned. Do yoiurself a favor and don’t overlook it. As already mentioned earlier by Dave, you do not absolutely need a decdicated server to run it. The networking issues that you will have are not specific to SQL Server. Securing the access to a CubeSQL or to a SQL Server instance are essentially the same set of concerns and tasks.
@Norm
The 3 techs will be at different workstations
Working in such a restricted network due to the fact that since we are on an Air Force network and everything “functional” is not approved. It would just make the world easier and keep contractual obligations and workflow going if I could have used a single sqlite database but obviously that is not the answer. I appreciate all the input from everyone and will have to spend some time getting my proposal to management ready.
Thanks again everyone!
Any one of those three workstations could function as the “server” as long as its powered on