Is it worth it to get the add-in that allows me to use Python istead of VBA?

[Welshgasman]

Not suitable as a database server at all.

Served us well in work, and does so in many other companies I would expect, if all you need is LAN access.

[Jennifer Murphy]

I just asked ChatGPT to summarize the various database options. Here's its reply. I'd appreciate any comments. If anyone has any additional questions or comments, I can post them.

https://chatgpt.com/share/3eaa7f13-8...4-f5375d6797ea

[Welshgasman]

But Access is flakey and really only supports VBA, an archaic and error-prone programing language, for macros and user-defined functions (UDFs)

Well that is BS.

[madpiet]

So how much data are you talking about? Why do you need a "big" database engine? How many tables? How many records in each?

What do you need the database to actually do? Seems like you haven't addressed that. Without it, it's a bit like saying "I need to buy a truck" without ever mentioning if you're going to tow anything or transport anything. Without knowing that, it's impossible to say which option is more/less suitable. In my opinion, reading what ChatGPT won't do much for you until you can answer the questions about what your real data needs and performance are.

[Welshgasman]

ChatGPT highlights the old adage.
Garbage in, garbage out.

Unless you are very specific ( and you were not) then the answer will be very generic if not just plain rubbish.

[Jennifer Murphy]

I took the 3 ratings that ChatGPT reported, found 5 more, and ran them through a normalizing macro that I wrote for Excel some time ago. Here are the results:



I hope the Access sycophants here are not too distressed by it's very low rating by these reviewers. Only 3 of the 8 reviews even bothered to include it.

These 8 ratings have a range of selection and rating criteria, so I don't think these results are definitive. As been pointed out here, a lot depends on what the database will be used for. But it gives me a good place to start. I will dive deeper into each rating source. If I think some are more reliable than others, I can change the weights.

Oh, and to answer the question of what I want it for, it varies. My immediate goal is to just become familiar enough with at least one database system so that I can create simple databases for my personal use such as rating databases for things like restaurants and various types of products.

My next goal is a database that can be used by our neighborhood association. From time to time, products like books or record albums are collected by one neighbor from those who no longer want them and then offered to those who do. I'd like to create a database that contains all of the donations and then make it available to those who want them. I would allocate a column to each taker. They could "go online" and access the database, browse the available items, and indicate their choices in the column provided for them. This would be an "online" database that s mostly read only (the donated items), but with one read/write column for each taker. The donations can easily get to 1,000 or more. We have about 700 people in our neighborhood association, so maybe 20-30 might be interested in one product. I doubt that all of them would be "online" at the dame time, but something like 4-5 could.

I am sure that I will come up with other applications. At tennis awhile back, we were talking about what wins matches -- is it winning shots or the lack of losing shots? I developed some formulas that take as input the probabilities of getting each shot in play and generate the odds of winning the point, the game, the set, and the match. I might like to make that available online were players could enter some probabilities and see some results.

[CJ_London]

Access is not a database. It is a RAD tool (Rapid Application Development) which can connect to pretty much any database you care to find. It happens to come with the ACE database engine which many users find is sufficient for their needs.

My next goal is a database that can be used by our neighborhood association.

That rules out Access as a front end unless all users have it installed. There is the free runtime version but users would still be limited to using a windows device. For Apple and Linux devices you can install a windows VM but I very much doubt your neighborhood would want to go there - they will want to use a browser.

My immediate goal is to just become familiar enough with at least one database system

given your expected userbase will be connecting online, Access is not for you.

I would allocate a column to each taker.

Learn about database normalisation which is a standard across all databases, you do not allocate a column for each taker - that is Excel thinking.

For the backend (the database) choose one - what is popular today may not be popular tomorrow. Long term 'standards' have been set by Sql Server and mySQL. Postgre is popular at the moment but in a year or two's time it could be supplanted by something else. You just need to find one that can be hosted on the web with the appropriate security functionality you require.

To create the front end, you will need to use web development tools of which Python is one of many.

The benefit of Access is the front end (the forms and reports) and the development time, not the back end. But the front end does not work on the web (although there are ways organisational users (including Apple and Linux users) can connect via the web, primarily through hosting on terminal server or citrix. But it comes with a cost. There is also VPN for windows users.

Chances are, if you have a neighborhood association, they will have a hosted website. Talk to the host, find out what databases they can mount for you (mySQL is a common one) and the cost of hosting. They may also provide a development environment for the database and another for developing your web pages so your users can access and edit the data.

I hate VBA. It's an ancient, error-prone excuse for a programming language. I don't know Python, but from what I've read it's an excellent, modern programming language. If it could be used instead of VBA in Access, Excel, or Word, I would think productivity would be increased.

I very much doubt it - typically you can develop something in Access in a tenth of the time it takes to develop in other languages - which doesn't sound like a productivity improvement to me. But then your stated requirement for online precludes using Access.

For your home projects, Access will be fine for your purposes. You might want to put an Excel file on One Drive and share the drive with your neighborhood for the library project (Access does not work with One Drive). The same applies to your tennis project.

[Jennifer Murphy]

Learn about database normalisation which is a standard across all databases, you do not allocate a column for each taker - that is Excel thinking.

I understand the concepts of normalization, primary keys, foreign keys, junction tables, etc. When I said "allocate a column" I was referring to what the user would see. It would look like a spreadsheet. In the actual database, each taker's choices would be a field (like a column) in the Albums table or a connected table.