database selection

[WinterDeere]

MS Access front end / User Interface, SQL backend. MS Office products are bloated, slow, and get quirky when asked to do too much.
There are always users that can't be trusted so the Access user interface protects the backend data while SQL does the heavy lifting.

Yep. Understood. But other than the "slow" part, the rest all sounds like it's geared just for me.

Oh, and I'm running this mostly on huge multi-socketed PC's, none fewer than 32 physical cores, with NVMe drive arrays. Point being, "slow" may not affect me as much as the average user, assuming Access (like recent Excel) can actually make use of multithreading.

 

[WinterDeere]

Perfect example of Excel breaking a pefectly working function, for no good reason:

=INDEX('[ITEM MASTER.xlsx]Items'!Description,
MATCH(1,([@Item]='ITEM MASTER.xlsx'!Item)*([@[Rev.]]='ITEM MASTER.xlsx'!Rev),0))

Obviously calling named range data up from file ITEM MASTER.xlsx. But for some unknown reason, excel changed '[ITEM MASTER.xlsx]Items'!Item to 'ITEM MASTER.xlsx'!Item, and '[ITEM MASTER.xlsx]Items'!Rev to 'ITEM MASTER.xlsx'!Rev. Now the formula doesn't work.

Sometimes, it will allow me to change it back to get it working again, other times it will immediately change my correction back to the wrong format shown above.

Programs like Excel shouldn't even have formula auto-correction. It's not as if any AI can know better than the user, what they're trying to achieve with a formula. And if they insist on including that, then there's never an instance when it should over-ride a user correction... but here we are.

 

[square1]

Nothing as maddening as a function / script / code that has performed flawlessly for years breaking due to an "Update" (we no longer refer to them as Upgrades!).

20ish years ago MS disallowed as symbol in Access (can't remember which one now) commonly used in VB scripts. Broke a crap-ton and a half of modules. Scrambled for days editing scripts in DBs built by my predecessor to get things working again after identifying the issue.

 

[BrendanDaniels]

I had a similar situation and ended up building a simple Excel file first to sort things out before moving to a database. It gave me a clearer idea of what data I actually needed. I used a bunch of excel formulas examples I found online to calculate totals, filter lists, and even auto-fill entries based on dropdowns. It helped me figure out the structure before putting time into any software setup.