July 17, 2014 at 12:27 pm
... there are few companies where there are enough IT developers to handle all the business needs. The best option then is to provide tools to the users for them to be more self-serving. If you don't give users the tools they desire, then they will find their own ways around IT, creating manual processes, poor data stores that aren't designed or backed up properly, and creating more problems in the long term. That's how shadow IT groups get started.
In my experience, the shadow IT groups got started the moment someone outside IT bought a copy of Access.
The problem I have with Access isn't Access itself, but the way in which non-IT types use it building their own data stores and 'apps': without any knowledge of good IT practises, nor IT oversight.
July 17, 2014 at 12:32 pm
I like Access, but many of my experiences with it early on were negative. It had nothing to do with the product itself, but the fact that I seldom got to develop the app. I just came along to support it after it was already built. (Grad student trying to make a buck.)
Since it was "approachable", any idiot could come along and be a "developer". So, you had untrained people, or people that had written some BASIC (and not the visual kind) or FORTRAN (!), that decided they could design a database and put an app in front of it. The tables didn't have good typing on the columns, there we're any indices, let alone PK's, the code was spaghetti, and there was zero documentation. It was a pain, but not due to the product itself.
I realized it had a niche, and even used it for a 300 level intro to DB theory class I taught at the University of Illinois. My fellow Profs scoffed at it, as they had their students use Postgres or Ingres, or some other "real DBMS" - albeit with a command line interface!
More than half my students were non-CS majors, and would never see Postgres/Ingres again. So, I had the students use Access, with all its warts, to do a start to finish design/development project that evolved through the semester as they learned the appropriate theory. They loved it!
In summary, my objections to Access have almost always been with Access "developers" and not the product. Sure it has limitations. But at it's price point, it's been a useful tool.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 17, 2014 at 12:33 pm
GoofyGuy (7/17/2014)
... there are few companies where there are enough IT developers to handle all the business needs. The best option then is to provide tools to the users for them to be more self-serving. If you don't give users the tools they desire, then they will find their own ways around IT, creating manual processes, poor data stores that aren't designed or backed up properly, and creating more problems in the long term. That's how shadow IT groups get started.In my experience, the shadow IT groups got started the moment someone outside IT bought a copy of Access.
The problem I have with Access isn't Access itself, but the way in which non-IT types use it building their own data stores and 'apps': without any knowledge of good IT practises, nor IT oversight.
could have saved a lot of typing by waiting 5 minutes and just putting +1 on this post 😉
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 17, 2014 at 12:35 pm
Non IT oversight is a big deal. Nothing scarier than PII, PCI, HIPAA data sitting in unknown and un-monitored places.
July 17, 2014 at 12:36 pm
I also "grew up" with Access, starting with 2.0. Once I started using Oracle and SQL Server, I had no further use for it other than maintaining a home inventory. I didn't have any dislike for it or anything, but no real use for it either. The bigger databases had more powerful SQL languages, performed better and handled multiple concurrent users better.
Nowadays, I see people use access as a front end for SQL Server simply because they're familiar with it and don't want to learn SQL Server. They have these "magical queries in Access" that do everything they want, so why would they want to rewrite them in SQL Server? They just run the view, copy the data into Excel and email it to the user. What they don't see is how simple it is to query the view from a web page and serve it out to the user instead, which means the user doesn't have to bother them every time they want to see update data.
I guess the bottom line is that I still don't have disdain for Access, but I don't like the way some people use it to avoid learning the coolness that is SQL Server.
July 17, 2014 at 12:58 pm
I love it! People in IT saying positive things about Access? Never thought I would see the day!
Access is much maligned because it allows almost anyone to create a database application which can and has caused some real issues, but Access in the hands of a skilled developer is a whole different story. I'm still supporting Access applications I created years ago, one in 1996 and the other in 2001, and they are both working very well for my clients. Both have complex functionality and few users which is perfect for Access. They both recently decided they wanted to be able to run their applications from anywhere so I set them up with a Terminal Services host, installed the free run-time version of Access 2013, and now they are running them anywhere they have an Internet connection using RemoteApp. Sure, I could have rebuilt them in ASP.NET, but it would have been tens of thousands of dollars and wasn't the best solution.
The number of users Access can handle depends on many factors. I had a very complex Access application with a SQL Server back end with about 30 concurrent users. Twenty of them on the same local network as the server and another 10 in a remote office using Terminal Services and it worked very well. I've also had 5000 users on an Access database, but it was 5000 nurses submitting a 10 question survery via a web page over 3 weeks and 24 hours a day so concurrency wasn't an issue. Worked perfectly.
I'm currently working as a data analyst and regularly use an Access database that runs anywhere from 1 to 1.5 Gig to pull and process data from Oracle and SQL Server and it works like a charm. Sometimes I use linked tables and sometimes pass-through queries, just depends on the situation. Sure, I have to clean it up occasionally and do a compact and repair but I don't see that as an issue. I've heard one of my fellow data analysts say "I hate Access, but sometimes it's just the best way to get things done." Hmmm, did you listen to what you just said? We also have Teradata and the performance with Access really stinks so I use SQL Assistant for my Teradata work - so I'm perfectly willing to admit Access isn't always the best tool, but it's pretty darn good.
July 17, 2014 at 1:03 pm
I'm a little afraid to wade in here, since I'm an Access MVP. You might think I'm biased toward Access, but the reality is that my knowledge of Access makes me even more aware of when it's appropriate and when it's not.
A few points:
- Almost everyone has indicated that it's the person using Access, not Access itself that's so bad. I totally agree. We've seen some truly atrocious Access applications. I even wrote an article about it called "9 Signs That an Amateur Built Your Access Application". But Access can be used to build well-structured, well-behaved applications too.
In a TechEd presentation several years ago I said, "The best thing about Access is that it's included with Office. The worst thing about Access is that it's included with Office."
- One of strongest selling points for Access is low cost development. It's very tough to beat the speed of building a desktop business application in Access (when you know what you're doing, of course). I know, because our shop builds both Access and ASP.NET applications, and the cost premium for web applications is significant. Of course, a web app is often what they need, but sometimes they have requirements can be met with the cheaper Access architecture.
- We almost always use Access as a front-end to SQL Server. We're careful to not lock thousands of records or do a bunch of cursor operations. We've been very successful with this approach and have applications that support lots of internal users.
- You might want to get used to more Access in your world, because Access web apps are maturing. They use SQL Azure and SharePoint directly.
Cheers,
Armen Stein
July 17, 2014 at 1:05 pm
A very balanced, thoughtful post, Armen, thank you.
July 17, 2014 at 1:25 pm
My experiences pretty much parallel that of Andy's, and I have similar impressions.
However, to this day I hate Microsoft for doing away with the Switchboard. I could build macros to run tasks for the user, who often needed to know very little about Access. Just hit the buttons in the right order and the file would be imported, it would get appended to the appropriate places in the database, and the final button would export a fresh Excel file or printed report that the user wanted.
Now they have all the guts exposed, and I can see the look of horror when I introduce a new worker to the Access db that someone else has been running... "This is nothing like Excel" their eyes tell me.
July 17, 2014 at 1:47 pm
Yet one more who "grew up with Access".
I still use Access when prototyping front-ends for SQL server based solutions. Some of the data-related objects in Visual Studio aren't quite as intuitive as their Access counterparts so it saves me the hassle of figuring out the Visual Basic (or Visual whatever) code and concentrate on getting the back end and overall workflow right. Access also seems (to me at least) a bit more forgiving when introducing changes from the back end.
Once I get the back end squared away and stable, I go back and code up a permanent front end using Visual Studio (as I am doing right this moment). The current Access front end to one of my solutions handles 40-odd concurrent users without a hitch, but there are a lot of things I'd like to do that are much more difficult in Access.
Contrariwise, I've been dealing with a beast that was once a bunch of individual Access databases rolled into one single Access database then scaled up to SQL server as a back end with an Access front end. Yes, you can cringe now...
Needless to say, its' undergoing a total re-write.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
July 17, 2014 at 2:01 pm
I started with DB1, DB2, DB3 back in the late 80s, then progressed to Access 2.0, 97, etc. Access was a huge improvement over the DB product. We have several apps using Access as a front end to SQL, and as many have said here it's all about how you manage Access.
My problem is when a user creates an Access DB without telling IT, then deletes data and calls us to fix it. I'm sorry if you have no backup you're out of luck.
Working with Access does give some users a basic understanding of how a database works and if it gets the job done I'm okay with that. Just ignore my bumper sticker that says 'Friends don't let friends use Access'. 😛
July 17, 2014 at 2:07 pm
been a sql dba since 4.2, used to teach and access class, the change from excel to access is a complete mind set change. I used to spend 4 hours undoing the excel mind set to a relational database. If not every excel user just plows all the data into one table. Access back then was a great prototype tool because the GUI to create tables in 4.2 to 6.5 was poor. Access is still a nice query tool and for small businesses is a cheap alternative. There seems to be a severe shortage of dba's and frankly access is a good starting point for someone considering the migration who does not understand relational db's
July 17, 2014 at 2:50 pm
dBase, Foxpro, and MS Access were nice little self-contained development platforms that got the job done in an era when options for multi-user enterprise development on the PC platform where limited in terms of memory and CPU, and they and couldn't compete with mid-range IBM servers. So it made sense to push processing down to the users desktop. If you had one guy on staff who knew MS Access, then you had your database, application, and BI reporting / charting covered.
Still today, we can use FoxPro and MS Access as a front end for SQL Server, and it works OK, so long as the developer re-architects the bulk of the SQL into stored procedures an doesn't just rely on linked tables, pass-through queries, and client side cursors.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 17, 2014 at 2:53 pm
we let them use access but only with sp's that way i can limit the damage in their queries, otherwise you get some one who thinks 3 outer joins is a great idea
July 17, 2014 at 2:59 pm
I am one of the people who do not like access.
I have had too many experiences with poorly built access (and FileMaker Pro) 'applications' that I never want to have to deal with access (or FMP) again.
I can't even count the number of times I have had to put my foot down and tell a client they could not use access as a back end for a busy web site. I could have made a lot of money by building what they wanted and then fixing it later for $$$, but I would rather not hamstring myself or my clients in the first place.
Another nail in the coffin is that I spent many years in the Unix world and most of that time was spent scripting out Oracle/MySQL DB's and ETL processes.
I tend to view any kind of wysiwyg application as either a learning tool to be quickly discarded or a crutch.
Now that I am building ETL apps in SSIS, I long for the days when I could script out my ETL process and did not have to deal with the limitations of a wysiwyg app.
Yeah, I know. Get off my lawn and all that.
I am sure there are some really good access apps that have been developed, I just have not knowingly used them.
Viewing 15 posts - 16 through 30 (of 113 total)
You must be logged in to reply to this topic. Login to reply