Modern alternatives to Microsoft Access for a small-scale desktop database

by sjy   Last Updated April 16, 2018 04:05 AM

I've been using Microsoft Access to build a database which solves a similar problem to software like CaseMap, Masterfile, Case Notebook, and CaseFleet. Essentially, the database needs to allow users to create a graph where the nodes include data types like Fact/Event, Person/Witness, Document and Tag, and query the graph to answer questions like 'which documents contain evidence of this fact,' 'who witnessed this event,' and 'what facts need to be proved to establish the claim represented by this tag.' Users must be able to construct these queries and produce nicely-formatted reports without learning a query language.

Advantages of Access

  • It's already part of the standard operating environment in many corporate networks, so users don't need to ask IT to buy or configure anything.

  • It supports multiple concurrent users using just a file on a shared network drive. Scaling's not a problem, because there's rarely a need for more than 3 concurrent users and the database shouldn't grow to more than 100 MB – it only stores metadata, and you can just create a new database for each new case. There's no need to maintain a database server, and users can easily understand where the database is and how to back it up or distribute it.

  • It's not web-based, so you don't need to worry about sharing confidential information with a third party. Source documents, which may not come in a predictable format, can be stored locally, rather than uploading gigabytes to an internet service that may not be able to do anything useful with the data anyway. A local database can index source documents using the filesystem API, and allow the user to open them instantly in their preferred application.

  • There's no distinction between users and developers, so power users can modify the table schemas and forms themselves to adapt the database for particular use cases.

  • Users can even add new multi-valued fields without needing to understand junction tables and the relational model, and these are supported by Access's built-in filtering tools.

Disadvantages of Access

  • The only available programming language is VBA and there's no integrated support for version control (although this project on GitHub looks helpful).

  • It's quirky and buggy. The error messages in the VBA debugger are very unhelpful, and I've repeatedly lost work because my code caused Access to crash with no error message. Doing anything that isn't natively supported by the built-in controls tends to involve nasty hacks documented only on Access user forums.

  • It feels like legacy technology: does Microsoft Access have a future? Microsoft seems to be moving away from Windows desktop apps and encouraging developers to build PowerApps on Azure or native apps on the Universal Windows Platform. (But following the trend of cloud-based, mobile-enabled software seems irreconcilable with my desire to avoid the maintenance, confidentiality and security concerns associated with a separate server.)

  • My problem might be better solved by a NoSQL database. Although there are a few data types that will always be used, users should generally be free to create new data types or add new fields to the existing data types and query on those fields. Even if queries are inefficient, performance shouldn't be a problem because the scale is relatively small.

Potential alternatives

I like functional programming, I plan to work on this alone as a side project, and, while it would be nice if the application was cross-platform, I'm mainly interested in Windows. I'm thinking about building a WPF application in F♯ with an integrated Redis server (after reading this StackOverflow answer). The main concern I have is how to replicate the 'concurrent users over a network share' feature.

One option might be to use a lock file to determine whether opening a database file should start up a server or connect to another server already running on the LAN. Using a server-client model, even when the database is being used locally by a single user, might also make it easier to upgrade to a dedicated database server if it becomes necessary to add remote users over a slow VPN.

This seems hacky though, so I'm also considering SQLite. Users could create their own data types with a sufficiently friendly schema-manipulation UI, or user-created fields could just be stored in a JSON column. Theoretically, the database file could live on a network share, but the Appropriate Uses For SQLite page warns that:

SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, 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.

The answers to questions on StackOverflow about SQLite over a network share and How to make SQLite robust over a Windows shared drive are also pretty disappointing. They suggest that there is still no safe way to share a database over a network filesystem, even though the maintenance cost of a database server is a problem for many small businesses.

Questions

  1. Are there any modern alternatives to Microsoft Access for building serverless, small-scale desktop databases? Is it unwise to build something like this in Access in 2018?

  2. Is there any way to reliably share a database over a network file system, or should I abandon this goal and just focus on making it easy for users to run a local server?

  3. Are there any NoSQL databases with lightweight servers that can easily be bundled inside a .NET application and load data from a network share?



Related Questions


Should i be using My own MVVM implementation?

Updated October 15, 2017 11:05 AM