This has been my experience. I taught myself rudimentary Access skills during a slow period at one job (with approval) using only Access itself (no textbooks). IMHO, the worst problem with Microsoft Access isn’t the “Access,” it’s the “Microsoft.” All MS applications have obtuse help; it seems to be a corporate philosophy. Maybe they save money on technical writing? Maybe they want to sell training courses, so they don’t want you to be able to figure things out on your own? The help is often maddeningly simple – it reads like a parody of help text: the entire help entry for “How do I do X” is typically “click the button that says X,” with no explanation of context, other options, or why you might want to use other similar functions instead.
But there’s another part of it that bugs me – Microsoft products always seem to invent a new word or term for every common function. It’s like they think they can copyright the terms if they use different terms. So looking up anything in the help index of a Microsoft program is difficult if you don’t already know the term of choice. Many times I’ll be trying to find, to make up an example, “How do I attach x to y” and I’ll try “attach,” “append,” “link,” “add in,” “connect,” and so on, with no results at all, not even a suggestion like “to attach an item, see blahblahblah.” Finally I’ll blunder across some third-party website that reveals the Microsoft term for this is “e-partnering” or something equally bizarre. It’s like trying to learn a new language whose vocabulary is closely guarded by hostile native speakers.
So I eventually got Access to work for fairly simple database queries, but every time I tried to learn something new, I was blocked and baffled at the start until I blundered across the Microsoftese words for what I was trying to do – only then could I start actually learning how to do it.
Anyone else ever hear this mantra: “Someday everyone is going to stop using spreadsheets and start using Access”. My wife was told this by her instructor at an Excel class. Does this even remotely make sense? Spreadsheets are great, database programs are great (just that Access sucks ass in my opinion, I’m an Infomaker kind of guy). To think that all spreadsheet users are just going to pack it in and do everything in Access is nuts.
I keep hearing the exact opposite - I talk about doing some really trivial data crunching job in Access and someone says “Oh, can’t you just do that in Excel by [some godawful arcane contorted longwinded malarkey that still relies on a human scanning and manipulating the results]?”
That has to be one of the more stupid things I’ve heard today. As other’s have said, spreadsheets do what spreadsheets do, and databases do what databases do, although there *is *some overlap. I love working with both programs.
A coworker has a spreadsheet with thousands of lines of non-normalized data, and she’s using Excel to find particular records by multiple criteria. Her spreadsheet is a horrorshow of COUNTIF’s and DSUM’s. I’ve been begging her to give me about 4 hours to create an application in Access. That’s what databases are best at: finding things with multiple criteria.
ETA: Access does have an auto-join option that you can turn on if you want. If you drag tables into the query window, Access will assume that the same column names that end in “ID” or “Num” or whatever imply a join and add it. It’ll even try to determine which is the master and which is the child. This is really handy sometimes when you design your tables with this in mind. You don’t have to turn it on, though.
That just tells you which values appear more than once in SomeField. If you want to deduplicate the table, it’s easier still - just do the grouping query without the count.
It’s really weird because there are other things they do where it’s clear they were thinking from the developers perspective. But when it comes to help it seems like the people in charge of that have zero concept of what a developer may be looking for to understand that particular item.
Even today, if I am googling for an explanation of something, I always avoid any page from microsoft because I am almost always forced to continue looking for a more complete explanation.
Wow. I’ve made a bucket of money off Access. I’ve taught courses in how to use it.
And therein lies the problem. People don’t know how to use it properly. It’s not just a matter of dragging fields around on a report. It is a full-blown DBMS and some amount of intelligent design is required in creating the table structures.
I’ll second this one. We had a buggy ODBC client upgrade, and since I’m the back-end database guy here, I found out just how much ‘production’ work is sitting around on peoples desktops in MS Access. A bit scary, actually. The smart ones at least put them out on the network share so there is some sort of backup, but that is hardly universal.
I learned relational databases from Access, so I understand that it can be beneficial to entry level users, but now that I’ve seen what it does do the databases it runs against, I would never use it for anything that has any sort of performance requirement.
The key word here is “should” and even that isn’t true in every situation. My concern was that it sounded a bit like his database system automatically linked them solely because they had the same name (might not have been true, as it was a bit vague), which is a pretty scary thought. If two tables are related, then explicitly define that relationship, period. Remember that ERP systems are not the same thing as database systems, even if they have one as their backend, so what might be standard in one world isn’t necessarily true in the other.
While I completely agree with you regarding help on many of their products, it’s not universal. Their SQL Server help (BOL) is one of the more comprehensive pieces of goodness I’ve ever seen. For example, the entry for CAST AND CONVERT, which is used to change datatypes and handle odd date formatting.
What I posted applies to databases, as well as the entire environment surrounding any system.
Having inconsistency in named entities creates problems.
And his reason for saying “never” use them is not due to crappy DB design, it’s due to ambiguities that can exist even in a good design - which is a valid point.
But saying “never” use it is too strong - I see tons of situations daily in which it can be used safely - like everything, use it when appropriate.
It doesn’t even have to be inconsistencies. I’ve consulted on this stuff for decades, and I’ve seen more columns named things like “CreateDate” and “ModifiedDate” than I could possibly remember. Want to have the system automatically add those columns to the join you have between your PK and FK? Let me know how that works out for you.
Of course it CAN be used safely. If I personally handled every aspect of every database in our company, and if I signed a binding contract to remain an employee there (and to not bite the dust) until any such databases were retired, then I might consider using them. Okay, not even then would I do it, but I could probably be semi-safe under those completely unrealistic circumstances, as long as I never forgot that they were used even when rushing to get something out under time constraints.
I have to (consulted for decades) and I don’t disagree with the example you just provided.
But, I have used this type of feature numerous times in different environments over the last 30 years when I’m dealing with a well constructed DB, or even a subset of the tables in which it’s safe, and yes, it still requires review.
Situations where it’s a useful feature:
If the join is created in some development environment/utility that shows the automatically created links so they can be reviewed
Or if it’s a natural join and the sql is not going into production where it can be impacted by later changes
Probably 80% of the sql I write falls into category #2 (short life span, minutes to weeks) and here it’s just a matter of properly understanding the database and the feature.
I can’t add much to the discussion right now, but as an undergrad enrolled in a database course at the moment…I like SQL Developer faaaar more than I liked Access, even if I’m writing the usual SQL in SQL developer.
Access just makes me want to rip my hair out based upon its interface.
I’ll have fun reading through this thread and learning from it later when I have the time; I’m really glad it’s been started.
I agree if it’s well constructed. My concern (and I’m a fan of defensive programming) is that well-constructed DBs often become less so over time.
That’s probably the difference and in fact, for ad hoc queries, I’d have no problem with that usage. Of course, ad hoc queries often turn into production queries pretty quickly. I typically manage teams that work primarily in the BI stack, where the lifespan is sometimes measured in decades, yet changes occur frequently, as new source data pops up all of the time. Hell, my current company just spend a few hundred million on a new company, which means that we’ll have massive additions to our warehouse in the near future. Short of micromanaging and poring over every line of code anyone on the team (and it’s a pretty large team, so that’s a lot of code) writes, I’m going to stick with a defensive foundation. It’s the same reason we typically put an abstraction layer (view) on top of our tables, even if it simply returns every column in a single table. Things change, sometimes frequently, and I don’t want breakages to be a common occurrence.
When you say “SQL Developer”, are you referring to the free Oracle front end? If so, other than it being quite a bit slower than many of the pay-to-play options, I agree that it’s pretty decent.
If you find this stuff fun (I do), I’d recommend you learn both Oracle and SQL Server. Sounds like you have Oracle covered, but you can get a fully functioning Enterprise Edition of SQL Server 2012 (this is the one with all of the bells and whistles, including a rather large BI stack), for about $60. It’s called the Developer Edition, but other than the fact that you can’t legally use it for production work, it’s fully functional. In other words, you can build and test things all day long, you just can’t use it as a backend for your finished commercial product.
Oracle has many great things going for it, but SQL Server does as well, and it comes with a lot more playthings in the core package. Knowing both will serve you well in your future, although you’re unlikely to be a true expert in both, as there’s just too much to keep up with. If you get really good, give me a shout. I might be hiring.
I’ve tried repeatedly to teach myself Access and even now taken a couple of pro-taught classes on it, and I still run back to my beloved Excel. Even if it probably takes me twice as long to figure out how to do what I want to do in Excel, I know what I’m doing all along the way.
