November 19, 2019 at 3:17 pm
Just commenting to second what everyone else has said. I also got started in actual database work using Access. (Apart from the career-change courses I subsequently took in Oracle.) Access "just works," and just well enough to be very hard to dislodge from a lot of workplaces.
I have long been annoyed with the small but critical differences in Access SQL (* vs % for wildcards, for example), but even I never was able to quickly enough write SQL Server-only replacements to justify my discontent over those differences. Then, of course, my successor wrote several Access front-end / SQL back-end apps for clients that they don't want to part with.
I read that article before Steve posted about it, but judging from his editorial and from the comments here, it is somewhat reassuring that I haven't been the only one having to confront the Access "it just works" experience.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 19, 2019 at 3:34 pm
Um, what? 🙂
In terms of SQL language (as opposed to flow control and other non-SQL elements) Jet supports nearly as rich an array of language elements as SQL Server does.
This includes all the basic SQL clauses, joins, etc. In addition you can include VBA functions in the select itself. And VBA functions in SQL are *fast*, unlike functions in T/SQL.
In addition to SQL you also have the ability to directly access data via one of the ODBC methods or DAO or ADO...
You can even create pass-through SQL to talk to SQL Server or other databases. So I'm not sure why you say Access's SQL language is limited.
The only element that's really missing from Jet is stored procedures. Which I admit make security so much simpler.
T/SQL, on the other hand is glacially slow for anything that isn't DML or DDL. Which is why Jeff Moden always rants on RBAR. 🙂
I like jeffs approach to RBAR - we all have our pet hates - mine is functions and cursors.
but as for ODBC, DAO and ADO - sql can do that - granted it's linked servers , which are the spawn of satan .. but on the plus side sql 2019 now has improved Polybase to include MongoDb which for me is a big Win
SQL is designed for DML and DDL - if you are using it for anything else then find a new tool - please just don't let it be an entity framework
MVDBA
November 19, 2019 at 3:44 pm
I started using Access in 2008, as sort of a side project to my real job (Railway Safety Engineer at the time). I didn't have any knowledge of SQL at the time nor much knowledge of programming language, unless you want to count making a few bat files and some matlab scripts.
Admittedly the first database I designed was not pretty, and I would probably deny having ever created it 🙂 But Access was sooo easy to just pick up, start creating stuff, and most importantly "Learn as you go". All you need is contained within a single file (If your just getting started and haven't split the file into a frontend and a data backend). In contrast when I tried to setup my first web program I had to install and setup apache, mysql and understand a bit of php as well as html to even make a single "Hello world" appear on screen.And I was terrified at the time as to whether I had just opened my server up for all sorts of security bugs. I'm not saying that Access is more secure, merely that the basic security model is so much easier to understand.
A year later I created a Requirements Managements Tool, supporting about 40 users, with about 25 concurrent users (The network was excellent there, which helped). This tool is still in use today, and has been controlling several major infrastructure projects. We had a client who went from DOORS to our system, because quote "It just works".
Fast forward to today, I develop Access applications full time. 95% of our backends are SQL server or SQL server Express. For really small projects, or projects we inherit we might still do Access backends, but we do prefer SQL server as the backend, especially when security is involved. Our biggest project (with SQL backend) so far has about 1000 users (concurrency peaked at 100 users). So for sure Access is still super relevant for us.
One of the main issues I've seen from IT "Pros" is that in their mind, they cannot separate the Access Development from the Jet/Ace Backend. They think its one and and the same, and thus the reputation of Access is "tarnished" by the rep of Jet.
November 19, 2019 at 3:55 pm
smiley coder, please don't tell me you used MIMS or any of my access databases for track inspections 🙂
MVDBA
November 19, 2019 at 4:13 pm
I have never done any development on Access, but I have sure been called to consult for a lot of Access applications. On many occasions, I have had the unfortunate task of informing someone that their Access app could never be fixed. The forms and VBA code they had so lovingly built was a snarl of spaghetti, impossible to enhance. I basically agree with all of the positive comments here about certain uses of Access. However, I wish the VBA editor in Access had a warning at the top: Access is an individual productivity tool that runs on a single desktop. Do not develop enterprise applications here.
November 19, 2019 at 4:17 pm
I have never done any development on Access, but I have sure been called to consult for a lot of Access applications. On many occasions, I have had the unfortunate task of informing someone that their Access app could never be fixed. The forms and VBA code they had so lovingly built was a snarl of spaghetti, impossible to enhance. I basically agree with all of the positive comments here about certain uses of Access. However, I wish the VBA editor in Access had a warning at the top: Access is an individual productivity tool that runs on a single desktop. Do not develop enterprise applications here.
I fully agree. My feelings about Access are not so much positive as they are accepting with resignation that Access is very difficult to "kill off." I'm sure it is possible to write well-structured and maintainable Access VBA code (I have done so here and there, much to my surprised satisfaction), but on the whole it is definitely prone to spaghetti code as the default outcome for most apps. And that is not even counting the DLLs and other OS items that need to be correct and up to date if one is to avoid running into cryptic errors trying to use an Access DB or project that had to be moved to another computer.
But it is sobering how much of that stuff people put up with rather than trying to rip off the Band-Aid and use another platform.
-- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 19, 2019 at 4:23 pm
I have never done any development on Access, but I have sure been called to consult for a lot of Access applications. On many occasions, I have had the unfortunate task of informing someone that their Access app could never be fixed. The forms and VBA code they had so lovingly built was a snarl of spaghetti, impossible to enhance. I basically agree with all of the positive comments here about certain uses of Access. However, I wish the VBA editor in Access had a warning at the top: Access is an individual productivity tool that runs on a single desktop. Do not develop enterprise applications here.
4 words
On error resume next
MVDBA
November 19, 2019 at 4:32 pm
I have never done any development on Access, but I have sure been called to consult for a lot of Access applications. On many occasions, I have had the unfortunate task of informing someone that their Access app could never be fixed. The forms and VBA code they had so lovingly built was a snarl of spaghetti, impossible to enhance. I basically agree with all of the positive comments here about certain uses of Access. However, I wish the VBA editor in Access had a warning at the top: Access is an individual productivity tool that runs on a single desktop. Do not develop enterprise applications here.
Ah. 🙂
That explains it. Professionally developed VBA code is no different than professionally developed code in any other language. Spaghetti code is anathema in any language. VBA allows all the same techniques (error checking, argument validation, etc.) as C# or anything else. Code behind forms is just another name for event driven programming.
Which means, of course, someone who isn't fluent in VBA and professional development is going to create an unholy mess with all the common sins. Since you personally never developed in VBA you won't have the background to rewrite someone else's amateur code into professional code--and I doubt the client would be willing to pay enough for you to do that even if you did! (laughing)
I agree Access wouldn't scale to Enterprise level programs (i.e. hundreds of concurrent users), my experience is it tops out around 25...and hits the wall at 30. But within those limits (for Jet, not SQL backends) Access can't be touched for speed of development. You just need to be fluent in VBA and be a professional developer to wring the performance and maintainability from it.
Oh, and not start from someone else's amateur code base! 🙂 That way lies madness.
November 19, 2019 at 5:36 pm
I have to agree with both sides to some extent. I was in a user group with a guy, who as far as I know still makes really good money from Access. Both developing new applications and fixing horribly mangled ones. He claimed to have a few techniques to allow a fairly large group of users to connect to Access database without issues. The other side is walking into a new company who's main bread and butter application was a horrific pile of garbage with the worst table designs I've ever seen. It made him a LOT of money coming in to fix and extend the beast until the company finally put together an in-house software team who looked at it in absolute horror. It took about a year to write a C# / SQL Server replacement but it was well worth it.
As with anything, skilled, knowledgeable people will craft solid applications. Access's gift and curse was that almost anyone could create an application and some that were only supposed to be POCs because production apps.
November 19, 2019 at 6:02 pm
Microsoft Access is the bane of our existence. We have hundreds of Access databases and apps in production today. Allowing for duplication, since its likely more than 1 person uses the same Access db/app I estimate we have about 200 unique Access dbs/apps.
They've been around far longer than I've been in this job. I think the reasons we have so many is the ease of setting one up, the fact that Access is a part of our Office licenses and the speed in which someone and whip together an app and database.
Kindest Regards, Rod Connect with me on LinkedIn.
November 19, 2019 at 6:35 pm
We use 1099 software whose back end is dBase. Access/Jet aren't going anywhere.
Also, another poster complained about the ease of breaking into the database. That's a feature, not a bug. Having dealt with embedded systems that use Jet, being able to join their data with SQL Server's is a godsend.
November 19, 2019 at 6:38 pm
But T/SQL *sucks* as a language, and having to learn two distinct languages to do a single project is insane.
I think access is a spiffy tool, but at its heart, its vba, which had it been suitable for enterprise level stuff, vb6 would never have been ditched in favor of dot net.
November 19, 2019 at 6:49 pm
Microsoft Access is the bane of our existence. We have hundreds of Access databases and apps in production today. Allowing for duplication, since its likely more than 1 person uses the same Access db/app I estimate we have about 200 unique Access dbs/apps.
They've been around far longer than I've been in this job. I think the reasons we have so many is the ease of setting one up, the fact that Access is a part of our Office licenses and the speed in which someone and whip together an app and database.
@rod: Let me ask a question: If access wasn't around to solve those business needs, what would happen instead? I mean we can all dream of a world where only professionals develop software, and ideally only after getting 10 years of experience so they know what they are doing. But the issue is that most of these apps solve a business need, for which there was either no budget to have it developed, or IT was too busy to assist.
So if we rule out Access, and rule out professional software, that still leaves us with a business need that has to solved.
Then people grab the most "database" like thing they can get their hands on, which is *shudders* Excel.
Now I love Excel for reporting. But I've seen much much worse Excel messes than I've ever seen Access messes. Excel becomes a hodgepodge of hundreds (or thousands!!) of files that link together in a absolute mess, with references buried deep within cells, so that the whole sheet breaks when someone moves a file. At least with Access you most of the time have a single data backend to look at, and a single frontend to look at.
I would say that in an ideal world, IT would have a resource that could assist their users with setting up proper custom systems, whether it be Excel based, Access based, or Power Apps. This could solve the business needs, while trying to enforce some good programming rules while developing.
November 19, 2019 at 8:25 pm
I think at least part of the problem with understanding the role of "Access as a database" in most organizations is that it "Access", in fact, is not a database. Let me explain.
Access is a set of development tools from which you can build a relational database.
Access is shipped with its own database engine, called ACE, but it also provides tools for interface design, for reporting, and for data consolidation. In that single set of tools, anyone can create tables, write queries, design forms and reports, write basic code, even connect to enterprise level data stores (and that's probably what gives some DBA's night sweats, right?).
For the last few years of my career, I mostly built or maintained or "rescued" Access front ends (i.e. interfaces) with SQL Server or SQL Azure back ends .
I can't think of the last time I created or supported a production database, in fact, which was purely Access/Access. I did encounter maybe one or two minor projects involving modernizing an older "Access database" that had been in continuous use for two decades or more, though.
All of that said, I've often claimed that one of the big advantages of Access is that anyone can start the application and create a "database". I've also had to admit that one of the big shortcomings of Access is that anyone can start the Access application and create a "database" with it. And they do.
For better or worse, Access democratized database development for a huge audience and that's one important reason it'll never go away entirely.
November 19, 2019 at 8:36 pm
I'd put MS Access in the same general database classification as Excel, FileMaker, FoxPro, or even SQLite. It does "just work" - but only within a narrow scope of use cases (no more than a handful of concurrent users, small databases, centralized application hosting not a requirements, etc.).
The modern "it just works" replacement for MS Access would probably have a serverless hosted database on the backend (something like Azure CosmosDB or one of the Amazon offerings) and then a web based IDE with data model templates and a simple form designer (ie: Google Forms or the web version of MS Office).
Maybe something along these lines:
https://www.lifewire.com/best-free-online-database-creators-3486264
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 70 total)
You must be logged in to reply to this topic. Login to reply