I used MS Access to create a fully working prototype of a solution to a big client. It was mostly meant as a proof-of-concept type of thing, to get accept on the basic functionality and general UI, before re-writing it in more of an enterprise grade tool.
So, the demo happened and the Access file was handed over. We didn’t hear back.
About 15 years later, I bump into an old colleague - “you know that Access database you wrote? We just pulled that out of production about a year back and replaced it with a new thing.”
This just to say that Access certainly can be “good enough” for lots of use cases too.
I have always pondered how to actually buy Access. At one point it was part of Office but licensing or buying it currently is somewhat of a mystery - including where/how to host apps that you build.
Access runs on a local copy of Windows. No need to “host” elsewhere unless another Windows computer. Data can reside in the local database file, but best to separate data from queries/reports/VB/etc. Data can also exist on local or remote database servers including MariaDB, MySQL, Oracle, Microsoft SQL Server, SharePoint and probably others. Been a while since I looked. Also can connect to Excel spreadsheet tables if one wants.
For when i was actively using it, for years I ran it in a Windows running in VMWare and/or Parallels on a Mac.
Good question. I would say it may be a good alternative to Excel macros. If you are running regular, and varied reports based on big spreadsheets (thousands or 100,000 of rows) and you are being asked to do data analysis on them e.g. filter, vlookup, amend data then there will come a point when you need to automate this in some way.
Compared with a cumbersome, slow macro for example you may find SQL super fast and flexible. I’ve just learnt it and I love it.
Some use cases: database management systems become useful when your datasets get large, when you have to worry about relationships between records and want a systemic way to ensure integrity, when you want to explore relationships between different sets of data.
One note, the version installed by default on the Mac is disabled for some critical things, like adding extensions. It’s also an older rev.(at least on my Catalina system) If you do use SQLite I’d suggest doing a clean install in a directory where it won’t conflict with the mac system version.
Speaking of SQLite extensions, this repo is a lifesaver – I’ve only used it for a few things (especially adding a median function!), but it’s nicely structured: SQLean
That’s the one I am using, or rather still trying to use, issue for me is that I’m trying to use it within both Android Studio and PyCharm and I’m still having problems getting it installed properly. I think it’s a path problem but wasn’t able to work on it the last 2 days. I’m lookingt at it for the UUID, and CRYPTO extensions.
I’m with others here who go for SQLite. Perhaps doesn’t implement the SQL of MariaDB or postregeSQL; I lament the absence of some form of PROCEDUREs for example. The major advances for me is it small footprint and no necessity for a network connection — even to anything in the 127.0.0.0/8 block. Plus it can be used via/for iOS/iPadOS apps too. All very desirable when in the deepest London Underground tunnels because there is no Internet down there.
I’ve been watching and reading through this thread since it started.
Looking for some advice / suggestions / ideas on possible options.
I’m building a company dashboard.
Data comes from Zoho CRM (Zoho Books) and Shopify.
Currently I use Zoho Analytics for our detailed reporting, but I find it really limited / structured when it comes to displaying the data how I want it.
I’m very focused as well on having the staff manually enter some data (for example the sales stuff enter their numbers daily) as I find this creates more ownership of the numbers. I don’t rely on these numbers for my detailed reporting (as I use the actual figures from Zoho / shopify) - they’re mainly used in our daily huddles.
I love the idea of a software like re-tool, but I’m not sure where to start given all the data is currently in Zoho.
I too lament the absence of stored procedures in SQLite, it has the appearance of an idealogical position. The developers have always stated the primary purpose of SQLite is to be an application data store, so the stored procedures aren’t of significant value as the function they usually fulfil — data integrity — can and should be met by application code. That’s absolutely fine, but to me misses the point that the data may be used by more than one application, so ensuring its quality in the database does have value.
I don’t think I’m alone in this analysis as I recent came across this project from Meta. It is essentially a compiler that reads a flavour of procedural SQL and generates C code against the SQLite C library. You compile the C and link it with the SQLite runtime library, thus letting you call the function from SQL. I’ve just starting with it, so really don’t know how well it works or easy it is to do, but thought you might be interested.
No, just no. SQLite. “Lite,” get it? A great little database that can be spun up in a single file with no server required. SQLite is everywhere (behind the scenes) on the Mac and does a great job. There are so many other paid and free database tools available that would do what you want. SQLite should be left to do what it does best – provide a lightweight backing store for applications.
Many of which presume that one is working in a networked environment even if both the client and server on the same machine.
As PRODEDUREs work entirely within the scope of a database system it is not unreasonable that SQLite might also provide the feature too. No one is asking for inclusion of C/C++/Swift/Algol-68/Ada within SQLite.
This is not to challenge your view, which is exactly that of the developers, but I have looked for alternatives and not found any open source, embedded SQL databases that support procedural SQL. If you can suggest some, I’d be grateful as I’m looking for something on which to build a centralised data repository for a set of related mobile apps that have to run offline (ie no database server such as MySQL or Postgres).
Many developers just want triggers or custom functions when they say stored procedures. Those are available natively or via a project like Define in the SQLean extension list. Those who want procedures for performance are looking at custom extensions (where CG/SQL appears to help.)
Those who want procedures for network/process security in a multi-client situation need to just trust their client libraries/applications because that’s what they signed up for when they chose a database that’s just a file on the server.
