[RESOLVED] Choosing a Server-less Database for a Desktop Application

I'm about to start coding a Windows Form application using Visual Studio 2019. The application will provide hobby collection management and a few other hobby related functions. Since it will be used mostly (if not entirely), by non-technically savvy adults without any IT infrastructure or support, the critical application requirements include:
1. Installation and configuration must be highly automated and idiot-proof.
2. On first launch, the application must automatically either create an initialized database, or copy one from the installation package.
3. The database platform must be server-less and maintenance-free, except for any utility functions that may be automated into the application itself (e.g., backup, compact/repair, etc.).

The actual data load will be pretty light. The main table will be tested with at least 10,000 records but the average user will probably only have a few hundred, or perhaps a thousand or two. There will be a handful of other tables that would seldom have more than a hundred records each, plus a dozen or so small lookup tables. The database just needs to support ordinary CRUD operations, as well as foreign key and NOT NULL constraints. Support for strong typing is highly desirable, but its absence MAY not be a deal-breaker.

I've been coding and supporting database applications for long enough that implementing all the intended features and meeting all the above requirements is stuff I've already done. My one uncertainty concerns the choice of database platform.

My first inclination is to use a Jet database, because I've already used that in my 9-5 job to implement a similar project that meets all the above requirements, as well as a number of other desktop projects. My only hesitation is a concern that future versions of Windows may break applications that use a deprecated technology like Jet. If including the appropriate data access type library in the compiled application (along with targeting a recent version of the Dot Net Framework) will assure that the application continues to work on any future version of Windows which supports the targeted Dot Net version, then I'll go with Jet.

However, if the future viability of an application using a Jet database is sketchy, I'll probably want to choose another technology. The other platform I read about a lot is SQLite. I'm familiar with its many virtues, but I'm also concerned about its quirks (including some that are claimed to be features rather than bugs). My main concern is the lack of strong typing in SQLite.

I'll be grateful for any insight anyone may have regarding Jet, SQLite or any other database platform that might be suitable for this project.

I would go with either the Access option with ACE (rather than Jet), or SQLite.

-tg

I don't think ACE buys you anything but a potential deployment headache along with version churn. Just use Jet 4.0, period. It's part of Windows out of the box, it is well-documented, and still gets patches as needed through Windows updates. It isn't going anywhere.

SQLite just makes me laugh. Support is so sketchy, its type system is foreign to Windows, and I gave up on it when it kept corrupting BLOB data. You also have a version churn problem along with a deployment nightmare.

For completeness sake: Firebird

Can be server-based, but also filebased desktop db

EDIT: In your case it's Firebird Embedded (Single-Place/User)
https://en.wikipedia.org/wiki/Firebi...tabase_server)

One of it's main advantages: It has an event-system
https://firebirdsql.org/file/documen...ird_events.pdf

Thanks, dilettante. It seems that Jet 4.0 only works on 32-bit platforms. I'm developing on a 64-bit Windows 10 Professional machine, but need the application to run on both 32 and 64 bit machines.

Funny, seems to be working just fine for me whether running on 32 or 64 bit Windows installs.

So I assume you are doing something like using a compiler that can output x64 code. If so, you are sort of screwed and will have to deal with deploying some add-on software (ACE) or some 3rd party stuff.

But you say "run on both" so I think you must be lost. Your x64 programs will not run at all on a 32-bit Windows install, only 32-bit x86 code will run on both. Or perhaps you are compiling to .Net scripting language bytecode targeted to be interpreted on either platform?

If so, you'll need a deployment strategy that can not just manage deploying the necessary add-on software but do so installing the correct flavor.


The easy and smart move would probably be to target x86 only and use Jet 4.0 and call it a day. It sounds like you have impaled yourself on one of the many dash-mounted knives .Net has pointed at the driver.

I understand that only 32-bit code will run on both 32-bit and 64-bit machines, but my wording probably didn't reflect that very well.

In any case, I've been attempting to do exactly what you suggest and just go with targeting x86 and using Jet 4.0, but I can't get it to work. For example, on my Windows 10 Professional 64-bit development machine, the following code:

Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;DataSource=C:\Data\TestDb.mdb")
cn.Open()

fails on the error "Could not find installable ISAM." Do you have any idea how to get around that?

"Could not find installable ISAM" is a Jet 4.0 exception, so we know that part works.

I'd guess that you just have a poorly formed connection string. Try something more like:

Code:

---

"Provider='Microsoft.Jet.OleDb.4.0';DataSource='C:\Data\TestDb.mdb'"

---

ADO can accept quotes (") or apostrophes (') as delimiters when it parses a connection string into a series of key/value pairs. Once it has seen (or guessed) the Provider and other keys that belong to it, it can pass the rest of the list of pairs to that Provider to process.

The reason to "quote" values is that some characters (such as semicolons and spaces) will impact the parsing.

You don't appear to be using ADO but something that sort of "apes" ADO, but it may have its own parsing rules to deal with. For example perhaps it doesn't accept apostrophes and requires only quotes or something. For all I know it might also break tokens at "" characters.

But looking at the docs for this class it does appear that the intent was indeed to ape ADO with only a few minor exceptions.

OleDbConnection.ConnectionString Property is what I took a look at.

It turns out that the presence or absence of the single quotes makes no difference. But when I changed DataSource to Data Source it worked. It's always the little things. Thanks for hanging in there with me, dilettante.

Well your failing example did have me puzzled. Normally you'd only need the quoting if the path name contained spaces.