|
I've been poking around YouTube, trying to find whether SQL has Access's query design interface/tool/whatever. Designing queries in Access seems MUCH easier than SQL's offering, but maybe I'm not using the right search terms.
|
|
|
|
|
David O'Neil wrote: Designing queries in Access seems MUCH easier than SQL's offering
It irritates me every time I use the query designer in SSMS...take the time to arrange tables/objects in the designer, run the query, return to the designer and find that everything is back to being stacked vertical...it can't even remember the last window size ffs!  At least the Access designer remembers the layout. Additionally, because I need the same queries to work in both Access and SQL Server, I need to design in Access as table/view joins designed in Access will always work in SQL Server, but not the other way around. Oh yeah, and I also prefer Access < 2007. (.mdb)
I've ranted about this before, but you'd think that the SSMS team would put a little more effort into the query designer. Every update nag I get, I wonder 'have they fixed it?' and every update I'm dissappointed! 
"Go forth into the source" - Neal Morse
"Hope is contagious"
|
|
|
|
|
|
obermd wrote: Access is the hidden gem in Office
And was also the most misused tool. Entire business logic applications written in VBA / VB6 with a self contained .mdb prone to get farked every week.
GCS d--(d-) s-/++ a C++++ U+++ P- L+@ E-- W++ N+ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t+ 5? X R+++ tv-- b+(+++) DI+++ D++ G e++ h--- r+++ y+++* Weapons extension: ma- k++ F+2 X
|
|
|
|
|
David O'Neil wrote: Everyone seems to sh*t on Office and VBA, but when used correctly it is TREMENDOUSLY POWERFUL! A small business could be ran with it pretty easily
My brother works in a multinational corporation (I think they are the fourth biggest in their field) and most of their internal monthly accounting is made in Excel, assisted by VBA macros for the more complex things (like reading the detailed results of the month and filling out the official accounting software mandated by the different governments). But you haven't heard that from me 
A long time ago (1995, I think), when I was starting to program, I built a DB with GUI in Access for a library. At that time Access allowed to build GUIs, do not know if it still does. On delivery, the guy who hired me commented "This is all in Access? It looks great."
|
|
|
|
|
ElectronProgrammer wrote: At that time Access allowed to build GUIs, do not know if it still does.
2010 does, and I bet the newer versions also do. Haven't played with them.
|
|
|
|
|
Even 2021 does support the GUIs! I have Access 'apps' that I started in the 1990s and they have had minor tweaks (e.g. going from Access 2 to Access 97 was tricky as they changed the default Recordset class, some error nos altered) but apart from that, they work fine. The default 'style' for forms change between releases, but old forms still work without modification.
I have used Access in multi-user mode; admittedly, the multi-users were web users and the web app was the only 'real' user using the database (apart from a maintenance user). That ran from 1997 to well into the 2000s.
|
|
|
|
|
jsc42 wrote: Even 2021 does support the GUIs! I have Access 'apps' that I started in the 1990s and they have had minor tweaks
Same here -- I have several databases started in Access V2 which have migrated through every version up to the current one. VBA has been expanded but most of the original code works.
Beyond Access, my MS Word Normal.dotx contains macros that have run, unchanged, since 1995. This reinforces how bad the constant language churn is for the front line languages.
|
|
|
|
|
Access is a tool. Like any other tool, it has its purpose. Use it for the wrong purpose and one is destined for failure.
The reason most poo-poo on Access is because for most, it is not the correct tool for the job, SQL Server, Oracle, etc. are the correct tools for the job.
and yes, if you have a table that needs more than 255 columns, then you have much bigger problems on your hands than Access.
|
|
|
|
|
I used Access since it first came out. I still do at times. I switched to SQK Server after it came out, mostly because of the 32767 limit.
Access is a tool. Like any other tool, it has its purpose. Use it for the wrong purpose and one is destined for failure.
I agree with you one hundred percent! the same can be said for, and is equally true about VB in ALL of it's forms from VB6 to the latest version of VB.net. The same can be said for other languages, for that matter. I'm looking at you, Griff. Just because you like one language better than others, does not give you the right to rundown the others.
ED
|
|
|
|
|
Slow Eddie wrote: I agree with you one hundred percent! the same can be said for, and is equally true about VB in ALL of it's forms from VB6 to the latest version of VB.net
Visual Basic was a revolutionary language that completely changed the programming landscape. I recall trying to develop GUIs prior to VB ... sheesh!
Look at the "popular languages" lists -- regardless of the ranking criteria, Classic VB is often still in the top 20. Personally, this surprises me, but it's oddly true.
Folks complain about the bad applications written in VB ... I've had to deal with far more crap code in C, C++, C#, and Java. As a mentor said, it's a poor craftsman who blames the tools for his lack of skill.
|
|
|
|
|
I too love Access, for the right sort of jobs.
However the problem that I often see is when a non-programmer builds a simple solution with Access. At first, no big deal. But then over time, new functionality gets patched on, Now fast forward a few years, and you have this behemoth of an app, built with loads of spaghetti-style code, and no one really knows how it works anymore. Now they turn to the professional developers to fix things in it or add yet more functionality, and we are left with this Ball of Mud to sort out.
One company I worked with had one such Access DB, with multiple forms and reports, that had become crucial to their business over the course of almost 20 years of patching. They even had one-off versions with for one or two people with different formulas for estimations of pricing. And full of these little bugs that they wanted fixed. They thought upgrading to a new version of Access (they were still on Office 2003) would fix their problems....
Long story short - like any tool, you need to use it properly. Programming in Access is like programming in any other language, and needs some forethought for anything beyond trivial solutions.
|
|
|
|
|
Andreas Mertens wrote: They even had one-off versions with for one or two people with different formulas for estimations of pricing
Ungh!!! That's Rough, Buddy!!!
|
|
|
|
|
It was. It would be nice if you could easily put all of the code and forms into git or some other version control system
|
|
|
|
|
Or have some more tables that help control when to display the estimates. Copying the MDB to change a formula is probably not the best way.
|
|
|
|
|
|
I once wrote an entire management system for a school department in Access in one week while I had a severe flu that only gave me about four hours of coherence per day.
I wasted at least one whole day on a series of cascading drop downs for the most complicated screen. (I will blame that delay on delirium)
If you like Access, check out OutSystems. I think of it as a cloud hosted, multiuser super-Access.
Similar speed of development for simple CRUD apps.
|
|
|
|
|
Further yay! Got Outlook talking to Access, so can now log sent (and in the future, received) emails as they are sent and received! Can even do basic templates for email lists, and fill in the addresses and salutations from Access as well, so it goes both ways!
The only issue is that Outlook locks up while sending emails, because it doesn't have a true method to trigger timer events so the main thread pretty much locks up.
|
|
|
|
|
Access is like a utility car/van compared to the massive semitruck of SQL Server.
it has all the needs most people individuals or small groups of people will need.
not everyone needs dedicated server for connecting 3 tables
|
|
|
|
|
Sorry, I lost interest when you said "This is not sarcasm ..."
Nothing succeeds like a budgie without teeth.
|
|
|
|
|
You're probably better off using the free SQL Server express than Access. Also, Power BI is like Excel but with better and easier visualizations. With that said, MSN.com is really bad about censoring certain views on particular topics.
|
|
|
|
|
Can you interact with Word, Excel, and Outlook as easily with Express? And, as I've asked elsewhere, can you create queries in Express as easily as you can with Access? The query YouTube vids I've seen indicate that SQL Express complex queries are at least an order of magnitude more difficult to design than they are in Access. And, as kmoorevs indicated, it doesn't keep the view the way you had it when you reopen the designer. Access looks like a work of art compared to that.
|
|
|
|
|
I'm not disputing the power of Access, VBA et al.
For a given task or at least limited domain with a good plan it's powerful. The example you cited is the kind of thing it is most valuable for.
The problem I've encountered a number of times is really a chain of issues that happens predictably, and Access gets the most heat from this situation.
This is when powerful tools are readily available to users who happen to lack the training of a good database design, or application experience.
In all cases these were smart people, who were in a hurry and didn't recognize the complexity of the tasks they were undertaking.
The resulting issues blend and this pattern emerges in business and technical applications alike:
> User with a need creates an application on [insert toolset here] etc on the desktop
> The application fills the need nicely at first
> Sometimes other users with similar needs recognize the power of the solution and start to use it too
> All happens quietly without review or consideration from experienced IT staff or at least a knowledgeable Database expert.
> Everyone is happy for a while (months, years...) because it just works.
> Later we learn that the application is said to have run "really fast" when it was new.
> Over time users forgot that the application was created entirely by "Dave in Engineering" or "Lucy in Underwriting"
> Crisis happens - person/designer leaves | space runs out | computer fails
> No one knew that person/designer had to [Insert task here] (e.g. "clean out the tables every couple of months to keep it running", "add new configurations")
> No documentation, no source control, no backups
> "Hey Fix This!" and our response is "What IS this? How long have you been using this?"
I'm not exhagerating, and this pattern is my only real beef with Access.
The price is secondary, but I'd say that if any company is willing to pay the cost of an Access license they should also put money into basic database design training first. Of course then they may well realize that there are other great database options available.
Most problems in any domain are rooted in a failure to communicate.
<philibuster_off>
Cheers!
|
|
|
|
|
This is exactly what I see happening to many of the no-code/low-code solutions being touted these days.
|
|
|
|
|
For over 20 years, my company has offered a choice on database options, Access or SQL Server for most of our desktop apps. For either smaller (< 20 sites) or single-user customers, Access makes perfect sense. While there are some slight differences in syntax, 99% of the same sql queries will work in either one. A nice thing about this (and something we do all the time) is that while I'm on a remote/support call I can downsize a customer's sql database, upload it, download it on my end, hook it up and troubleshoot right away.
I also use Access for personal projects where portability is required...databases reside on an external drive.
"Go forth into the source" - Neal Morse
"Hope is contagious"
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
