Results 1 to 11 of 11

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

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    32

    Resolved [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.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,526

    Re: Choosing a Server-less Database for a Desktop Application

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,967

    Re: Choosing a Server-less Database for a Desktop Application

    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.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,721

    Re: Choosing a Server-less Database for a Desktop Application

    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
    Last edited by Zvoni; Sep 29th, 2021 at 04:35 AM.
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    32

    Re: Choosing a Server-less Database for a Desktop Application

    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.

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,967

    Re: Choosing a Server-less Database for a Desktop Application

    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.

  7. #7

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    32

    Question Re: Choosing a Server-less Database for a Desktop Application

    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?

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,967

    Re: Choosing a Server-less Database for a Desktop Application

    "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.

  9. #9
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,967

    Re: Choosing a Server-less Database for a Desktop Application

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

  10. #10

    Thread Starter
    Member
    Join Date
    Apr 2010
    Posts
    32

    Re: Choosing a Server-less Database for a Desktop Application

    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.

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,967

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

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width