Database Journal published an article yesterday about the Top 10 Reasons Why Access Still Rocks for Developers. After I wiped the coffee off my monitor, I had to write a rebuttal.
The following headlines are theirs, not mine, but the responses are mine.
1. The price is right.
The author says:
“Access is freely available … to everyone who has Microsoft Office Professional.”
That’s like saying fine Corinthian leather is completely free … to anyone who has a Chrysler Cordoba.
Microsoft Office Professional is not free – it’s $400! If you want free, go download SQL Server Express Edition right now. It’s completely free, period.
2. Landing pad for data.
That’s not even a sentence. You have to read through his points one by one to understand where he’s going:
“I regularly copy and paste data from Excel and Notepad directly into Access tables.”
You know what’s really funny is that I regularly copy/paste data from SQL Server into Excel! It’s all about what you’re familiar with. Excel has a heck of a strong feature as a front end, and I’d make the argument that I’d rather do a lot of my “landing pad” work in Excel, not Access. The author goes on to point out:
“While I have 10 years experience with SQL Server, I only recently learned how to use SQL Server Integration Services (formerly DTS) because Access is easy-peasy-lemon-squeezy…”
Access does have a great reputation for ease-of-use. This is the one point I’ll grant the author.
3. Plethora of books and training materials
Amazon search results reveal roughly 10,000 books for Microsoft Access and 5,000 books for Microsoft SQL Server. How many books do you need? What platform do you think has too few books available that your #3 reason to use Access is the book selection? Punch cards?
4. Vast online community to offer free help
You want to go there? Really? Okay, let’s look at the Twitter user base on WeFollow for a few tags:
But the author goes on to kick sand in the face of the SQL Server community by saying:
“I never felt the camaraderie or the community in SQL Server or .Net newsgroups that was ubiquitous in the Access ones.”
Of course not, dude – because you’re pimping Microsoft Access. SQL Server people ***hate*** Access people. If I had a dollar for every time I encountered a stupendously poorly crafted Access “database” that required amazing rework in order to scale, I’d – well, actually, I do. I have a lot of money from those projects, come to think of it. Actually, would you mind sending me your client list?
5. Subforms
The author writes:
“This might be outdated, but I’m going to include it anyhow. Decades ago when I developed in PowerBuilder, Visual Basic 6 and Access, the ability to drag and drop a form onto another form to create a synchronized pair was huge.”
Welcome to 2010. Among many interesting new things such as flat panel monitors, hybrid cars, and the wheel, we’ve also come to embrace many new programming concepts like MVC. While it was initially intriguing to get your application chocolate in my database peanut butter, we have found over time – decades, in fact – that the application might work best when it’s separated from the database.
6. Reports
See above.
7. VBA
See above, but one quote has to be shown:
“Real Access developers don’t use macros … we write VBA code.”
I don’t even know where to begin with that one.
8. Integration with “real” databases.
All the Storage an Access Guy Needs
Why not just use a real database from the start? SQL Server Express Edition is completely free, and not only do the databases scale up beyond 4 gigs, your career can scale too. The skills you learn working with Express Edition translate to a better long-term career as a database administrator or developer on huge projects – projects where you don’t have to be the one guy writing data storage, reports, and workflows all inside the same tool, by yourself.
One of the author’s points is just flat out incorrect:
“The front end was an Access 97 mdb file, which linked to an Oracle database. On Friday at noon, 125 employees would scramble to update their digital “time sheets” and the system didn’t blink. Maybe it was the fact that the data was in Oracle or maybe it was because the Access used Pass Through queries to execute stored procedures and server side views, but the reason doesn’t matter.”
Actually, that does matter. You could have written the front end in .NET, C++, or Classic ASP, and it would have scaled if Oracle’s doing all the work.
9. New features in 2010
The author says:
“Table macros and publish to Web. ‘Nuff said.”
Errr, wait – up in #7, I thought you said real Access developers don’t use macros?
10. The Play-Doh factor
The author says:
“Access applications are easy to mold and shape into whatever you or your client needs. In this context, easy means inexpensive.”
If we reword “applications” to be “proof-of-concept apps”, I’d agree. But when the client builds a web site for their online store, does it scale? If they suddenly become the next bacon salt, can they sustain a load of hundreds or thousands of customers ordering simultaneously? Can they build StackOverflow in Access? No way – again, I’ve followed behind too many Access projects that suddenly ran into problems with scaling or data volumes.
Folks, come on over to the SQL Server side. We make real dough, not the play kind.
Update: More Posts from Other Bloggers
Other bloggers have chimed in with their thoughts on Access:
- Tim Ford – Saturday Night’s Alright for Fight Club
- K. Brian Kelley – Why I Don’t Like Access
- Merrill Aldrich – Motorcycles and Access
I’m not sure it is that easy to import data from MS Access into SQL Server.
It seems there is no 64 bit Microsoft OLE DB Provider for Jet in SQL Server 2008. This means methods currently used for importing Excel, Access, and CSV data into SQL tables which use the current 32 bit Jet provider, are no longer going to work.
As someone who uses SQL 2005 64-bit, I agree. I have to run my SSIS packages in 32-bit mode to pull in data from all my legacy sources since most of them don’t have a 64-bit driver.
The connectivity drivers for Office products would not be included with SQL Server. They are usually installed along with Office, but you can also download and install them seperately if you’re simply looking for connectivity drivers.
As most are aware, 64 bit drivers for Office have not been available in the past. This will change with the release of Office 2010.
Here is a link to the Beta release of those drivers (there is a link for both 32 and 64 bit), should you be interested in a test drive:
http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
I don’t deal with any 64-bit servers, but I just looked through all the documentation for SQL Server Migration Assistant for Access and it says nothing about there being problems using it with 64-bit SQL Server. This would stand to reason, since the server doesn’t care whether the client is 32-bit or 64-bit. SSMA is, in fact, the tool that most Access developers use for upsizing (I haven’t used it for a real upsizing as all upsized projects have been to SQL 2000 so far).
Perhaps you’re having problems because you’re insisting on using the wrong tools?
In any event, 64-bit ACE is already in beta:
http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
I have heard from people on Stackoverflow who’ve tried it that it already works just fine (I don’t program in anything but Access and PHP, so I wouldn’t know first hand), so if you feel most comfortable using SSIS to import Jet/ACE data, you should try it out. Naturally, it’s backwardly compatible and can import legacy Jet data, though I’m not sure how far back that goes (Jet 4, definitely, Jet 3.x, most likely, anything before that, perhaps, but might require running the data through an import into Access 2000 or Access 97 first, depending on the Jet version).
David, before 2010 Access the issue has just been the lack of 64 bit ODBC/OLEDB drivers, making import using the 64 bit SSIS or linked servers in 64 bit SQL Server unavailable. I’m glad they are finally going to have this driver, not that I need it really. It just seems to make sense due to the fact that everything is moving toward 64 bit.
This isn’t just a Microsoft Access (Jet/ACE) issue. It also is an issue with Microsoft Excel. There are 32-bit drivers for data import from these two sources, but there are no 64-bit drivers that aren’t in beta. A lot of enterprises have gone to 64-bit servers because it removes the memory limitations we’re tired of dealing with, especially with larger databases. However, if you have a Microsoft Access database or an Excel spreadsheet either as a source or as a destination, you’ll have to run your SSIS package under the 32-bit version of dtexec, because that’s what it takes to use the drivers. As a result, the ETL process loses the benefit of being able to access all that extra memory.
Until the ACE provider is out of beta, is it not the case that virtualization can address at least part of the problem?
And, of course, it seems to me that somebody is not making very smart decisions if they are moving to 64-bit platforms when they still depend on data interfaces that are 32-bit-only.
If your servers are connecting to data sources designed to be run on client desktops (where it’s only very recently that 64-bit Windows has become really common), then maybe someone should re-architect them to not depend on components not designed to be used on servers?
In any event, I think 64-bit Office is going to remove this problem, but I’m not sure anyone should blame Microsoft or Access or Excel for being 32-bit up to this point. It has been a natural fit for the platforms on which those apps have been designed to run, and that’s not servers.
David, I still get all the benifits of 64 bit on my SQL Server beyond the 32 bit legacy data sources, so I would say it is well worth it. Plus, because the rest of my core SQL services, IIS, etc. can take advantage of all 8 GB on my server, it frees up more of the “first” 4 GB (not accurate but you know what I mean) to be used by my 32 bit SSIS package. When I’m setting up a new server, I prefer to push the boundries of software/hardware that I can use at that point to move to the next level as quickly as possible, but that’s just a personal preference.
I think if the author had just read the following KB article he could have saved himself and other commenters a lot of time:
http://support.microsoft.com/kb/303528
Specifically:
“Microsoft Jet is not intended for use with high-stress server applications, high-concurrency server applications, or 24 hours a day, seven days a week server applications. This includes server applications, such as Web applications, commerce applications, transactional applications, and messaging server applications. For these types of applications, the best solution is to switch to a true client/server-based database system, such as Microsoft Data Engine (MSDE) or Microsoft SQL Server.”
Access is a RAD tool and Jet/ACE is s small scale database engine. They are entirely different conversations. Jet/ACE isn’t trying to compete with enterprise scale data stores, and for a very good reason. It’s not an enterprise scale data store. It’s a desktop database engine.
The story of popular Access/Jet deployments outgrowing Jet for data storage has a million examples. To completely discount Jet, and especially Access, for that reason seems a bit harsh.
Uh oh. Almost 2GB of comments on this post…
[...] Brent Ozar is in on this discussion too. Here he gives his top 10 reasons why access still doesn’t rock. [...]
[...] an interesting rebuttal to an article written by an access fanboy… Top 10 Reasons Why Access Still Doesn’t Rock | Brent Ozar – Too Much Information the original article is linked in the first paragraph comments by the dbforums experts? [...]
[...] SQLDumbass: I wrote a blog post slamming MS Access, and now I feel kinda bad. Should I be concerned that I will get shanked by an developer at the [...]
[...] the ultimate DBA sin: I used Microsoft Access. And dammit, I enjoyed it. Somewhere, Brent Ozar, the king of Access, is plotting my violent and painful [...]
[...] MS Access [...]
Trying to install SQL Management studio on Win 7 64 bit.
No joy – what a runaround to do something so simple.
Anyone have any ideas….exact steps would be nice.
Pau – I’m not sure what you mean. I’ve done it dozens of times. What’s the exact error you’re getting?
Brent
Thanks for coming back.
As soon as I run the file I get a message “This program has known compatibility issues”
I found a post relating to a service pack 3 so am in process of installing that to see if it makes a differnece.
http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=5d76230d-580d-4874-8c7d-93491a29db15
Yes, get the latest version of SQL Server. It’s SQL Server 2008 R2.
Seems to be working now. Bit of luck finding that luck.
However it brings up a connect to server dialog box.
I put in localhost() but it’s coming back with an error that it cant find it.
An ideas?
Thanks
It sounds like you’re new to this, so you may want to puck up a book to help get started. Click on Books at the top of my page for recommendations. I wish I could help everyone troubleshoot, but that’s beyond what I can do here.
Thanks Brent, but I just need to connect to a server at this stage
You’ve left 4 blog comments just getting to this stage.
Stop punishing yourself – pick up a book and make your life easier. We’ll both be happier. 
Thanks Brent, but I’ve just got two Sharepoint books from Amazon. Yes, even I the humble Access developer is moving on up.
Congratulations. I’ve got a dozen books on cooking, but that doesn’t help me connect to SQL Server either. I know it sounds flip, but if you’re struggling to connect to your local SQL Server, then even when you succeed, you’re going to be in for a steep learning curve. Go get help.
Thanks Brett..I figured I needed my pc name followed by SQLExpress
Never had such issues with Access
Awesome – you come on my site asking for my help, and then you plug your own training site? I’ll be editing your comment now and ripping out that link. FYI, you’ve effectively burned a bridge. Buh-bye.
Actually dude – it was not a plug. Just a bit of banter. No probs with removing the link.
Late to the party but.
A lot of people in large organisations don’t have access to SQL Server. (I do)
It really excels for RAD Development for small numbers of people.
I would argue the skill and knowledge competency of the proponent maybe more important than the environment that they use.
Hell what should I be more impressed by Facebook or the guys at NASA who managed to get to the moon?
“Excel has a heck of a strong feature as a front end, and I’d make the argument that I’d rather do a lot of my “landing pad” work in Excel, not Access.”
Ever done a vlookup on 50,000 rows? obviously not…
Preparing data for something? My choice Access!
Landing pad for data? Yes!
No Name – I just love how you don’t have the guts to put your name or email on that comment, but I’ll humor you nonetheless. If I need to do a vlookup on 50,000 rows, I do it in – brace yourself – a database, meaning SQL Server. It’s faster than Access. Kthxbai.