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? There are ~1700 Stack Overflow questions tagged with Access, and hundreds of thousands tagged with SQL Server. Not to mention the wildly vibrant #SQLhelp on Twitter, and SQLslack.com.
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.
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:
249 Comments. Leave new
Fast rebuttal Brent. Regarding #7 on VBA, unless I’m missing something VBA shouldn’t even earn a mention in the original article because it’s been replaced by Visual Studio Tools for Applications (VSTA)
http://en.wikipedia.org/wiki/Visual_Basic_for_Applications#Future
The citation has zilch to do with the programming language hosted in the applications that are part of the Office suite. VBA is the only language choice in the next version of Access, and so far as I know, in the version after that. While the other Office apps can use .NET components, Access cannot. I expect this is something that will change in the next few years.
So, there is absolutely no reason why VBA should be omitted from the original article.
While for new development VSTO is the preferred method, hit ALT+F11 in any office app and VBA pops up.
I’ve written VBA this year in Excel 2010. They twaked it a bit to deal with 64 bit systems. Yes the IDE is outdated and ugly, but it still works.
As someone who got their start using MS Access, and also my present job, I couldn’t agree with Brent more. I first learned Access because the company I worked for at the time had it and I needed a way to store information to build a foundry scheduling/inventory system. This was back in the SQL 6.5 days so no SQL Express back then. I remember being totally amazed at my first exposure to SQL Server, when SQL 7 first came out and Microsoft was bragging about their Terraserver.
The company I work for now still uses Access 97 as a report engine connected to SQL 2005 backends. Access is what the corporate folks understand, somewhat, and they’re reluctant to let it go. But at least we’re moving towards SSRS for most reports. Not a day goes by that we don’t discover a new “feature” of Access where data doesn’t come across properly.
It is strange to think how many of the visual tools we see in SSMS first came from MS Access. I remember using it as a design tool for SQL Server once. I haven’t used Access for years but one of the better presentations at last year’s Teched was from an Access expert, who has got the delivery of Access-based mini-systems down to a fine art. It didn’t persuade me. I’ve suffered enough from it in the past.
“SQL Server people ***hate*** Access people”
A little bit harsh methinks 🙂
No Jamie, it isn’t 😛
Ok, maybe a little harsh, but I think my blood pressure rises about 20 points any time someone suggests using access for anything.
Access people don’t hate SQL Server. They use it all the time as a secure, reliable back end to their Access database applications. They also use MySQL and PostgreSQL and Oracle and just about any database engine you can name.
SQL Server people seem to hate Jet/ACE, which is not Access at all. And they don’t seem to have any comprehension of the distinction between the two, which just goes to show how shallow their understanding of the situation is, and how unpersuasive their opinions on Access are.
No, some of us hate Access and what we’ve seen users do with Access. I don’t mean the database, I mean the portion that lets them write aggregious queries for their reports, reports which get kept in that one Access file which they invariably misplace or misfile on the file server because they only open it up once a quarter or once a year to generate board reports. I would rather the reports get written and deployed to SSRS or Business Objects or some other reporting platform. That way when they call up and say the report is performing poorly, we can actually see how they’ve built it, and also when it comes time to find it for their next round of report runs, they can without us having to pull back a tape from six months ago to restore a file that it turns out they deleted.
I’ve just had my first experience with SSRS.
It is truly crap which is what I expected.
MS have never managed to replicate the Access report writer in any other form. Check out VB6, .Net SQL2008.
Truly rubbish report writer.
Tedous is not the word, I can think of so many others.
I’m sure Sharepoint must have a reporting facility.
Anything to get away from SSRS.
My advice to anyone thinking of using SSRS – stick to Crystal.
I couldn’t have said it better myself! 🙂
I’ve dealt with that exact situation at my work over the last 11 years or so.
I’ll admit, I’ve seen some really cool implementations of Access DBs as well, but those were all systems that used link-tables that were back-ended into some sort of SQL server –on the project I’m thinking of, it was MySQL, though at the job that pays the bills, we primarily use MS-SQL (I’m not sure what version our SQL-Cluster is up to, but it’s pretty vast in terms of what all it’s running).
Personally, I prefer working in open source languages because they tend to be a lot more portable across platforms, and while I primarily work with Win8.1 (+Classic Shell) at work, I also bring in my own tablet that I keep loaded with Android x86 and Ubuntu-Mate and do a large amount of my development work via that system with multiple SSH clients running from separate desktop spaces to reach out to all of the systems I manage. (I work in network security, but I’m also a developer and systems engineer… I got started with computers about 32 years ago and never let up… just to put things into perspective of where I’m coming from).
Access *is* handy… for the rank amatuer. But those of us that really know what we’re doing all tend to use SQL in one form or another. And that’s sort of the beauty of it… there’s no one set way to go about things. But, there are good ways and bad ways, and I see that you very much recognize that as well, and have obviously dealt with it in a corporate/enterprise/gov. environment like myself, or at least something within the ballpark of those scales of infrastructures. The problems that we see in these environments, where thousands of users are working out of SQL servers simultaneously, really goes to show the weakness of Access and anything with similar limitations, mainly due to the scalability as others here have mentioned as well. The one thing I personally used to really like about Access was the ability to create a skin-like template in HTML to wrap around an Access front-end which ties via linked tables to SQL. That was a cool and easy way to build our web-applications for event tracking or small projects, and I even used a setup like that at home for a while for tracking my own technical inventory… but I eventually replaced it with a LAMP server running an OTRS install that I’d extended a bit.
I couldn’t imagine someone trying to do the other project I consult on, via Access, though. It’s a massive array of servers, running CentOS, Apache, Tomcat, PostgreSQL and a few other engines that combined form my state’s employee benefits system. As is, on the existing platform, it has zero issues in terms of performance and overall reliability, and this is the 3rd platform migration I had moved it through, from planning, engineering, and implementation. Currently, this system as a group has around 96 Xeon cores and 160GB ram involved in processing, and ties into a 160TB SAN (though, itself, only utilizing about 4TB of space on the SAN). At any one time, this system has around 180 workers in it, plus a percentage of the 80,000+ state employees that access the systems from time to time. Access can’t even touch a load like that.
Anyway, your post got me to thinking about my personal experiences in which I’ve dealt with user-created problems that I’ve had to unravel in the course of my job because of a misconception or lack of proper training on the part of the end user. Good post man!
Your blaming non-IT people who don’t give a rats @#$ for something Software has no control over.
MS Access is an easier and less expensive way to build an application that doesn’t have more then 3-400 users, and doesn’t need a web Interface…..It comes packaged with MS Office…..
I have experience with both products. MS SQL and Access.
The people paying for these applications don’t care that SQL is more “pure”, according to Geeks who spendd all their time obsessing about such things.
Does it work? Is it serving our needs? Yes, yes it is, and its cheaper. The SQL developer wants more “time” and people. The Access developer doesn’t need all that………………. Access wins. And it will be around and heavily used, for a long long ….long time.
Jason – you wrote:
“MS Access is an easier and less expensive way..It comes packaged with MS Office…..”
Interesting. How much does Office cost per user these days, by the way?
“aggregious queries” terrible horrible misunderstanding of language in general
Seems kinda closed minded to insist on using SQL Server instead of Access no matter what. I use whichever tool suits the circumstance. Access is perfect for some situations, and SQL Server can be overkill. I have a killer desktop PC in my home office for serious work and paying games, and a tired old piece of junk tablet in my briefcase for quick trips when I’ll just be checking email and looking up flights or the weather on the web. I put cheap soap in the guest bathroom hardly anyone ever uses, and luxurious fancy soap in the master bathroom shower.
Exactly. I analyze a clients requirements, and if Access fits the bill so be it. If MS SQL would be best, I would recommend that. Reading the author of the Blog poster reminds me of an argumentative 16-year old.
“SQL Server people ***hate*** Access people”
I don’t hate them. I sometimes am frustrated by the fact that people on both sides can’t seem to separate the discussion of the database engine that is holding the data and the application design/generation features that MS Access offers. They really are two separate parts that can be discussed separately.
For instance, one can use MS Access to throw together a fast, non-scalable, non-enterprisable front end on almost any database on the back end, much like the Oracle example above. Sometimes that’s all that is needed. Of course, people forget the non-scalable, non-enterprisable part, but that’s true of any workaround.
With most I talk to who are “access people” go off on why MS Access is so great, they aren’t talking about Jet. They are talking about the forms/macro/code/reporting features.
I have also made lots of money working on taking a “groaning under the weight of too many users and no disaster recovery strategy” MS Access system to the real world. It just means, though, that the original solution was no longer working. Same thing happens with all personal productivity tools like Excel, PowerPoint, and Visio. Eventually the requirements exceed the original design.
Excellent post, Karen.
This very article makes exactly that mistake. All the actual criticisms are of Jet/ACE being used in inappropriate scenarios. None of the criticisms are of Access as database application development tool.
This article is really quite worthless, as all it actually demonstrates is that the person writing it doesn’t really understand the most basic facts about the product he’s criticizing.
There is a situation, though, where even the use of MS Access as the front end can be problematic when it comes to enterprise deployment, scalability, performance, and reliability. It really does come down to knowing when a solution has reached a point that its underlying platform components no longer can meet the increased volume, distribution, DR, and reliability requirements. The rule of thumb I usually use is when we get to 10 concurrent users, we need to move to enterprise class technologies.
Mod this comment up!
I find that the Access hate is mostly directed at Access databases that were quite successful when small. Then they grew and the problems really started. But to be clear, Access can be a great solution for personal or small workgroup databases.
The thing is, Access is “accessible”. In the real world, by ordinary users, who routinely have a copy of Office already installed. Access has all the best attributes that PC’s had when they started.
Would you use a PC as a server? You can but it’s not a good idea. And the 10 user cutoff point to transition to an enterprise class database, I also agree with.
The danger with Access is that databases have a long history of growing beyond their design capacity. And organizations try to avoid/postpone the looming re-write by keeping the personal tool running too long. That’s what ticks off IT folks.
This rebuttal and the original article are both a waste of time. Access is like any other tool available to developers. If used in the proper context it does the job, just as SQL Server would be overkill for some projects. As a developer I use the tools that provide me with what I need to meet the requirements of the job whether it be Access, VB, C#, ASP, SQL Server, or something else. Every project doesn’t require SQL Server or Access it all a matter of using the right tool for the job. Like any good developer I keep a variety of tools in my toolbox so I can provide my clients with best solution for their dollar spent.
John – the problem is that we only have a limited amount of time in our day. Every new tool we try to learn, the less time that’s available to us to learn something else. I would rather focus all of my database education on a platform that can scale, rather than being a jack-of-all-trades on two database platforms.
Brent – I agree that there is only a limited amount of time to learn, however if you focus all your efforts on just a single platform then you may not be providing your customer with the best bang for the buck. I’ve been in this business for over 25 years and SQL Server isn’t always the answer to very datbase application. I can definitely say that I have made plenty of money fixing so-called SQL Server based applications also, but that isn’t to say SQL Server is bad just the implementation was bad…I believe the same can be said for some Access based applications. Proper implementation makes all the difference.
John – I disagree about focusing efforts on a single platform. When building a solution for a customer, I don’t do the whole thing myself. Building a solid-scalable application from start to finish, including the user interface, data access, and database, is more than I would take on personally. I’ve chosen to specialize in one part of the solution.
I do agree that if the company only wants small applications that don’t need to scale, one person may be able to do it well, but for scalability, it’s beyond one person’s skills.
Brent – While I agree that building a solution does sometimes require a team, however even large scalable systems don’t always require a team to accomplish. I guess it is just a matter of approach. I have done it both ways, and IMHO and that of my clients successfully. My feeling is that even if you focus on just a part of a solid-scalable solution without intimate knowledge of all the parts and their inherent strengths and weaknesses you may be doing your client a disservice.
Just my two cents worth…have a great day.
John – sorry, but you’re going to have to back that up. Point my readers at a large scalable solution storing data in Microsoft Access that was built by one person. I think I speak for a lot of us when I say I’d be very interested in seeing that system, and it would change the way I feel about Access.
Brent – I don’t believe I said anything about Access in my response…I was talking about large scalable solution development in general and whether a team was needed to accomplish said solution.
John – okay, now you’re justifying learning Access because teams are necessary to build large scalable solutions? That doesn’t add up.
Wow Brent – I think the wheels have fallen off the track here. Your response to me didn’t have anything to do with Access it spoke about my viewpoint on having a set of tools to use and your disagreement and viewpoint that in your opinion focusing on a single platform only was sufficient. Maybe I am missing something here, but my response was in regards to your “single tool” approach and my “multi-tool” approach to development and whether a team was a requirement for developing a large-scalable solution.
The problem here is that when people criticize Access for lack of scalability, they aren’t talking about Access at all, but about Jet/ACE, i.e., Access’s default built-in database engine. You can built an Access application to run against almost any back-end database you like, and scalability will be determined not by Access, but by the capabilities of the database engine and by the efficiency of the schema (and the hardware and so forth).
Now, that said, I wouldn’t recommend an Access front end to a database with 10,000 users. It would work, but there are enough deployment issues with an Access app that it would probably not be a good choice.
But the vast majority of database applications out there (and the ones that businesses and workgroups need) have only a fraction of that number of users, and Access as front end is perfectly adequate or stellar for those environments. Whether or not Jet/ACE is appropriate as data store depends on the user populations. Anything under 20 is going to be just fine for most apps, unless the developer is incompetent.
But that’s the case with any development platform! Sturgeon’s Law applies everywhere.
True. But the debates are more fun 🙂
That’s for sure…let the zealots fire away…;-)
All the arguments in the Database Journal are biased. The *only* useful purpose of Access was the reporting capability. BUT when SSRS came in the SQL Server 2000, that changed. How many concurrent users does he have in his Access applications? I developed Access apps in the past. And once they hit the production floor, performance became an issue. Access is meant as a training ground for data workers. Once they get past a certain level, they should go beyond Access and just forget about it. Do not even think about VBA stuff. Go .NET if you ever want to do database programming. The author of the Database Journal article must be kidding. For all I know, he’s just out to piss off Brent O.
Marlon, I recall when SQL2K came out and I looked at SSRS – all they did was take MS Access Reporting tool and add a few bells and whistles! Well, that’s what it looked like certainly.
I like to think of some of the SQL Features as having grown out of things people just don’t want to let go of in Access, but I would be happy to see Access go the way of the dinasour. Well, at least business critical ‘applications’.
But I have to agree, Brent, I’m with you as I’ve made a lot of money fixing these Access applications 🙂
Hilarious!… Thanks for the laugh this morning.
I’ve never touched Access… never seen an app written with it… All I know is that Access wouldn’t exist if MSFT hadn’t bought Fox Software in the early 1990’s to incorporate FoxPro’s “Rushmore” technology into its data engine. This leads to the corollary: “VFP people ***hate*** Access people”.
Access already existed before Microsoft purchased Fox. Microsoft incorporated the Fox Rushmore technology into Jet because it was good technology and improved the engine. Perhaps Access wouldn’t be as successful if Jet hadn’t gotten that improvement, but saying it wouldn’t exist without the Fox acquisition is not very believable.
Wow Brent – I think the wheels have fallen off the track here. Your response to me didn’t have anything to do with Access it spoke about my viewpoint on having a set of tools to use and your disagreement and viewpoint that in your opinion focusing on a single platform only was sufficient. Maybe I am missing something here, but my response was in regards to your “single tool” approach and my “multi-tool” approach to development and whether a team was a requirement for developing a large-scalable solution.
I know it makes a great punching bag, but I don’t really think it’s fair to attack Access here. Access is a FANTASTIC tool for the advanced business user (read: non-developer). It is also a great tool for developers to use for basic automation tasks, or applications that are just beyond the advanced business user’s ability. To use a “real” programming language and a “real” database for this type of task would be a waste of hours IMO.
Regarding the cleanup work, I wouldn’t be surprised if the businesses involved actually got a better value by having the application logic figured out by the Access guy, and then calling in the big guns to tackle the flaws in their implementation.
Responsibility for the disastrous Access implementations out there has to be placed on the businesses involved, for failing to recognize the point at which these Access applications become a problem. I’m sure that the hotshot access developers promising enterprise-ready Access solutions don’t help either, but that can’t be blamed on the tool either. If you really want to reach these people, it’s going to take a more nuanced approach than shouting “Access Sucks” from the top of the mountain.
Alex – I would agree with you, but reread the original article title. It says “Top 10 Reasons Why Access Rocks for Developers.” It didn’t say for advanced business users – it said developers.
Touche!
I obviously didn’t read the original article. I still think that Access has its’ place in a developer’s toolkit for the type of tasks that I mentioned above. This is especially true for the type of developer that’s embedded in a small department, and not working on systems used by the entire company. Whether it’s worth taking the time to learn is another story, but I suspect that anyone developing with other Microsoft tools would be able to pick it up pretty quickly.
It might take years of experience to figure out the black magic that separates the “enterprise-ready” Access apps from the rest, but that may be a good thing. The basics of Access (and knowing how to use pass-through queries) are probably sufficient to take your Access application as far as it ever should go 😉
Alex – yep, I agree there. I really like Excel for these same types of solutions. I wouldn’t slam Excel because it doesn’t have relational integrity – it’s the right kind of tool for a certain kind of job. Lots of jobs, actually.
Alex – I agree and well said.
I knew *someone* would write a rebuttal to that Database Journal article. Figured it’d be a wee shouting match, too :-).
To me the biggest problem with Access is actually not mentioned directly here or there: it is, simply, “if you want to dig yourself a hole it will be hard to climb out of, then Access is the best shovel anywhere.”
The problem has nothing to do with price or features, or which product is better, it’s the fact that an Access application written for a small business is, in almost every case, a spectacular and frustrating dead end.
I have had to try to untangle several of these for customers, as a consultant, and it was never, ever pretty. The endless repeating story:
A small business or corportate department makes a modest Access app, and it works for them. The author is not generally a developer, so the schema is sort of adequate, and the code kind of works. But, in principle, it’s a success. The group becomes more dependent on it, and it gets new features. Version control is almost impossible, but it’s usually one guy building it anyway. The production MDB is where he deploys his work.
At some point it gets too slow, or broken, or mysterious things start happening like data integrity problems. There are questions about backups and such. No worries, says this group, there’s a path to upgrade to SQL Server (this is a myth foisted on them by Microsoft, and it’s one thing the MS marketing team does that really … er … upsets me.)
The team then faces this dilemma: soldier on, indefinitely, with this broken, slow app OR scrap everything and reconstruct it on a scalable platform. There really isn’t any middle ground. And don’t even talk to me about pass-through queries and linked tables. I’ve been there, and it’s not a happy place.
So the core problem is: migration from Access to a real platform is a myth.
I’m afraid I’d have to disagree with your assertion that “migration from Access is a myth.” I make almost my whole living taking these old apps that have been in use for years and making them into something better. There isn’t any need to migrate from Access as front end, and in many cases, no need to upsize the back end. I get hired when all the hotshot developers have quoted $25K for a job I can do for 1/4 that.
I’ve seen some ugly apps, but they were GOOD ENOUGH.
I don’t think GOOD ENOUGH gets enough credit. HTML is another example of this. The web is a collection of horrid, non-comformant HTML that won’t validate. But boy, it sure is useful, and it sure is easy for anybody to put up a website with HTML.
Access has enabled a lot of small businesses to have a database application to address real business needs when they otherwise would not have been able to have afforded it. And a lot of the people promoting themselves as “professionals” are not — I’ve seen tons of crap created by “professional” Access developers.
But even the crap worked for a long time.
Right up until it didn’t, and then I get called in to fix it, and the customer gets more value out of the investment in Access. And saves a ton of money in comparison to the alternatives.
So you give your clients a better-performing version of a product that’s locked into one vendor and one OS and a deprecated language? There’s a reason for that $25K price tag: the other developers want to move the product to a more viable toolset with an actively developed language and an evolving, improving database with scalability. It’s not surprising that the kind of company that went for Access in the first place is now choosing the cheapest solution that just crosses the threshold of acceptable improvement. 🙁
>The web is a collection of horrid, non-comformant HTML that won’t
>validate.
And most of that was written by the same type of people who developed the Access apps. 😉 And they were coding to another proprietary MS product, IE6.
>Access has enabled a lot of small businesses to have a database
>application to address real business needs when they otherwise
>would not have been able to have afforded it.
Are we really going to credit Microsoft here? The purpose of Access nowadays is to be a gateway drug to SQL Server and also to encourage the user to become more firmly embedded in proprietary Microsoft tech – such as removing Data Access Pages from Access and replacing it with the ability to only share data on MS’ proprietary Sharepoint server product.
It still costs $100+ just for Access and upgrade pricing has been discontinued by Microsoft to make things “simpler”. Again, roping you in and then milking you. If you’re going to talk about software that deserves credit for giving people database abilities they would not have been able to afford, the credit can only go to open source. From the public domain local database sqlite3 (with no arbitrary 2gb limit, full ACID compliance and comprehensive QA process) to enterprise-level PostgreSQL which offers features like data compression, partitioning and replication only available in SQL Server Enterprise (which’ll run you a minimum of $25K) these are the tools that are really making database development available to all. We can toss in just about any programming language such as Java or especially python – simple to learn like VB but much more powerful and as a scripting language equipped with great capabilities for manipulating data that you don’t find in VBA. Heck, you can toss in ipython as an interactive shell/front end for real-time ETL and data manipulation, and the pandas library, in wide use in both financial services and scientific institutions for its “data frame” structure that lets users easily slice and dice data, sort, filter, exclude blank fields, summarize data, perform functions on rows or columns, join together different frames on matching fields, drop rows or columns, easily output data to numerous formats, etc. And for reporting there are BIRT, Jasper Reports and Pentaho, sophisticated Java-based reporting solutions. Report servers are also available for them, unlike Access, for sophisticated automation of report running and delivery. Unlike Access, tools like Java, python, BIRT, PostgreSQL, sqlite3, etc. are all cross-platform, with python and sqlite3 even runnable on smart phones! Each of these technologies also store data and produce output in *open standard formats* so there’s no vendor lock-in (which is why there are several report servers which can serve BIRT, Jasper and Pentaho reports). PostgreSQL is quite scalable and 4TB databases are known to be in production. Unlike VBA, learning python opens up many other possibilities as it is both a general purpose, standalone language and also embedded as a scripting solution in many other products. In fact, one of PostgreSQL’s nicest features is that stored procedures and triggers can be written in at least 12 different languages so far, one of which is Python. Versions of Python for Java and .Net/Mono, Jython and IronPython, also exist and allow the use of Java/.Net libraries. C# and Java are also obviously found in many places. This makes the extra effort to learn a full, still-developed language (particularly python as the extra effort is minimal) far more worth it than putting the time into VBA. The same with mastering ISO-standard SQL vs. Jet/Ace. And I’m not sure why a small-budget business or startup would choose SQL Server Express instead of a db with no file size, memory or core limits, and a host of features unavailable in SQLSE, ready to scale with them.
I just don’t see the merit you’re pitching in polishing up bad code on an intentionally limited product which may have more features stripped out of it, UI features forced on it (ribbon), or who knows what else (Metro????????) at any time for reasons detrimental to the developer but beneficial to the vendor. It’s like bragging about taking $3K and patching up an old car with 200K miles on it rather than spending more money on a recent used car or a lot on a brand new one. The reality is that the 200K’s EOL date is approaching, patches or not, and that 3K would have been better spent on buying a faster, safer, more fuel efficient alternative. And they’ve saved nothing by buying Access and then paying you, especially if the whole idea of Access is DIY development in the first place. They’ve now spent thousands. They could have spent $0 on an open source language, IDE, database and report writer and put a tiny fraction of what they spent on you into a good book or two like the Head First series. Actual training, even community college night courses (not necessary, of course) would still have been cheaper, and they’d have a valuable skill set to show for it in the end as well.
SQL Server is not a RAD tool, Access is. SQL Server is good for storing large amounts of data and has good security – nothing else.
Why are you comparing MS Access with SQL Server – it makes no sense at all.
If you are going to compare Access then compare it with another desktop database system – there are plenty around, but none as good as Access. MS Access is the world’s best selling desktop database by miles.
If a user wants to create a small database for a small number of users then tell me how they would do that in SQL Server? I suppose it would mean writing pass through queries in Access or possibly implementing an interface in .Net. Not very user friendly for the average user is it?
So lets see – how would you get SQL Server to talk to Powerpoint or Excel or Word? Oooops..I forgot …you can’t make it talk to those apps.
Pls compare like for like……SQL Server against Oracle, SAP or Siebel. Many would say it does not come up to scratch. Personally I have no problem with it…I use both SQL Server and Access, but like with anything it comes down to using the right tool for the right job.
Paul – lots of good questions in there.
“SQL Server is not a RAD tool, Access is.”
I agree. However, I’m focusing specifically on the aspect of Access that’s near and dear to my heart: its inability to securely and reliably scale data storage.
“MS Access is the world’s best selling desktop database by miles.”
It comes free with Microsoft Office Pro. That’s like saying Notepad is the world’s best selling text editor.
“If a user wants to create a small database for a small number of users then tell me how they would do that in SQL Server?”
I did a webcast on that topic called Developing Something for Nothing. I encourage you to view the webcast.
“Not very user friendly for the average user is it?”
The original story that I linked to doesn’t say “Top 10 Reasons Why Access Rocks for the Average User.” It says for the developer.
“So lets see – how would you get SQL Server to talk to Powerpoint or Excel or Word?”
If you store a lot of data in PowerPoint and Word, then I understand why Access would seem attractive to you.
Lol… this last comment has me rolling on the floor…
“If you store a lot of data in PowerPoint and Word, then I understand why Access would seem attractive to you.”
Devastating blow…
To quote an older video game: “Fatality!”
You mean “the aspect of Jet/ACE…its inability to…scale.”
Access is not a database.
Access is a database application development platform that comes with a default database engine that is useful for small-scale data storage needs.
If you mean Jet/ACE, then say Jet/ACE.
But you’re missing something pretty darned important, and that’s that Danny’s article was not about Jet/ACE, but about Access.
Your arguments are really confused, and your response to Danny’s article shows it in that you do a bait-and-switch each time you criticize Access — you take a point about Access as front-end development tool and flip over to something Jet/ACE-related.
This is the same mistake all the Access bigots make, and they look really foolish when they do so. They reveal that they just don’t understand what Access is, and that’s probably because they don’t really know anything about it beyond what can be trivially gleaned by a few hours playing around with it.
When someone chooses Jet/ACE to store data that should better be stored in a database engine with larger capacity and better security, the flaw is not in the tool, but in the person mis-using it. Jet/ACE is what it is, and it works beautifully for small user populations (a couple of dozen). That covers a really large portion of the database applications that are needed by small businesses and workgroups within larger businesses.
And that’s why Access has been such a huge success, because it fits the needs of a large number of businesses needing database applications. That those applications can outgrow their original scope and design should not surprise anyone — that can happen with any application of any kind and built on any platform.
So stop dissing Access when you really mean to criticize mis-use of Jet/ACE. And put the blame where it belongs, not on Access/Jet/ACE, but on the people who make bad decisions about which tools to use for particular tasks.
You write:
Wait, in the main article you say:
So, you agree with Danny’s original point.
So why are you criticizing him for saying what you yourself are saying here?
(and let’s not even get into the issue of the Access 2007 runtime being free, which means you can develop and distribute an Access app for the cost of your developer’s copy of A2007 with no additional cost)
I think his point was that you Access is usually not the main reason why people buy Office. The reasons are Word, Excel and Powerpoint (in that order). Access is just something that comes along with it. That’s why his comparison is valid.
Well, that’s fine, but he can’t have it both ways. Either that’s his point, i.e,. that most people aren’t paying anything extra for it, or his point is that Access is not free. Making the one point basically negates the other, so he should decide which he wants to make.
If he’d written what you wrote, there certainly wouldn’t be anything for me to disagree with.
David, in this case let’s use the comparisons to put it on perspective:
– It is invalid to say Notepad is free. Notepad is not free, you had to pay the Windows license to get it.
– It is invalid to say Notepad is the world’s best selling text editor. Yeah, EVERYBODY who has Windows has notepad. But the reason that they bought Windows is not (at all) Notepad. The fact that it comes with Windows doesn’t make it a best seller word processor.
So… just translate those into Access, by the same logic:
– It is invalid to say Access is free.
– It is invalid to say Access is the best selling desktop database.
I hope I helped clarify Brent’s point. It is not “having it both ways”. It’s just two invalid statements that look alike… but are actually 2 different things.
Two years later this reply is cracking me up :-). But I actually can tell him that you could do it with PostgreSQL. It can have its stored procedures and triggers written in many languages, including Python. Unlike SQL Server, which converts code in other languages into CLR first, PostgreSQL really is running the code (so the appropriate interpreters/compilers need to be on the server). Python has binding to access the COM automation for Office products (although I admit I’ve never used automation to interface with Powerpoint, but I assume it’s possible as with the other Office products). To take things even further, PostgreSQL offers “foreign data wrappers”, FDW.
“In 2003, a new extension called SQL/MED (“SQL Management of External Data”) was added to the SQL standard. It is a standardized way of handling access to remote objects in SQL databases. In 2011, PostgreSQL 9.1 was released with a great support of this standard.
In a nutshell, you can now use various Foreign Data Wrappers (FDW) to connect a PostgreSQL Server to remote data stores. ”
These FDWs will look like regular tables in PostgreSQL, even if they’re pulling in data from an RSS feed, Amazon web services, CSV file, etc! There’s an extension to be able to create FDWs with Python. Therefore, one could create a FDW with python and its automation libraries for Office (there are also libraries to directly read Excel files, I’m not sure about Word and highly doubt it for Powerpoint).
So I know it was a smart alec rhetorical question, but an answer really does exist regarding using a “real” database to do what he wants. I find it interesting that Access users imagine that talking to Excel is something magical and exotic and only available in Access. They call it MTBS (Microsoft-Trained Brain Syndrome) in the Linux world – the mistaken belief that only Microsoft tools can do something or that the Microsoft way is the only way something can be done. You’ll see it exhibited when someone says something like “I need Excel” rather than “I need a spreadsheet” or “No, I have to type the letter in Word!” I even saw it in a comment above when a person was claiming he uses the best tool for the job and then every single tool he rattled off was a Microsoft proprietary one, meaning he might not be as open-minded as he thinks. 🙂
Courtesy of COM, almost anything can talk to Office products anyway. I know I did it with Delphi Pascal for years. It’s not a spooky exotic technology baked into Access; it’s a (Microsoft) standard exposed to anything that wants to use it.
Perl is a RAD tool. Probably the most significant RAD tool considering spread of platform, and ease of which you can quickly write code. This is why Perl gets knocked around. Because it’s really easy to write bad code in Perl. You can write excellent code in Perl, too, but there’s a reason there’s a Perl obfuscation contest each year. So then, given that Perl has the ability through one of its libraries to generate file system databases (which effectively is what Access is), and given that if you develop such a solution in Perl you could run it everywhere, from a developer perspective, why not go Perl? Why stick with MS Access? And yeah, on the Windows side it does a great job interfacing with Excel and Word, so that argument is moot. And yes, there is the ability to create GUI interfaces, too. So that’s not an issue, either.
Brent
I am not knocking SQL Server. I use it each day as the backend to my VB.net and ASP apps.
I agree that security in Access has never been good…in fact MS have ripped it out completely with the latest version. Also it has never been the most stable of DB’s, although I see an improvement to earlier versions.
I don’t see how it is given away free. It’s always been part of the Office suite, since version 2.0 I believe, or sold seperately – and the MS set of release books cover it. One could argue that OneNote is given away free. It’s not to be confused with SQL Server Express which as you know is a free product.
As a developer I find it a great tool and am not part of the snobbery that exists among programmers. As I mentioned, it is a question of using the right tool for the right job…something which many developers fail to do. Preferring to look at the project more from their own, rather than the users needs.
I just don’t use SQL Server for certain projects….it’s overkill at times. Also I’d rather the user was empowered to write their own queries, forms, reports etc…..most people would not want to get involved with SQL reporting services for instance.
If you are going to create a database for a small number of users why put yourself through the tedium of using SQL Server?
Great question about why put yourself through the tedium of using SQL Server. Here’s a few answers:
Because users grow. What starts out as a tiny app for a few users grows over time. After all, you want your app to be popular, right? You want the users to like it, and you hope that the company grows. When it does, your Access data storage will be toast.
Because features grow. If people love your app, they’ll want it to do more and more things. They’ll want to store more data in it and slice and dice the data. It’s pretty easy to run into Access’s file size limits.
Because you need to back up. Backing up an Access file – not database, FILE – is a nightmare. The more people use it, the harder it is to get a backup. SQL Server backups are a piece of cake.
Because you need reliability. The more popular your app becomes, the more people will need it as a part of their day-to-day routines, and the more it will become mission critical. You can’t throw money at Access and make it highly available. You can write a check for a SQL Server application and get high availability without redesigning your app.
I could go on and on – transactions, security, recoverability from corruption, caching, scheduled jobs, you name it, Access doesn’t deliver.
You are really confused. You say “Access doesn’t deliver” but Access *does* deliver all these things if you choose a database engine that provides the functionality you say is required.
If you want to criticize Jet/ACE, then do that.
But this constant confusion of Access and Jet/ACE just makes you look foolish. That you share this confusion with so many doesn’t make it any less laughable.
I don’t think you know subject you are talking about.
Because users grow
Because features grow
Do you know about existence of DB up-sizing procedure for MS Access? Jet-based database could be transferred to SQL-based without too much pain any time when size or number of users exceeded.
By the way, 2GB is a lot of data if database doesn’t have embedded objects. It covers most of middle size databases I’ve seen. Larger databases normally planned as SQL server databases in the first place anyways.
“Backing up an Access file – not database, FILE – is a nightmare”
I don’t get it, how difficult to backup/copy 2GB file which is not locked (mdb files never locked)? Why is it nightmare?
If we are talking about frontend application which normally on is stored locally on PC or on home drive (if TS used) back up not needed in most of designs.
I’m putting my infrastructure architect hat on here.
“mdb files never locked”
Sorry, they do get locked. And sometimes, from a server operations perspective we have to go in and break that lock. We’ve had users not be able to get in. We’ve had critical files not get backed up. The first is a problem. The second is a nightmare.
Also, up-sizing is easy on the back-end. It is *not* easy on the front-end. Now you have to ensure everyone has their connections updated. If you’re dealing with just a handful of users in one office, no big deal. But when you’re talking about 50-75 users in 30 offices, big deal. When you get larger than that, really big deal.
In most applications, 50-75 users is too many for a Jet/ACE back end, so any competent developer would be using a different data store. And the mention of “30 offices” really suggests that a Jet/ACE back end is not in the cards in the first place (unless the app is deployed via Terminal Services, or it’s a web app with a Jet/ACE back end, which I’d say is a mistake with even 10 users since Jet/ACE is not an appropriate data store for web applications at all because of the threading limitations).
So, really, your scenario here is unrealistic, and, again, doesn’t reflect anything other than the incompetence of individuals who have failed to choose the right tools for the specific job.
No, David, my scenario is not unrealistic. It is realistic (being seen in the real world) because I’ve seen it a lot, and so have many SQL Server DBAs who are asked to upsize these type of solutions.
I also didn’t say more than 10 concurrent users, which is an assumption you made. This was for data that was updated by each of these folks at most once a month. So at most there would be 2 concurrent users trying to access the data at any one time. However, there were that many users over that many offices. And the problem they ran into was they needed all the data in one place. So an engineered solution (not ours) was to put everything into a Jet/ACE database and store it on a central file server. Amazing how 2 concurrent users could cause a resource contention. Often.
And because of that, I’d rather deploy a SQL Server Express back-end every time rather than rely on Jet/ACE.
(for some reason I’m not given a link to reply directly to your last post)
You’re confusing incompetence of the people deploying the solution with defects in Jet/ACE. First off, 30 offices means the users are not all on the same LAN, so as Jet/ACE back end, even with 1 user in each office is unquestionably an improper solution (you can’t use a Jet/ACE back end across anything other than a reliable wired connection with sufficient bandwidth, e.g., 10Mbps and up). Terminal Server deployment of the app would be a solution to the WAN/Internet deployment problem (i.e., keeping a single Jet/ACE database as the central data store for all users).
As to the number of users if 10 concurrent users is locking the back end, then your developer is incompetent (though the problem may actually because by the WAN issues, see above; which is, once again, a problem with the competence of the developer). This is once again not a weakness of Jet/ACE, but a human failure.
Last of all, you seem to be all for upsizing in this scenario. SO AM I. Any Access developer who wouldn’t recommend either that or Terminal Server deployment is NOT COMPETENT.
Whatever you may choose to deploy, that’s up to you. If you’re not competent to know when and how to deploy an application with a Jet/ACE data store, then by all means it’s a good thing that you choose a database that you are not incompetent in using. This does not, however, indicate anything at all about Jet/ACE — it only demonstrates facts about your knowledge and competence.
David – attacking others personally probably isn’t the best way to get your point across here. Take a deep breath, step back, and separate yourself from the product for a moment.
You believe that you can successfully implement solutions with a particular product. That’s great. The rest of us here have been personally burned by this product a number of times. I wish I could trust everyone to make the right implementation decisions with every product, but unfortunately, we’re not all David W. Fenton.
It’s like the gun lobby says – guns don’t kill people, people kill people. However, at the end of the day, we have to put some legislation in place to stop folks from shooting each other to death. I wish I could put a 10-day mandatory waiting period on Microsoft Access use, and force users to interview with people like you and me to determine whether Access is a good fit for them, but unfortunately, that’s not the case.
Here’s a free clue:
I’m not attacking you or anyone else. I’m criticizing their words, which are confused and demonstrate a poor grasp of the issues involved here.
IN THE CONTEXT OF THIS DISCUSSION, which is an effort to completely discredit Jet/ACE as a viable data store for any purpose, anecdotes about particular bad implementations of applications with a Jet/ACE data store are simply not relevant, as your examples don’t demonstrate anything at all wrong with Jet/ACE — they only demonstrate the incompetence of the people who don’t know how to use it.
Now, granted, because Jet/ACE is so easy to use and so accessible, the number of such people is legion. But that’s an advantage for Jet/ACE, not a drawback, just as the fact that people can write crappy non-validating HTML and their web pages still render is an advantage for HTML, and a driver of its widespread success. I doubt you would recommend abandoning HTML just because people can get away with writing crappy HTML, so it’s ludicrous to advocate never using Jet/ACE just because a lot of people don’t know how to use it properly. A lot of people don’t know how to use SQL Server properly, too, but it’s hard enough to use that they don’t get the chance to mis-use it (though I’ve seen some pretty ridiculous stuff in MySQL, for instance).
Last of all, the guns analogy is ludicrous — anyone who uses it in a discussion of databases should be assessed a Godwin penalty for use of massively overdramatic analogies.
David – when you make statements like “if you’re not competent” then you’re attacking people personally. I can almost hear the heavy breathing through the monitor.
All right, David, I’ll call your situation. We did deploy that database through a Terminal Services solution (Citrix) *AFTER* all the issues I mentioned. We proposed trying to upsize it to SQL Server. That was rejected as being “too complicated.” Guess what? Same locking issues. Two users.
Look, you can say “bad developer” all you want to. When I’m sitting in infrastructure, that’s not my call. I don’t get to pick the developers any more than I get to pick the business requirements. But I’m still responsible for making sure that data gets backed up. If it was in SQL Server and somebody did something knuckleheaded and data access was slow, yes, that’s still an issue. But I can still backup and restore the database. And that’s something we were NOT able to do so cleanly with the Jet/ACE solution. And that’s because of an inherent limitation on the architecture of the solution.
So if you ask me, I’m going to, by default, look for a solution other than Jet/ACE. A solution that doesn’t prevent me from completing my expected service even if your “developer” makes bad choices.
“If you’re not competent” is colloquial English for “if one is not competent,” i.e., not a specific person, but any person who happens to meet the condition after the “if”.
If one is implicated by the following clause, and is insulted, then I think you need to grow a thicker skin.
To be honest I’ve never seen locked mdb file. MS Access locks ldb files but leave mdb file unlocked (obviously otherwise users will not be able to commit concurrent changes to the same database).
There are ways to make “safe backup”, but to my experience even straight copying of backend mdb file which is in use never produces corrupted copy starting from Access 2.0
Regarding the frontend in large organizations, it is not Aceess-related problem. MS Access Frontend mdb could be managed the same way as any other peace of software used in organisation – with default settings, customizations, methods of delivery to computer/user etc. Computer management software is of help here, nothing to do with databases.
TS/Citrix/VDA is another option for big guys. Coming MS publishing to SharePoint is new way to implement Access frontend Apps in situations you’ve described.
My experience clearly shows it much easier to deal with MS access frontend if you compare it with “normal apps” – with “dll hell” and incompatible versions of .NET frameworks and JRE.
MS Access frontend is one of easiest things to handle for network admins.
How about modern corporate .NET smartclient app with full Oracle client and 7 prerequisites? 🙂 Is it really smart? And upgrading it on thousands PCs over hundreds of locations? Is it easier?
For databases in particular range Access is still far the best/fastest/cheapest platform, even with JET backend in some cases.
@NZAlex: “To be honest I’ve never seen locked mdb file.”
I’ve never seen a Bugatti Veyron either, but I know they exist. However, I *have* seen locked MDB files repeatedly.
@NZAlex: “My experience clearly shows it much easier to deal with MS access frontend if you compare it with “normal apps” – with “dll hell” and incompatible versions of .NET frameworks and JRE. MS Access frontend is one of easiest things to handle for network admins.”
As opposed to a web app, you’re simply wrong. I’ve gone through the nightmare of trying to deploy MS Access front end files across thousands of disconnected users across the US with very slow bandwidth. I’ve also gone through the delight of sub-second deployments to an entire web farm, instantly fixing a bug affecting users around the world. Comparing MS Access deployments to other thick client projects is short-sighted – compare it to what the rest of the world is migrating toward these days.
Brent Ozar: “As opposed to a web app…”
I see you like to compare incomparable things for better effect 🙂 SQL server with desktop system and fat apps with web apps.
The reality is core business/finance applications will not become web-based soon if will at all. The main reasons are user comfort (mostly locally installed apps faster and more convenient for user) and extreme complexity -large amount of linked information shown to user on the screen.
Example – I currently work for Uni, we have 250 fat apps and only 15 web-based.
Access applications is in ever better position here with potential ability to publish them on Sharepoint framework. This is also way to move Access applications to the cloud according to modern fashion/mania. I have not seen yet how well it works with heavy Access applications, I would be interested to know if somebody tried to publish application with linked subforms and other things like that.
If I compare MS access to other fat apps, it is very easy to deal with from deployment/upgrading point of view, I would say it is trivial even on large number of PC. Just out of interest, how did you deploy/upgrade the Access fronted on those PCs? Is it logon script, CCMS/Altiris or something else?
I am in awe that this thread is still active. How about we all just agree that our personal experience IS reality for each of us individually.
It’s really quite difficult to write an application that locks the back end with two users.
On the other hand, perhaps I’m misinterpreting what you mean by that. As long as anybody is using the app, the back end file will be “locked” (i.e., not safely copyable via the file system because it’s open). I don’t see this as a problem, since when the users close the app or close the forms that are causing the locks on the back end file, they will no longer have the back end open.
For the small number of users you describe there is really no reason why there should be concurrency issues.
Backing up a live MDB file is another issue entirely. There are a number of ways to handle this, and any experienced developer will do what’s necessary to make the file usable by the backup. If they can’t do that, then either they don’t know what they are doing, or the requirements are such that a Jet/ACE back end is not appropriate in the first place, and they should have recommended using something else all along.
This is not a flaw in Jet/ACE, which works fine when the people involved know how to use it and don’t deploy it in environments where it is not suitable. This is a problem with the people using it.
You have every right to choose never to use Jet/ACE in the environments where you have a say in the matter.
You also have the right to criticize Jet/ACE for things that have nothing at all to do with the database engine itself. But nobody should take you seriously when you do that because you are blaming the tools for the mistakes of the workman.
David, the expectation of modern database platforms is to be able to do backup and recovery while the platform is in use. This has trickled down to business users. So when they hear “database” they assume that such functionality is there. It’s not in Jet/ACE. It’s also one of the issues I have with some of the engines for MySQL. Because it’s an expectation, we in infrastructure have to deliver on it. One off solutions kill us.
And that’s where we get to the crux of what you’re proposing. For every Jet/ACE database I want to make sure I can backup (and therefore recover) during the limited backup window my, and every other infrastructure environment, deals with, I have to rely on a developer writing a custom solution. If I am dependent on the end user to close out, it will eventually fail. See the problem here? Now we have multiple one-off solutions for what is expected functionality. You can blame the business user for having wrong expectations, but this is the reality we live in and they don’t care about the blame; they want the functionality. If you’re dealing with Mom and Pop shops, use Jet/ACE to your heart’s content. But if you’re talking enterprise, most folks on the infrastructure side want to see it out, and out in a hurry.
And, yes, it’s acceptable to criticize the tool if it doesn’t perform as expected. The business expectation of backup/recovery for “database” isn’t met by Jet/ACE. That’s where my criticism with it lies. When that’s solved without a custom solution having to be built every time, then I’ll consider it a viable tool.
Also I’d rather the user was empowered to write their own queries, forms, reports etc…..
These empowered users often generate some of the worst performing and most intrusive queries and reports. Thanks no, I’d rather they have to make a request that is properly written and doesn’t impact the system because they did something stupid like SELECT * to only use a couple of columns of multiple nested correlated subqueries because they can’t write a JOIN correctly, or use a CAST/CONVERT on the column side of a query forcing a table scan because they want to find information for a specific date without evaluating time.
Again though – it’s not a proper comparison, even at a developers stand point.
Not every system is likely to be scaleable to warrant SQL Server. That’s like saying don’t write a Windows app because at some point you will have to convert it to the web.
Everyone is aware Access cannot compete with SQL on those terms. It’s never tried to.
You’d be better comparing Access to DBase,Powerbuilder,Paradox,Foxpro,DataEase, Approach, Dataflext etc. Standalone databases which people either love or hate.
But try comparing SQL Server to Oracle or SAP and those developers would say SQL Server is toast.
SAP is an application which runs on top of a database. So of course it’s not a valid comparison. SAP does run on SQL Server, BTW. A large company, I think it’s called Microsoft does this. Do a search on Google/Bing for “Microsoft SAP SQL Server” and you should find it pretty readily. And actually, SQL Server does compete with Oracle now. At what scale are you thinking that it’s toast?
“SELECT * to only use a couple of columns of multiple nested correlated subqueries because they can’t write a JOIN correctly, or use a CAST/CONVERT on”
I’ve not come across any users in my time who have that kind of capability anyway. The most they will do in Access is drag and drop tables in the query designer.
But anyway – at least you’re giving control to them to do things.
“And actually, SQL Server does compete with Oracle now. At what scale are you thinking that it’s toast?”
I’m not saying it’s toast, but Oracle developers do not like it in my experience. And I was comparing SQL to SAP from a data warehousing point.
Paul – again, you don’t even seem to understand what you’re saying. SAP is software that stores data in a database. SAP’s data warehouse stores its data in SQL Server, Oracle, or DB2. I’ve worked with multiterabyte SAP BI warehouses in SQL Server. SAP is not a database.
You do seem to be able to understand this type of distinction.
Why, then, do you respond to an article about Access the database application development tool with criticisms that instead apply only to the Jet/ACE database engine?
Access is not a database.
And Danny wasn’t writing about Jet/ACE.
I would buy this except the original author clouds the issue. See point #8 from the original:
When he says, “Microsoft Access can play with the big boys,” he confuses the argument. If you take Jet/ACE out of the equation, MS Access isn’t playing with the big boys. It’s a front end to the big boys like Oracle. The boys it is playing with are reporting solutions like Crystal Reports, SQL Server Reporting Services, Business Objects, etc. So if you’re going to take Brent to task for confusing the issue, you need to take the original author to task as well.
I think in that passage Danhy’s addressing the frequently-made assertion that Access as database front end can’t scale because of its dependencies on Jet for processing the data. In the A97 time frame, there was no alternative to using ODBC to connect to Oracle, and any Access app’s ODBC interface is managed by Jet.
Danny wasn’t necessarily clear about that, but having been on the receiving end of all the people bad-mouthing Jet, I’m pretty sure that’s what he was talking about.
As to confusing the issue, Danny’s article may have a few unclear passages, but it’s not missing the point from beginning to end, as Brent has done by responding to an article about Access as front-end development tool with a bunch of complaints about mis-use of the Jet/ACE database engine.
Brent – I never said it was a database.
But you’ll find SQL Server is laughed at in the SAP/Oracle community.
As for not understanding – please explain why you compare a desktop database to a client server database.
“But you’ll find SQL Server is laughed at in the SAP/Oracle community.”
I’m not sure where you’re going with that – that’s not a discussion we can prove one way or the other. All it takes is one person to laugh, and your point is valid. I’ll agree that somewhere, someone is laughing at SQL Server right now, just as they’re laughing at global warming, McDonald’s cheeseburgers, and lolcats.
“please explain why you compare a desktop database to a client server database.”
Because SQL Server Express Edition is a great desktop database. Unlike Access, it’s actually free (even to redistribute), supports transactions & integrity, can be easily backed up, supports more data, and has a real upgrade path.
Paul – I think you will find reasoning with Brent to be rather otiose.
For the rest of the audience who didn’t know what otiose meant, it means ineffective or futile. It’ll probably help if I use it in another sentence:
“I’m about to lose weeks of my life. I tried building an application in Access, but our company started growing, and we ran into the 2 gig file size limit in Access. What an otiose way to store data.”
Heh. I was wondering, but i highlighted the word and moved the moused, and Wikilook told me what it meant. I love FF.
“But you’ll find SQL Server is laughed at in the SAP/Oracle community.”
That’s why we have SQL Server Community, and you should see how we laugh at Oracle. Ok, I’m just kidding. 😉
“But you’ll find SQL Server is laughed at in the SAP/Oracle community.”
You will also find people in the SAP/DB2 community that are laughing at the SAP/Oracle guys, and vice versa.
I’m not arguing with him. He’s kind of likeable in a Steve Jobs kind of way.
I’m not knocking SQL Server either – I use it each day in development. But I cut my teeth on MS Access back in the 90’s so have a fondness for it I guess.
I’m not quite sure what “fondness” has to do with recommending a database platform. I cut my teeth on dBase III back in the ’80s. Even if Ashton-Tate was still around, I wouldn’t write an article telling developers to use dBase in new projects when a product like SQL Server Express is available that’s truly free (and not bundled with Office) and has a growth path in case needs exceed what was originally planned.
“Also I’d rather the user was empowered to write their own queries, forms, reports etc…..”
As far as this goes, I have worked in several environemtns where the database schema was friendly enough for the typical end user to write their own queries. I instances like this, see SSRS Report Builder (especially 2.0 and the coming 3.0). Talk about empowering users. Give them a nice report model that makes the database friendly to them and watch them. It is pretty awesome.
“I never felt the camaraderie or the community in SQL Server or .Net newsgroups that was ubiquitous in the Access ones.”
On this point, i can only express dismay. I have only been out in the SQL Community (twitter essentially) for a few months. I have to say that i have felt welcomed from the first moment. I rave to my co-workers and anyone I talk to that the SQL Community (particualry on Twitter where I participate) is vibrant, informative as all get out (I just learend what otiose was), and really really fun.
I also cut my teeth on Access (and also writing SQL on an AS400/I-Series). For what i needed at the time, it was great. However, I would never ever want any mission-critical app running on an Access database. It is just not meant for sharing and high availability like SQL Server is.
Access Fails. Great post. That is all.
Each and every product has its own specialized feature to be demanding for a particular situation or group. For this reason there is so variety of product and none of them are substitute for none of them. We wouldn’t compare between bicycle and airplane though both are vehicle because while I need airplane bicycle won’t work and vice versa.
“I would never ever want any mission-critical app running on an Access database.”
Who would?
If you are developing MS Office solutions – use Access.
I think any developer worth his salt takes time to analyze the requiremnent and provide the solution. If it fits the need use Access, if not then use something else. It’s really not that difficult.
I wouldn’t advise anyone to start developing an monolithic application in Access. I’ve seen it done brilliantly by people with a great deal of experience but then I’ve seen people riding one-wheeled Cycles too. It doesn’t mean I’d actually recommend it to anyone else.
When Access was designed, the idea of decoupling the application from the database to allow applications to grow, and to allow other applications to get to the data hadn’t really hit the mainstream. ODBC, JDBC and ADO changed everything. Sure, you can use Access as a front-end application builder, but you can use a vast number of other more recent tools that have gained from Access’s pioneering approach. You can use Access as a simple database, but when stacked up against even SQLite, it looks pretty moth-eaten. Then there’s PostgeSQL or SQL Express….
As I said before in a previous comment, a lot of the good bits of Access live on in SSMS (and Reporting Services), so it is a pretty easy decision for an Access-enthusiast to make.
Can anyone confirm if reporting services are as good as the reporting facility in MS Access? I looked at it briefly in it’s very first release and it seemed a very cut down version of what we have in Access. Rather like the VB report faciltiy MS put in VB 5 and 6.
Love it or hate it, one cannot argue that the report writer in Access is rather good. Better than Crystal it has been said at times.
Access is very good for created a quick executable that is self-contained. Also, integration with VB.NET is very nice. Access definitely has it’s place.
SQL Server Express should not be overlooked. T-SQL is more robust, it’s connection options are more complete, and it’s free to distribute.
I guess it depends on two factors:
1) What is the goal?
2) Which one is the developer more familiar with?
If the goal is a screen to save addresses to a TABLE and print off a list integrated with Word, i’d use Access. If the goal we’re to read an Excel file and match it’s results with a remote database, i’d use SQL Server Express.
If i needed to report on a few TABLEs, i’d use whichever i was more comfortable with.
You write:
This is false. An MDB/ACCEB file is a data file, not an executable. It can contain tables storing objects that include VBA code, which is compiled to p-code, but it’s not an executable. An Access application can only be run if you have full Access or the Access runtime installed.
Paul – about the reporting facilities, that’s a good question. My blog’s main audience is database administrators, not reporting folks, so we probably won’t get as good of an answer here.
Access was a good report writer and very comparable to SSRS 2000. A few things were still easier to do in Access at that time. But since then there have been many improvements to SSRS that makes it preferred over Access. The charting controls, tablix, and functions make it far superior to Access
With Report Builder 2.0, business users now have the ability to create robust reports that are comparable to anything a developer can put together. Plus, add in the security, subscriptions, and export capabilities and Access can’t hold a match to SSRS.
Brian Tkatch
Exactly
As Someone who is suffering from an “Enterprise” deployment of an Access app I can say without hesitation KILL IT WITH FIRE!
I think that we have all seen enough rubbish Access applications to know that we would prefer people didn’t use it for development.
HOWEVER…
If you want a simple system for 2-4 users, typically to update a main fact type table and a few related dimension type tables.
1 Stick the data in SQL with role based security. 0.5 days.
2 Use an Access Data Project for SQL 2000, regrettably linked tables for later. 0 days
3 Put a bound multiple record view screen on each table to update. 1 hr per table.
4 Write a filtered search screen with Pivot view on the main table.
5 Have a simple report that is based on the form’s recordsetclone to use the same filtering
You have a workable, manageable system in about a week.
In my experience, getting a nice .NET data screen takes about a man day.
Reporting Services is OK, but filters and context awareness are horrible.
Dan – totally agreed. I’m not saying Access isn’t good for front end development. I couldn’t care less what people use on the front end. My problem with Access is its shortcomings as a database.
Well that’s alright then ! we agree. Unfortunately, for most people Access is the entire suite so attack “Access” and you attack them, as you see below ! By the way, when leaving a reply (in Chrome, not sure about IE) you get very funny behaviour if you tab from the Name field, it seems to cycle the individual blog posts ?
You write:
Are you really this obtuse?
Danny’s article, the one you’ve taken all this time to criticize, was about ACCESS AS FRONT-END DEVELOPMENT TOOL.
Again, Access is not a database. Access includes a default database engine, Jet/ACE, that is useful for small-scale data storage needs.
Your dislike of the mis-use of Jet/ACE seems to have put you in a state of mind such that Danny’s article (which was on the subject of Access as development tool) set you off on a tirade completely unrelated to anything that Danny wrote.
Would it make you happy if we talked about all the programming weaknesses and lack of functionality of the no-longer-in-development VBA then? That’s a whole other area of problems for Access and since MS has halted development on VBA it’s never going to get any better. Can we compare Visual Basic to Delphi? Would that make you happy?
Here, Here! It wasn’t too hard to figure out that the original author has tinted ocular enhancers. In fact the mention of Access, and the case of hives that immediately appeared on my arms upon seeing the headline, are the only reasons I stopped to read it in the first place.
You, sir, are on the money, and well done.
I’ve been working with SQL Server and Access as a software developer for about the past eight years.
From the development side, I despise Access. I hate it with a fiery passion. I just had to write an application that, for various political reasons outside of my control, uses Access to store its data locally with no network present (meant primarily for laptop usage in the field where there is no network). It was one of the most painful experiences I’ve ever had.
While I’m not terribly much of a fan of working in .NET in general, it’s what I do day-to-day, and it’s amazing how well SQL Server works with it all. It’s nearly seamless. Working with Access is like pulling teeth through a hose while blindfolded. If that makes no sense, it’s because a lot of the hoops I have to jump through to get Access working right make no sense either.
Of course MS Access is a client server, enterprise database:
1) Find a spot on an enterprise server
2) Copy .mdb onto said server
3) Point MS Access client to said .mdb
4) Send company-wide e-mail telling everyone to use it.
Ok, my [Snark] tag was removed. Consider it appended.
Brent,
Putting on my business hat, an Access app that has many users and does not scale, that app is a smashing success. It does what people want, that is why people use it.
More to the point, because the scenario when an Access app has grown and needs to migrate to a more scalable platform – that shows a proven way to succeed in business. Have someone who knows what customers/users want, and have them build a solution with a technology they are most comfortable with. Later, when there is money, we can hire someone to upgrade.
The key point is to have someone who knows what the business needs. The choice of technologies is less important at this stage.
Such a project, done in Excel or Access, may have a better chance to get to profitability than a project done by a highly skilled team of software engineers who fail to communicate with the business well enough and build a perfect solution, but not for the problems they needed to solve.
Alex – is this a Freudian slip?
“an Access app that has many users and does not scale, that app is a smashing success.”
If the app has many users and it can’t scale, that’s a smashing failure. And that’s what I see over and over with data stored in Access.
It’s not a typo. Such an app is a VICTIM of its own success, in that it has become so popular that it has outgrown the original design parameters.
@Alex K
“an Access app that has many users and does not scale, that app is a smashing success.”
huh? Tell me that’s just a typo?! Tell me!
“More to the point, because the scenario when an Access app has grown and needs to migrate to a more scalable platform – that shows a proven way to succeed in business.”
Wrong! You scale your apps in anticipation of growth! You’re doing it wrong when you just scale your apps when the actual need is already at hand. You will miss opportunities that way. If you foresee that an app needs to scale in the future, then don’t build that app with Access.
In conclusion, Access just doesn’t work in *real*, forward-looking companies!
No, Brent, as a solution to a business problem – quite likely a smashing success. It has many users, because it does what users want, it has the features which are needed – so a very difficult problem has already been solved.
Like it or not, doing it quickly with a simple tool is a well proven way to success in startups. Doing it right the first time, in the context of a startup, may have a much higher chance to fail. Doing it right is slower to develop, slower to change, more expensive. We may run out of money before we finally get it right.
We geeks might not like it, but in startups in most cases figuring out what our users want is way the most difficult problem.
Alex – we’re going to have to agree to disagree.
If you believe that a situation with lots of users on an app that can’t scale is a success, then I understand why you’re a big fan of Microsoft Access. That’s exactly the situation it produces over and over again.
If you consider that a success, you’ll love Access. I don’t – so I don’t.
I bet John Rogers hasn’t seen this yet (Brent O) making fun of Access) – http://benchmarkitconsulting.com/colin-stasiuk/2009/08/27/i-love-getting-stuff-in-the-mail-thanks-brento-and-quest-crew/
Yes I’ve seen it Marlon, thank you for the link, I think this sums it up nicely a quote from that website:
“Cute. How about, “You know #uran arrogant self-important DBA if you dis a technology without context.”
Like I said in an earlier post it is futile to try and reason with someone who can’t see beyond the only tool he knows how to use, and thinks that it is the end all be all solution for everything. To me, whether it be Access, SQL Server or some other development platform, they are all just tools that may or may not be applicable to certain development scenarios – like others have noted. Just because Brent can’t think outside his own little one trick world…is his problem not mine. I’ve been in this business long enough to see many zealots like Brent come and go.
Hi John
I queried the same points that you have with Brent further up and he clarified that he is complaining about Access AS A DATA STORAGE MECHANISM.
Which I am sure you will agree has gone downhill since Jet 1.1 – I have seen terminally corrupted MDB’s, lost mdw’s, … SQL gives you transaction log and integrated security for free !
Put the data into SQL Server (paid, or SQL Express) , link Access to it and you have the cheapest and most potent development environment around.
(Or at least you did til Microsoft needlessly overcomplicated Access since 2000 = )! All you have to do on the front end is do some trivial vba to dynamically change form recordsets so they don’t open with all records loaded and it’s dead easy.
I probably do two of these 5-10 man day jobs a year and everyone is happy, the client’s IT department have as they see it killed an Access db (or brought it under control) and the business now have an improved version of their core app with extra lightness added.
John:
“it is futile to try and reason with someone who can’t see beyond the only tool he knows how to use”
I got my start on Microsoft Access. After running repeatedly into the 2gb limits due to my apps becoming popular, I ditched it and moved on to SQL Server. No, I haven’t kept my Access skills sharp – because Access failed me ten years ago, and as far as I’ve heard, there’s still a 2gb limit on file size, right? Is backup any easier than it was? Any transaction support yet in Jet?
If you were using the only-tool argument in favor of me learning Oracle, MySQL, Postgres, etc, I could see it, but Access with its 2gb file size limit? That’s like saying a modern mechanic isn’t a real mechanic until he knows the Pinto forwards and backwards. I’m not slamming the Pinto’s age – I’m slamming the fact that it explodes when you rear-end it. Access’s data limitations aren’t quite that dangerous, thank goodness, or we’d all have burn scars.
“I got my start on Microsoft Access. After running repeatedly into the 2gb limits due to my apps becoming popular, I ditched it and moved on to SQL Server.”
Then I would have to say that you were using the wrong tool for the job. Part of my job as a developer is to determine what the client needs and provide them with the best solution that not only meets their needs for the present but has a pathway to the future. To abandon a tool and then trash it because you didn’t know how to use it in its proper context is IMHO narrow-minded. I have never said Access was the end all or be all for development I have said that it is just another tool as is SQL Server. I wouldn’t use a sledge hammer for a job when a hammer would do the job.
Your analogy with the Pinto is incorrect the Pinto is the vehicle I am talking about the tool. If a modern mechanic doesn’t know how to use the tools available to him or their limitations, then yes he is not a real mechanic.
If you choose not to use Access because if doesn’t fit the work you are doing that is fine, but to trash it because others are using it successfully because it fits their particular job that is being narrow-minded to say the least.
As to your other questions: You can work around the 2gb data limit, however I wouldn’t recommend it as a standard practice; about backup I don’t know what kind of problems you were having backing up Access, but backups aren’t an issue, just as you would create a job in SQL you can create a backup job within the confines of the tools available to do so for Access (is it built in no, but very easily doable); and no transaction support isn’t built into Jet, but I am sure you knew that, but again I reiterate that Access is still a viable tool for certain jobs.
Your example of a developer choosing to use Access is not reflective of most real-world uses of Access. In most real-world uses of Access, a company buys Microsoft Office because… well, that’s just what everyone does. Director takes job. Director sees Access on machines. Finds someone in his department who claims to know Access. Tells them to make X with Access. And that’s how Access gets used for things. Most Access development has no IT involvement whatsoever.
I’ve seen this happen in multi-billion-dollar companies. Crazy me, I actually even tried to draw up a project analysis when one of these things fell into my lap, including a phase called “determine tools to use”. The Director with the six-digit salary asked me to explain that one. I did. “But we already have Access.”
“But it might not be the right tool because….”
“Jim did something like this in Access.”
“I know, but,,,,”
“Do you know how to use Access?”
“Yes.”
“Then do it with Access.”
Translation: he sure as ##*@& wasn’t getting IT involved, purchase orders submitted, and political boundary wars begun because if I wanted to bring in a non “approved” language or tool into the company that would involve two months of meetings and debate. He just wanted his *#&$&@ Access app like Jim always does for him.
It got to the point where the two people “in charge” of the project wanted a system that rated certain things, but they hadn’t yet decided how it should be rated: general letter grade, detailed score, etc. Purchasing decisions would be based on this grade. I wanted to analyse data and create a model to test, but that would take time and again, they weren’t used to people developing in Access wanting to take time, let alone due these planning analyses. I asked if I could talk to the end users who would be using the application.
Long pause: “That’s not really the way we do things, so that won’t be possible.”
I then got questions about how long it would take. I pointed out that they still hadn’t decided what they wanted it to do yet.
“But can’t you start doing it anyway?”
“Huh?”
“And then we can figure out the scoring later.”
Apparently “doing it” means put up a bunch of RAD forms and buttons on the screen without any idea of what the code will do yet. Of course, if they decided on the red/green idea rather than the detailed score I’d waste a lot of time, or have to do the code over again if it was the other way around.
I told my six-figure boss, “Well, we could design the program AFTER I write the program, but that’s not the way things are generally done.”
I was then told that users would not wait more than five seconds for an answer, and I explained it took longer than that to get the data from our single AS/400 that supported 681 retail stores plus corporate. It was then suggested can’t I just download data every week (forever), crunch it all and then put it in a table and have the app use that?
“No.”
“But that’s what Jim does with our dashboard.”
Yes, an Access “dashboard” – in which Jim downloads data from the As/400 once a month and processes it, which means the “dashboard” might be as much as one month out of date – longer if Jim goes on vacation. Jim knew this was insane, but he had a mortgage and a kid on the way.
Anyway, about a week later after more of this I told HR at 5PM I quit effective tomorrow.
But – that’s how Access applications are developed in the real world, in major companies (including one with three B’s in its name). Non-programmer bosses tell their non-programmer employees to do something with Access because they can’t or won’t get IT involved with it or need it fast (as opposed to right). You end up with “dashboards” that are really rear-view mirrors, and non-developers asking if we could decide what the program will do after we make it, and these people often make six figure salaries.
THAT’S why many people hate Access. It enables pointy-haired bosses to turn people who may or may not belong anywhere near a keyboard into database developers. No real developer has a say in the process, and often IT doesn’t either. In my example, IT simply declared Access “unsupported”. And I won’t even get into the fact that this billion-dollar firm used Access 97 until 2007.
Me giving the link is just a joke, John. I bet you don’t know Brent O. He is a very respected man in the SQL Server Community. Your last comment seems to have gone over board. Brent is a well-loved community guy. If you’ve searched his name on the internet, chances are you might have seen who this guy really is and what he has done for the SQL Server Community. He is a fun guy. C’mon these are just arguments over why or why not use Access in app development. Let us not throw invectives. No ad hominem, please.
These are very confused arguments in the article that prompted this rant was about Access as database application development tool, but all the criticisms of “Access” are against mis-use of the Jet/ACE database engine in contexts here it’s not appropriate.
The target is not Access.
The target really shouldn’t be Jet/ACE, either.
The target should be the developers who choose the wrong tool for the job.
Wether you start small and grow, or the plan starts with a big solution up front (with the dollars).In the end, it is about the success of the project!
No one would suggest that you run a large corporate, multi-state application on Access, you may not even use SQL server, who knows.
I believe you have to keep an open mind when developing the application – front end, user functionality, reporting and NOT just the DBA.
The true mark of the developer is the same – on time, in budget and working…
I have worked in 4 organisations that had this Access vs SQL Server issue. The problem as I saw was hardly a technical one. Most of these companies were making the difficult transition from a small business where everyone knew everything and reports were easily custom made for whoever wanted them to a larger business where responsibilites are more defined and there had to be some kind of regulations on querying and reporting data. Individuals esp managers had a very hard time understanding that and letting go of the control they had around their pet Access databases.I must admit as a SQL DBA i share the technical peeves around access and the parellel usage with sql server was a dba’s nightmare. That said I dont’ particularly ‘hate Access’,atleast not half as much I hate individuals who are control freaks and resist change in all ways possible.
This article is quite misguided. Danny Lesandrini’s article was about Access as a database application development tool. The criticisms in this article, despite quoting Danny’s points one by one, are all about mis-use of Jet/ACE, Access’s default database engine.
Take item 1:
1. The price is right.
You offer SQL Server Express as the alternative to “Access.” It is *not* an alternative to Access, but to Jet/ACE.
Indeed, Jet itself is free, since it’s a component of Windows (starting with Windows 2000). It’s not the latest version of Jet (the one shipped with A2007, called ACE), but it’s Jet 4. So, you can use Jet 4 for free without needing to download anything as long as you’re running on Windows.
More importantly, SQL Server Express doesn’t address the application development part of the equation. No, Access is not free, but if you acquire one copy, you can build an app that can run in the Access runtime which, starting with A2007, you can download and distribute for free. That’s not *quite* free, but it’s pretty close.
3. Plethora of books and training materials
Maybe there are twice as many books on Access because Access covers more bases than SQL Server — it’s not just a database engine, but a full database application development tool. There’s simply more to cover.
4. Vast online community to offer free help
On exactly what basis can you claim that Twitter is authoritative on this question? What a crazy non sequitur!
I’ve been participating in Access newsgroups since 1996, long before Twitter even existed. Most of the heavyweight Access gurus aren’t even on Twitter (and have no interest — I tried to get them interested, in fact, and they didn’t see the point). You’re like the guy who is looking for his car keys under the street light because that’s where the light is even though he lost his keys somewhere else. You won’t see the depth and breadth of the Access support forums if you don’t look in the right place.
Let’s do a comparison of the Microsoft public newsgroups. I just connected to news.microsoft.com and got the article counts for all the Access and SQL Server groups. Here are the results:
There are only 2/3s as many posts in the SQL Server groups as in the Access groups. And that’s not even counting a major non-Microsoft newsgroup, comp.databases.ms-access, and significant web forums like Utteraccess.com.
5. Subforms
You don’t really have anything to say about Danny’s actual point, but you do offer this non sequitur:
Uh, no professional Access developer does it any differently, with the code and data separated from each other. This demonstrates your ignorance of Access developer best practices, and probably indicates that you’re really not equipped to be criticizing Access as development platform.
8. Integration with “real” databases.
You ask:
Most professional Access developers do exactly that, when it’s warranted by the requirements of the application in question.
You continue:
Yes, you now seem to be getting the point — an Access front end scales according to the capabilities of the back-end database engine, which can be anything you choose. You seem to be able to read Danny’s article and quote each item, and yet you still fail to notice that the whole article is about Access as development tool, not as data store.
All of your criticisms are of Jet/ACE, not of Access.
9. New features in 2010
You write:
If you knew anything about recent versions of Access, this would not puzzle you at all.
Starting with Access 2007 (and ramping up further in A2010), Microsoft is majorly revamping macros. In A2010, they have error checking and branching, which means the reason they were to be avoided in the past are no longer applicable. If you had anything other than passing knowledge of Access as development tool, you would already know this.
But, of course, that’s not your focus — you don’t care about Access as development tool, you just care about mis-use of Jet/ACE as data store for apps where it’s inappropriate.
So, perhaps you should stop commenting about the things you are neither concerned nor knowledgable about?
10. The Play-Doh factor
You ask:
Every developer chooses his tools according to the task at hand. An online store would be an inappropriate task for Jet/ACE (Access couldn’t do it at all in currently-released versions; A2010 could if you hosted it on Sharepoint, and then you could use any back end you liked, including SQL Server).
You then ask:
You’re just repeating yourself — that’s the same question restated. Choose the appropriate tools. If you choose the wrong ones, you’ll end up with a mess. The problem there is not the tool, but YOU, for making the wrong choice!
And your entire article is wrong-headed in that you’re attacking the wrong target, and completely missing the point of Danny’s article.
As a consequence, you really do look quite foolish.
Let me do a rebuttal on #4. You throw out Twitter saying that’s not a valid comparison and then cite NNTP newsgroups, which, quite frankly, there are quite a few SQL Server types who absolutely abhor them (Brent being one of them). So if we can throw out Twitter because the Access types don’t use it, by that logic you’ll have to throw out NNTP because a lot of the big SQL Server types rarely step foot in those newsgroups. Hmmm, that doesn’t work so well for your argument, does it? You also neglect the community sites that are specific to those technologies that are out there. Sites like sqlservercentral.com, sql-server-performance.com, sqlteam.com, etc. There’s a whole lot of community interaction going on in those places that you’re very narrow criteria naturally exclude.
And don’t get me wrong, I’m not saying the numbers necessarily prove anything and I’m not saying you must throw out the NNTP newsgroups. My point is if we want to, we generally can find some small sample of data that “proves” our point when in reality the larger body of evidence doesn’t necessarily support it. What you’re doing here is the same thing the folks that published that study linking autism to vaccines did. Relatively small and chosen sample size to justify one’s position. It was wrong in the autism study and that should never have made a scientific journal. It’s wrong, here, too.
I didn’t say to throw out Twitter. I said that you can’t say anything useful about the Access support forums if you ignore the forums where the Access folks actually congegrate. I didn’t just cite Usenet groups, either.
My intention was not to “prove” that there’s more resources for Access support, but simply to show that you can’t assess the extent of support without actually making an effort to determine where people actually engage in discussion.
While my comparative Usenet numbers do tend to make it look like I was arguing that Access has more going on than SQL Server, that was not really my aim. I was only showing that there was plenty of information contrary to Brent’s meager and laughable Twitter stats. Brent was attempting to refute Danny, and what he wrote was really a joke.
I merely argued that you can’t look under the street lamp and then claim there are no car keys there — I wasn’t pointing out where the car keys actually are.
David Fenton – wow, 15 comments on one post in one night, with no one else interacting with you! That’s an all-time record in here. The numbers are still trickling in, but it looks like you might have even broken the 2gb barrier just with your comments alone!
I won’t refute your points blow-by-blow because you lost me when you didn’t agree about the very first point, price. You said that I didn’t address the development side of Access:
http://www.microsoft.com/exPress/
Microsoft’s Express suite includes not just SQL Server for free, but Visual Studio Express too. With one tool, you can develop applications for Windows or the web. And it’s free. Did I mention that it’s free?
You said $400 is “not *quite* free, but it’s pretty close.”
I expect that means you’ll be buying the drinks when we meet, because $400 is nowhere close to free in my book. That was where I stopped reading, and I just approved the rest of your comments to let the public read and decide.
You now cite Visual Studio Express as the free answer to Access as development tool.
In your original post, you cited SQL Server Express, mentioning nothing about any of the free VS tools. Your citaiton was this link:
http://sqlserverpedia.com/wiki/SQL-Server-Download
…and that link mentions nothing about VS Express.
Secondly, you claim *I* said:
…but I didn’t say anything of the sort. I said that if you have one development copy of Access (which if you buy it separately costs less than $200 depending on what price you qualify for), that’s your only expense. In terms of the marginal cost per desktop of distributing an Access app with the A2007 runtime, that’s a pretty low cost, not even close to the $400 price (which is the price for the whole Office suite).
Perhaps it’s good that you stopped reading after that since it’s quite clear that your reading comprehension is so poor that you would have been confused by most of it anyway.
Are we saying everything has to be scaled up?
Surely not.
I keep my accounts in Access…I know from the outset the data requirements.
There is no way I am going to install some bloated software such as SQL Server onto my laptop just so I can have greater storage.
What’s the point?
Paul – great question. Usually developers install their software on their own laptop. I assume that you would be developing more than just your list of accounts, right? You probably have clients that you’re building apps for, and you need to test those apps. That’s why you would already have SQL Server on your laptop, and thus, why NOT use it? It doesn’t cost you extra, and you can keep your skills fresh on that technology so that you learn more that your clients can use.
This is the same reason I got away from using Access. Once I realized it doesn’t scale well (and I don’t just mean the data storage there), I said, “Why aren’t I developing all of my projects in something that DOES scale? That way, when I learn a new technique or build a new piece of slick code, I can reuse it across all of my projects.”
You write:
This is interesting. It’s the first time you’ve actually clearly made this assertion. I’d like to see you provide some actual support for it, rather than just have it be a throwaway comment for which you take little responsibility.
This is not the first time I’ve said this. Scrollup through the comments and I point out that multiple developers can’t easily work on the same Access code simultaneously, and that it doesn’t work well with version control.
You’ve switched your use of “scale well” from “performs well under higher user population/higher data volume” to “works well as development tool with large development teams.” This is just another example of your apparent inability to maintain clean distinctions in your criticism of Access — you shift gears without explaining the context. Perhaps you’re just a bad writer.
Anyway, I would say that Access as front end is probably most appropriate for projects where there’s a single developer at a time. I’ve never worked regularly in any other environment and recognize that code management with multiple developers is a challenge (though not at all impossible).
I would cede the argument on the issue of large development teams.
But I don’t consider that a significant concession, since I think that doesn’t apply to any significant portion of the projects for which an Access front end is even close to being appropriate.
Brent
I have thought about this in the past.
For instance, with Access I know how easy it is to despatch an MDB,ACCDE file to clients, literally by email if I have to.
With SQL Server, Express or otherwise, I don’t think it is so easy. Then there would be configuration issues at the other end. I’d also be facing possible connection string issues by distribiting .Net or ASP front ends.
Maybe I am missing something or have not delved into SQL enough…maybe distribution is as easy as MS Access.
Paul – yep, if you want to send an all-new database to your clients, you can give them the mdf & ldf files, and they’re off to the races. Nothing needs to change.
However, there’s a big caveat with this solution – whether it’s Access or SQL Server. If you give the client a new file, you just overwrote their data. You probably wouldn’t want to do this with Access unless the mdb only contained the development code (forms, reports, etc) and not the data.
Therefore, if you’re using Access for the dev code (forms/reports/etc) and SQL Server Express Edition for the data storage (which gives you the transaction support, backups, >2gb data, etc) then you’re even better off than if you were using Access to store the data. You can deploy new MDB files with your new forms & reports without worrying about overwriting their data.
Brent
Are there any licence restrictions with SQL Express? I’m assuming this version can be used commercially for unlimited applications.
Paul
You got it – commercial use is welcome. Lots of vendors bundle Express Edition in with their applications because you can install it silently and just start it when your app runs.
SQL Server Express is limited to 4GBs.
Given all your harping on the Jet/ACE 2GB limit, I take that 4GBs is sufficient for you all your apps.
One of the differences is if I need to scale up, I can take that SQL Server Express database, connect it to a commercial edition of SQL Server, and I’m good to go. No data conversion required. That can’t be said for Jet/ACE.
If you design your Access app properly, it will upsize to SQL Server pretty easily, too.
I’m not making any argument that Jet/ACE is better than SQL Server, because that’s a question with no meaning outside the context of a particular application. You choose your tools based on the requirements of the particular situation.
A Jet/ACE back end is going to be just fine for a large number of small applications. For apps with the potential to grow, an experienced Access developer will design with upsizing in mind. Some developers may choose SQL Server Express from the start, but the determining factors are going to vary depending on the requirements in the particular circumstances (and the resources the client has to invest in support and maintenance).
Harping on the Jet 2GB limitation as Brent has done and then promoting an alternative with a 4GB limitation is yet another case of Brent making himself look foolish. If your app bumps up against the 2GB limitation, it’s pretty likely to hit the 4GB limit, too, and once you’ve done that, SQL Server is no longer a free alternative and you have to look at something like MySQL or PostgreSQL.
David – if you think I look foolish by promoting a database engine that can grow beyond 2gb, 4gb, and indeed, beyond multiple terabytes, then again, I can see why Access looks good to you. If you’re okay with Access’s limitations, then you’ll like Access. I’m not okay with those limitations, and that’s why I focused my post on its problems. You’ll notice that the commenters chimed in because they’ve also banged up against Access’s limits.
David- like KBK said, the beauty of SQL Server is that it scales up simply with licensing. You need to store more than 4 gigs, you upgrade to the next license up. There’s no higher license for Access – you’re just SOL.
But this wrecks your case for FREE.
It also ignores the fact that an experienced Access developer who deploys an app with a Jet/ACE back end will likely design with upsizing in mind (i.e., retrieving limited data sets, avoiding Access-specific techniques that are inefficient with a server back end, etc.).
I’ve never had an app upsized that was nearly as much work to get running well as I had expected. This has always amazed me, to be frank. Despite the fact that I’ve made a practice of designing all my apps for upsizing starting in about 1998, I still know that there are lots of little potential gotchas when switching to a server back end. But Jet/ODBC manages to smooth over an awful lot of the problems.
This doesn’t mean I don’t still move things server-side where appropriate (there are good reasons for that besides mere front-end performance), but it does mean that it’s almost always less work than I allocate for the project. Maybe I’m overly pessimistic, or maybe I haven’t done enough upsizing projects to get a feel for estimating it.
But I think you vastly overstate the difficulties of upsizing a well-designed Access app.
Let’s not bother to argue over poorly-designed ones, shall we?
I would tend to disagree on the difference between the 2 GB and 4 GB distinction. A classic example is WSUS. It would constantly butt up against the 2 GB restriction on MSDE, so there was a specially licensed version that allowed over 2 GB. I don’t think I’ve ever seen it bust 4 GB unless you’ve gotten to a point where you really need a more robust patching platform like System Center or Shavlik.
And the point is that if you do hit that limitation, with Jet/ACE you’ve now got to covert structures and import data. With SQL Server Express that’s not the case. If you’re dealing with more than 4 GB of data, you likely need a more robust RDBMS platform in any case, so you’ve likely already shelled out for SQL Server or a version of it (like what comes with Small Business Server). In this case, I stop Express, move my databases, attach them, and after repointing my app, I’m back in business. I don’t have this sort of migration path with Jet/ACE.
(this is in reply to a comment by Brent that has no REPLY link, so I’m posting it here)
Brent wrote:
Classic.
You look foolish for criticizing Access when you mean to criticize Jet/ACE.
You look foolish for writing inconsistent posts, where in one place you say one thing, and in another you completely contradict what you’ve written elsewhere.
I have to conclude that you’re really not a very good writer, because there’s nothing wrong with your basic position, that for the apps you work on, SQL Server is the appropriate database. How could anyone dispute you on that? I’m certainly not (you’ll look in vain for any place that I’ve told you that you shouldn’t use SQL Server).
But when you base an argument in part on the 2GB limitation of Jet/ACE and then promote a database with a 4GB limitation to replace it, you just look foolish. Yes, of course, it can be a good choice and if you hit the 4GB limit it’s easy to move to the licensed version. But then you’ve blown away your “it’s free!” argument — one part of your argument contradicts another part of it.
It’s the inconsistency that makes you look silly.
Worse still, your apparent unwillingness to acknowledge that you’ve overstated your case and repeatedly written sloppily makes it easy to dismiss your basic points. I’m not doing that. I didn’t disagree with you in the first place on the utility of SQL Server for particular applications. Nobody with any sense would.
The problem with this entire discussion is your categorical dismissal of Access and Jet/ACE for any purpose whatsoever. That would be as silly as me dismissing SQL Server for any purpose whatsoever.
The categoricals are all on one side, and to me you’re damaging the comity of the database development community by making it into an adversarial relationship. All the Access developers I know keep SQL Server in their toolbox and use it when it’s appropriate. There’s no us-versus-them on the part of Access developers, except when the Access bigots attack Access/Jet/ACE, and it only becomes adversarial then because most of the Access bigots are arguing in vast ignorance of Access/Jet/ACE.
You’ve made things a lot worse among developers who ought to work together.
Thanks a lot.
I don’t agree that I’ve been inconsistent. I’m consistently against people learning Access. The database doesn’t have a scalability path – you can’t get an Access database bigger than 2gb. You *CAN* get a SQL database bigger than 2gb. SQL is completely free up to 4gb. If you think that by charging when the database hits 4gb, somehow I’m being inconsistent, I’m not. If all you need is Access-level capabilities, SQL Server Express Edition stores twice as much, and it’s free.
Does Access also have forms & reports? Absolutely – but so does Visual Studio Express, and that’s free. Plus, just like you can start small and grow your skills with SQL Server Express, you can also grow your skills with Visual Studio to build hugely successful and scalable apps – something you can’t do with Access. You can’t take your Access skills, walk into Ebay, and start working on their systems. They don’t translate at all. However, if you get started with SQL Server Express Edition and Visual Studio Express Edition, you have an upgrade path – not just for your apps, but for your skills as well.
I write a lot about careers here, and coach my readers on how to grow their skills beyond Mom & Pop shops. I want them to have the best routes available to them. I want them to be able to jump into some aggressively scaling apps, just like I did. If I’d have limited myself to Access, I never would have been able to sit down with the guys at StackOverflow and say, “Lemme help you grow this thing.” Every minute of my life that I spent on Access, I can’t get back, and it doesn’t help me in the enterprise world.
By looking at your web site, I can see that you’ve made a career out of supporting small businesses by yourself. That’s cool – there’s absolutely nothing wrong with that, and I applaud your skills and capabilities. I bet you’re more business-savvy than 9 out of 10 developers. That’s great. But if a developer is starting work today with databases, I would argue that the time they spend learning Access would be better spent learning SQL Server Express Edition and Visual Studio Express. It’s got one heck of a strong future – dare I say, it rocks. The original article put way, way too strong an emphasis on Access rocking for developers, and I just don’t feel that’s the case. I haven’t heard anything from you to make me feel otherwise.
You write:
Access the database application development tool or Jet/ACE? Obviously, you mean the latter, but that’s not what you’re saying. If you mean Jet/ACE, then say that. Don’t confuse the issue by saying Access when you mean only the database engine. If you were able to maintain that distinction in your head and in your writing, you wouldn’t have gone off on Danny’s article at all, since it wasn’t about Jet/ACE in any way, shape or form.
None of my clients has data exceeding 1GB, including the ones whose data is stored in SQL Server. If their Jet/ACE data files started approaching 1GB, I’d be upsizing them to SQL Server, because I don’t want to get anywhere close to the 2GB limit.
But a vast number of real-world business applications don’t need data storage capacity above 1GB, let along beyond 4GBs. This is not to say there aren’t plenty that do, but they aren’t found in the majority of businesses.
…at which point it’s no longer free, and your argument about SQL Server being free falls down. That’s an inconsistency — you argue one way when it suits you and change the subject when the flaws of that argument are pointed out.
Well, then, it’s a good thing I didn’t say that. I’ve pointed out quite clearly where you’ve been inconsistent, and at no point did I say that was one of them.
But it also brings a number of support and maintenance issues. Consider backup. While it’s easy to set it up, it requires something beyond what backing up a Jet/ACE data file requires. Sure, in a large organization that’s no big deal because the infrastructure and expertise is available.
In a small business, or maybe more clearly, what might be called “micro-business” (the “small business” category is often considered to apply to businesses up to 500 employees), with fewer than 20 employees, or even fewer than 5 (the majority of my clients, BTW), this infrastructure and expertise may not be there. Many of my clients don’t even have a dedicated file server.
A recent scenario occured where backups were a problem. The main Access app used a SQL Server back end, and it was being backed up with Veritas backup. Unfortunately, Veritas backup was causing the server to crash, and any time the server was restarted, the backup service failed to load. So, the sysadmin decided to kill Veritas and replaced it with NT Backup. This would have backed up the SQL Server data files if they’d been stored on one of the data partitions, but they weren’t (I didn’t install this SQL Server instance, so I’m not responsible!), so their SQL Server wasn’t getting backed up at all.
Now, whose fault was this?
Well, it was the sysadmin’s fault because he made changes to the backup system without considering all the issues involved.
I discovered it only when I thought I might need to revert the database to an earlier state after a user claimed to have accidentally deleted a large batch of records (the user was mistaken). It was only in trying to locate the SQL Server backups that I found out there weren’t any.
But the Jet MDBs on their file server that were used by other Access apps were all getting backed up properly.
Now, I’m not claiming this is some flaw in SQL Server, just that it’s more complicated to make sure it’s backed up properly than with a simple file-based data store. Somebody fell down on the job and perhaps I should have been regularly checking that the SQL Server was getting backed up (and that the backups were valid!).
My point is not to apportion blame but simply to point out that switching to a SQL Server back end is not something that you can do without there being additional maintenance and administrative tasks that have to be seen to by somebody. In the context of the micro-businesses I work for, when there is no actual need for the additional potential data storage (most of my clients’ back-end data files don’t even approach 50MBs) and no need for the security and no need for the enhanced concurrency, why would someone recommend SQL Server when it brings with it additional responsibilities and configuration dependencies while offering no features that actually benefit the client right now? “Someday you may have more data” or “Someday you may have 30 users” or “Someday you may need bullet-proof security” are not really justifications for “Even though you don’t need any of these things right now, let’s introduce more potential points of failure in your maintenance and adminstrative routines.” Sure, whatever you say, Mr. Database Expert.
Uh, this is the MAIN POINT. And it’s substantially easier to use than VS, whether for a novice or for a trained programmer.
This is an argument that you have not previously made. It’s a reasonable one — one I disagree with, but it’s certainly rational.
Why you feel the need to make it, I don’t know. I don’t know that anyone has suggested that anybody should be learning Access as a path towards becoming an enterprise-level programmer or DBA.
In other words, you demolish an argument that hasn’t been made.
Kudos for that.
You personally likely can’t walk into eBay and start working on their systems, either.
On the contrary — one can learn quite a bit about proper schema design and normalization and optimizing SQL and a number of other things using Access, even with nothing more than Jet/ACE as your data store. Indeed, I see tons and tons of people using server databases who really don’t have a freaking clue about the basics of structuring data properly — people who don’t know 1/10th of what I know (and I’m only a lowly Access developer!).
But really, that’s just evidence of Sturgeon’s Law, and nobody ever looked very smart by criticizing the 90% of crap that everybody knows is out there.
I know quite a few developers who today are full-fledged SQL Server DBAs and .NET programmers who cut their teeth on plain old Access.
But, again, you’re arguing against something that nobody has suggested, i.e., that if you want a career path in high-level database development you should start with Access. Danny didn’t make this argument and nobody’s made it in any of the comments here, so you seem to me to be addressing some of your own imaginings.
Uh, who has argued that anyone limit themselves to Access? Again, you’re going on a rant against something that nobody has even vaguely alluded to, let alone seriously suggested.
Here’s a free clue:
There are literally millions of businesses outside the enterprise world and they need databases that aren’t on the enterprise scale. And they don’t have enterprise-level budgets or hardware to work with. Nor do they have the in-house expertise to even benefit from enterprise-style approaches to their problems. Access functions beautifully in that environment, both as data store and as development tool.
That is not to suggest it is good everywhere — it certainly isn’t! — but nobody has made that suggestion. You deny that it is useful anywhere, and I have a host of clients from my last 14 years of professional Access development who would vehemently disagree with you. And there are thousands of such organizations out there who have done just fine with the toolset you claim nobody should use.
You seem to me to be deluded.
Ack, sorry about the screwed-up nested block quotes. I should have proofed more carefully.
David,
I think you hit upon a key problem with that article. It’s titled in a way that’s all inclusive with respect to developers. It doesn’t make the same distinctions you’re making. Now you can say, “Well, the right tool for the job,” so on and so forth, but think about a new kid coming out of a Comp Sci program that was mostly java-based. He’s stepping into that fresh new job with a company that is enterprise in class. And he comes across that original article. Guess what? He thinks the guy is referring to all developers, gets hooked into the mantra, and then gets locked into some small bit reporting position because he has chosen to spend his time learning Access rather than Visual Studio + SQL Server. I think you get where I’m going.
If you’re targeting the small businesses, wonderful. More power to you. But you know, I’ve got clients in the small business arena and we are intentionally getting them off of Microsoft Access (both the dev platform and the Jet/ACE DB). We have the ability to provide a small business solution with SQL Server back-ends and SSRS reporting. So we have. And they have thanked us profusely (and paid us a rather nice wage for the effort, too). So I don’t buy the argument that it’s just about Access. Or at least, my clients don’t. Which reminds me, I’ve got more reports to write for them… and deploy to SSRS.
“Access the database application development tool or Jet/ACE? Obviously, you mean the latter, but that’s not what you’re saying.”
No, I mean the former. Access the database application development tool doesn’t scale to multi-person teams, as you already admitted.
“None of my clients has data exceeding 1GB, including the ones whose data is stored in SQL Server.”
Ahhh, there we go. I wondered why you were so zealously defending Access, and now I get it.
“…at which point it’s no longer free, and your argument about SQL Server being free falls down. That’s an inconsistency — you argue one way when it suits you and change the subject when the flaws of that argument are pointed out.”
No, that’s the beauty of the SQL Server ecosystem. When your data exceeds the size of 4GB (and I know that might seem difficult for you to imagine, since your clients don’t exceed 1GB) all you have to do is change your licensing. You don’t have to rework the software from scratch.
“And it’s substantially easier to use than VS, whether for a novice or for a trained programmer.”
I never disagreed that Access is easier to use. In fact, my original blog post pointed out that’s the one point I’d give the author. An Etch-a-Sketch is also easier to use than Visual Studio, but it doesn’t mean I’d recommend someone learn how to use one of those either.
“You personally likely can’t walk into eBay and start working on their systems, either.”
Hi, welcome to my blog. While you’re here, you might take the time to read my About page. Or not, and keep shooting yourself in the foot.
“But, again, you’re arguing against something that nobody has suggested, i.e., that if you want a career path in high-level database development you should start with Access. Danny didn’t make this argument and nobody’s made it in any of the comments here, so you seem to me to be addressing some of your own imaginings.”
Danny didn’t make the argument, and that’s the problem. He should have.
I keep pushing that argument because I want my readers to make more money. You can’t make it to the big show with Access skills. End of story.
I think you have a skewed perspective and you don’t seem to have the slightest self-awareness of this fact.
Ah, and your perspective is perfect – yet your clients all have under 1GB of data.
None of my clients have *under* 100GB of data, and only one of them has less then a terabyte.
I won’t ask what your billable rate is because I don’t need to.
I think the vast majority of Access developers are in the same position as I, with small business clients who have small amounts of data.
I guess you wouldn’t know this because you don’t work for those organizations.
As long as the attacks on Access are as ineffectual and inconsistent and clueless as yours, I don’t think there’s that much to worry about. Your argument basically boils down to blaming Access for not doing well things it was never designed to do. Again, people may mis-use Access, but that’s the fault of the users, not of the tool itself.
And continuing to write off Access for any purpose whatsoever just shows you to be an absolutist whose opinions on the subject can be safely disregarded as valueless.
“I think the vast majority of Access developers are in the same position as I, with small business clients who have small amounts of data. I guess you wouldn’t know this because you don’t work for those organizations.”
I do know that because I used to work in those environments. People loved using my apps, so those apps grew like crazy. People wanted to throw more and more things in them, and they got more and more users. Next thing you know, I was bumping up against the Access size limits, and upsizing an Access database into SQL Server is nowhere near as straightforward as it looks on the label.
But then again, maybe you don’t have that problem because people don’t like using your apps. 😉 I’m kidding, of course, but it makes me wonder. Don’t people come back to you and want to add more things to the apps? Don’t they love using your software, and want to expand its functionality? Or is it all one-and-done jobs?
“And continuing to write off Access for any purpose whatsoever just shows you to be an absolutist whose opinions on the subject can be safely disregarded as valueless.”
So if I said nobody should ever eat broken glass, that opinion is absolutist and has no value?
I disagree. I developed an e-commerce system with a SQL 2000 database and a combination of ASP.NET and Access project (ADP) front-ends. It supported over 25 users in two counties, thousands of international customers, and a $30MIL/Year company. When you deploy 25 copies of an Access project to 20 desktops, the main issue with scaling is the database, not the front-end. My experience just doesn’t jive with your argument. I will admit that I prefer to use a more robust dev environment like .NET for most projects, but sometimes Access is the best tool, especially early on.
Hugh, multi-person teams is referring to the development team where you have 4-10 (or more) developers working on the same code base.
Brian, my mistake. If I had paid closer attention, I would have realized that is what he meant. And I agree, Access is not the dev environment for projects that need to be divided between multiple developers.
Wait, you’re backing up the still open files for SQL Server? You’re not executing a BACKUP using T-SQL and writing the backup file to the OS to then be backed up? Have you tested recovery to make sure that you can actually recover said databases?
No, I’m not trying to backup the open SQL Server files. I’m using the backup agent to write backup files that can be backed up.
And, yes, I tested that they work.
My point in mentioning that the SQL Server files were not being backed up was that a non-knowledgable sysadmin might have thought that was a satisfactory backup method.
But all this just makes my point — SQL Server requires more administrative work than Jet/ACE and if there’s no need for any of the other things that SQL Server offers in comparison to the Jet/ACE data store, it’s pretty hard to justify the more complex setup just because SOMEDAY…
Again, David, I will disagree with you here. In my experience handling Access requires more overall administrative overhead for the system administrators than SQL Server. I have a blog post firing tomorrow which explains why. I cannot count the numbers of times I have had to deal with a “catastrophic” Microsoft Access issue (not just talking about the Jet/ACE DB, but that’s what my post focuses on). I can count the number of times I’ve had to deal with catastrophic issues on the SQL Server side. And in all but one case, it wasn’t SQL Server’s fault. In one case the SAN “froze” (HP’s “official” explanation for the incident). In another case HBA drivers caused corruption. In another case the app flattened SQL Server due to an infinite loop in the code (and this was a quad system back in the day when that was top of the line unless you went Itanium).
And while I’m thinking of it, I noticed on your web site that you say:
“DFA also develops software in Access 97 and Access 2000, but does not support Access 2007.”
Can you elaborate on why you don’t support Access 2007?
For one, I don’t have any clients asking for A2007 development. 99% of my work is with clients already using Access, and none of my current clients have A2007 installed, and none of the new clients I’ve picked up in the last year are using it.
On my most recent new project, two of the workstations had A2007 installed, but because of that, the users weren’t using their existing app on those workstations because the UI was too confusing for them. I looked into getting their existing app working in A2007 with the ribbon hidden and all that, but the app was built around a menu bar and a toolbar, and this just doesn’t work well in A2007, so rather than wasting time coding around a problem for an app that was soon to be replaced, we “downgraded” their A2007 workstations to A2003 so they could use the existing app comfortably.
I’m planning on developing in A2010 when it comes out, though. I expect to have A2010 apps in production use a year from now.
I see A2007 as a transitional release that was a huge change for interactive Access users without much in the way of new features that were helpful in full-fledged Access applications. A2007 started the progression towards Sharepoint integration, but not with enough benefit for small businesses to make those new features valuable.
A2010 changes that equation big time, so I’m very excited about finding scenarios where its feature set will be a big plus in comparison to older versions of Access. There are several nice but not essential features of A2007 that will come along as part of that package.
I am very conservative in moving to new software. I didn’t switch to A2000 development until long after it was released, and was still upgrading A97 apps to A2003 as late as 2007. I mostly skipped A2000 development except for clients who were already using it when I was brought into the picture. Indeed, I don’t think I upgraded a single existing app to A2000, with the exception of one in mid-2000 that was a foregone conclusion at the time I was brought into the picture. In that case, the upgrade was from Access 2, so despite the myriad issues with A2000, there was a great advantage to the upgrade.
A2000 was actually an extremely problematic release because there were so many basic structural changes along with the new database engine. Jet 4 was actually a great upgrade (and huge for replication, one of my specializations), but the integration into A2000 was badly hampered by the Microsoft emphasis on adapting Access to enterprise needs, what with the introduction of ADPs and other half-backed tools that never really worked properly (and have since been abandoned entirely or deprecated), the VBE integration (which was handled very poorly) and the horribly misguided ADO-everywhere campaign (which most Access developers immediately recognized as wrong-headed and did not drink the Kool Aid and stop using DAO for Jet data).
I skipped A2002 (the one in Office XP) entirely and most of my older apps have been upgraded to A2003, which is probably my favorite Access release after 97.
On the whole I wait a long time before recommending upgrades to my clients, unless there are real benefits. This is particularly the case when there are jarring changes to the user interface. I waited 2 years before I had my clients start using WinXP (I really, really loved Windows 2000, to be honest), and I have no clients using Vista, because until the release of Windows 7, I had all my clients buy their machines with WinXP. I’m setting up two new Windows 7 boxes tomorrow and am excited about that for the clients, since it’s such a great improvement over Vista and WinXP.
I stress long-term planning for my clients so they don’t waste money on hardware and software. This means 5-year horizons as a given for the planning, and it means staying with older versions of software and OS’s longer than most Microsoft-heads would. I feel I’m serving my clients better with less churn than I would be with always promoting the latest and greatest.
Hence, my conservatism on A2007.
As we now enter the childish stage of the debate, you write:
I’m not making any categorical or universal claims. I’m not saying that SQL Server is wrong for any particular circumstances, only that the circumstances determine what’s appropriate. That forecloses no particular technologies or tools.
You, on the other hand, are dismissing Access out of hand for all uses.
One of these things is not like the other.
What does this have to do with anything? I haven’t made any attempt to tell you what tools you should or should not use. I certainly haven’t come even close to suggesting that you should use Access personally.
Ah, yes, now we get to the really mature part of the discourse.
I don’t claim expertise in your area of work. Maybe you can extend the same courtesy to me and those of us who work with small businesses.
But I doubt you will.
HAHAHA, you’ve been insulting my reading comprehension from the get-go, not just here but on Usenet, and you call me childish?
Riiight….
Speaking of reading comprehension – if you’re just now noticing that this whole thing also covered pay rates, didn’t you notice that part of my original blog post? It was the last line in the post. It was the punch line, for crying out loud. I was particularly proud of that line. 😉
(I don’t know why some of your posts lack a REPLY link, so that’s why I reply down here at the end)
Brent asks:
My current development projects include these, some of which are in active development, and others in maintenance with small tweaks happening on a regular basis:
1. an app dating from 1996 that I took over and revamped from top to bottom between May and October. Jet data store under 50MBs with full historical inventory/customer/billing data from 1996 to present. Enhancements and tweaks left over from the main project still to be implemented.
2. an app that I took over in May 2000 and converted from Access 2 to Access 2000 and upsized to SQL Server in 2007 (about 5 years after I started urging the client to upsize, BTW). New import functionality planned but not yet scheduled. Original MDB was under 300MBs before upsizing (haven’t looked at size of the SQL Server files, but they archived a bunch of stale data when we upsized, so it’s likely not very big).
3. an app that was begun in May 1997 and that has undergone substantial ongoing development and extension since then, including close integration with the client’s website. 40MB replicated Jet back end (laptop and office PC). In addition to ongoing maintenance and regular tweaks, some major new features are planned but not yet scheduled.
4. a customer billing app that was also started in May 1997 (in production use Nov. 1998), for a company that runs 25 parking garages in Manhattan (only a half dozen or so handled by this app). Replicated Jet MDB (accounting office and laptop for the person in charge of billing), 200MBs. Includes historical billing data for the entire period since the app went live (over >100K invoices totalling over $33 million, >100K payments, 4K customers). Minor tweaks and enhancements (e.g., new reports, etc.).
5. an Access app that I just took over last month, with historical data back to 1993, and a 40MB Jet data file. App redesign still in the planning stages, though I’ve done major re-architecting already (fixing numerous schema errors importing a bunch of missing data, implementing proper relational integrity, revamping the existing app for extensibility, etc.). This project is my current development priority.
I think the fact that I have clients from over 10 years ago still asking me to do regular updates and enhancements pretty much answers your question.
Brent asks:
I perhaps noticed it on first reading (I do read what you write to the end, unlike yourself), but it hardly seems like a serious comment.
After the Wall Street bankers debacle I think it’s amusing that someone might still believe that extremely high pay is naturally reflective of performance or competence.
Wow! Talk about some heated debate. I don’t really want to get in the fray, but I will add one note. As someone who had developed using Access for 15 years and using SQL Server for 10 years, I mostly agree with Brent… mostly. To highlight the argument made by many about the development side rather than the database side, I want to make note of the ADP.
An Access project bypasses the Jet engine entirely, using an integrated SQL Server database as the back end, with stored procedures natively available to power your reports, forms, and data sets of any kind. To build an Access project on top of a well designed SQL Server database takes a small fraction of the time that it takes to build a .NET application… as in throw it up in a few hours. It is hard for me to see a downside here. If anything, it allows the initial blast of “I want this” and “I prefer this” and “Add this field” from users to occur while you have an enviroment that takes seconds to adjust.
Then, weeks or months later, when you are ready for a full scale application in .NET, you will be building a much more well considered version of an application than you ever could have done previously. And you didn’t waste valuable time with heavy maintenance time working out the basics of the application. Because lets face it, not matter how many mock-up screens are shown to users ahead of time, they don’t really pay attention until they start using the app.
My take on Microsoft Access (specifically Jet/ACE) and supporting it: http://www.sqlservercentral.com/blogs/brian_kelley/archive/2010/02/15/why-i-don-t-like-microsoft-access.aspx#comments
I think the big difference is perspective. Supporting and developing for microbusinesses is *very* different than supporting enterprise businesses, even SMB enterprise businesses. A microbusiness, one where there are zero IT resources and nearly a zero budget for IT services does require significant reduction in scalability. In other words, a business that does not want to pay for any technology (everything needs to be free) will have to significantly lower expectations for scalability and reliability. Much like they do for their physical facilities, transportation, advertising, marketing, and other business expenses.
We are never going to agree if we are debating what is the right tool that will satisfy the cost, benefits, and risks of microbusinesses and enterprise class businesses at the same rate.
For instance, if a 3 person Realtor shop came to me and said they wanted an app to store photos, multimedia files and listing information and that they wanted to spend less than $100 on technology, I would not tell them to start with MS Access then move to other technology when they got to the 1GB limit in the first 4 hours of entering data. I’d identify the types of open source/free/ otherwise zero acquisition cost technologies that they might be able to use to handle large data volumes. But I’d also probably tell them that they should not be paying for custom apps and should look at some external software service that would host all that data – maybe a CMS system that one could buy a template for that had real life data centres and databases behind the scenes. That without professional IT support, they were going to get themselves into trouble by having someone build a custom app. Period.
I get requests all the time from family and friends to help out a microbusiness that needs IT help. I tell them universally: find an online service that meets your needs. You don’t have the resources to support, manage, pay for or sustain custom anything.
But then again, I don’t work in that field, as my rates don’t match expectations for microbusinesses. My rates and expertise are at the enterprise level.
If an enterprise solution was required to handle pictures, multimedia files, and general listing data, I would not tell the enterprise to start with MS Access and then upgrade when they hit 1GB, either. I’d start them off by identifying what enterprise class technologies were already in place, supported, maintained, and managed. Then I’d ask how many users, how many concurrent, and how many developers. Then I’d see what was the quickest way to get them there.
Another excellent and completely reasonable post, Karen.
However, when you say this:
…I have a couple of responses.
Most of my clients are 3-5 person operations, microbusinesses by any definition.
But they don’t lack money for IT solutions tailored to their actual needs. That is, when I spec out an Access/Jet/ACE solution for such as business (in reality, in most cases I’m coming into the picture long after that decision has already been made), I’m not doing this sort of thinking:
“Well, the ideal solution would be .NET application running against an Oracle cluster capable of serving 100K users simultaneously, but obviously, they can’t afford that, so we have to scale back to something that costs fifty cents.”
No, the ideal solution is one that meets the client’s needs for capacity, functionality and reliability within a budget that gives reasonable ROI (and most of my clients are wary of anything that’s free, since they want to have someone to support the software they buy). In situations where I’m the IT department, and thus not onsite all the time (or even once a week), support and administrative costs need to be kept to a minimum.
Scaling the app to the client’s business does not mean underprovisioning from a theoretical maximum of scalability. No, it means choosing tools that cover the range of foreseeable needs for the application in question. I can tell you that hardly any of my present clients are ever going to expand beyond their current size, not because they are not well-run, but because they have already reached the natural maximum size range for their particular businesses (some types of business don’t scale up because they don’t exist (and likely can’t exist) at any larger scale).
For companies that already employees one or more people full-time to take care of IT needs, the marginal cost of adding a SQL Server in is pretty close to zero (SQL Server is *really* easy to care for in comparison to monsters like Oracle, for instance).
But for an organization that outsources IT, and like all my clienst, handles all their IT needs with a freelancer brought in only when needed, it’s a different situation. For one, most run-of-the-mill computer techs are not DBAs and have no experience administering and maintaining databases of any kind (I’m fairly unusual in being a PC tech with database background, or more correctly, a database developer who does generic PC tech support), so they’d have to bring in someone specifically to make sure the SQL Server backup and maintenance routines were in place, properly configured and running reliably (let alone properly installed in the first place). That is, the tech will recommend that if she’s savvy enough to know that she needs to.
Small businesses really like having one person to trust for everything, so adding in another support person is not always going to be attractive to them. And then there’s the additional cost — they might be paying their regular IT person a few hundred $$$ a month retainer so they can count on quick response time. Do they now have to add in a couple hundred more for the database admin?
A couple hundred a month is not that big a deal to a big corporation, but for small businesses, it buys a lot of reams of paper and toner and all the other things they need to keep their business running on a daily basis.
Now, all that said, I do definitely believe that most small businesses woefully underinvest in IT, mostly because they don’t engage in any pre-planning — they buy equipment and services when the notice that they need them, instead of working out a plan for how to maintain their IT infrastructure and services over the long run. And the result is a hodge-podge of equipment and a break-fix attitude about all IT problems (do nothing until something breaks and then fix it).
And they wrongly think of IT as nothing but a hardware expense (not even an investment), instead of as an ongoing service that needs to run reliably all the time. This means they think of IT as a cost center only, and not as a driver of profits in its ability to increase productivity. In general, they’ll count the costs of IT and never recognize the benefits, which are usually not really quantifiable (how do you figure less down time, fewer unexpected disruptions, etc., into your gross profit margin?).
One could easily make a case in the context of a big-picture IT plan for a microbusiness that a SQL Server would be better insurance against potential disaster than using Jet/ACE as your data store and relying on file backups to recover most of your data if anything disastrous occurs. But if there’s no real way to quantify that benefit, it just looks like a cost, and so, many microbusinesses will make the decision, wisely or not, to spend the money not on insurance that they may never cash in (SQL Server) but on something else — salaries or marketing or whatever improves the bottom line tangibly and directly.
This calculation (even though it overlooks important real parts of the cost/benefit equation) is kind of like a high-deductible insurance policy — they are willing to pay out of pocket for the unexpected issue in order to not have to pay the higher insurance premium. They are basically betting that the cost of a low-probability high-cost event will be lower than the accumulation of ongoing maintenance costs.
For my clients who are using Jet/ACE data stores, that decision has been a pretty good bet, as not a single one of them has lost a single byte of data. That doesn’t mean none of them has had no down time recovering from the rare incident of corruption (maybe 3 times in the last 10 years has a client of mine actually had a corrupt database that was not immediately repaired by a simple compact), just that nobody has come even close to going out of business because of one of those very rare events.
Naturally, in some businesses (even some of those with only 2 or 3 employees), any downtime or any data loss would be unacceptable, and thus for them, a database with higher administrative costs would be more than worth the expense. But I don’t have many such clients (the ones that are in that boat are already using SQL Server).
So, it’s not really a matter of scaling down from ideal massive enterprise-level solution. It’s a matter of finding a best fit between the client and the technologies. Different organizations will reach different conclusions on where the sweet spot is for ROI. It’s my job to help them choose correctly, but if I were doing a bad job of that, I don’t think I’d have as many clients who’ve been engaging my services for more than a decade.
If they keep calling me, I’m pretty sure it means they believe I’m helping their bottom line.
I like using MS Access becuase I like to build solutions rather than general database input/output stuff. I like the ability to make Access talk with Excel,Word,Outlook etc. I prefer to be a solution developer rather than a programmer. Sure I could still do this by using SQL Express on the backend, but it’s not something I’ve investigated.
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…
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.
…and what would you do if you didn’t have access to the SQL box, bright boy?
Frankly, your post is bunk, the number of logical fallacies committed being beyond comprehension – but much worse – your tone and method of expressing your viewpoints are completely deplorable and unprofessional.
I am responsible for managing the flow of information into and out of a primary risk management entity in one of the largest financial services firms in the world. And that means, in a business of this size, regular (as in daily) management of multi-million row result sets and rapid dissimination of analysis based on same to corporate knowledge workers and decision makers.
I am well qualified and experienced developing C#, VB.Net, SQL Server, etc. I regularly work against multi-terabyte data stores on both DB2UDB and SQL Server. Additionally I am routinely called upon to RAD applications and processes to fill emergent regulatory and risk management needs until the mainframe guys can catch up with their solutions (usually 12-15 months later). A properly constructed set of libraries and well defined patterns and practices are key components to make this work, and our primary tool at the desktop level is Access and Office.
Working in VBA, we utilize OO methods to manage data objects in the libraries and deploy front-ends that routinely have nothing more than a reference to the service library. All database calls and concurrency are handled in the service layer and there will be no locking issues using this approach – because there aren’t any locks.
For information and process management within compartmentalized groups there isn’t a better solution…
Does this mean we would use this toolset to build customer-facing solutions for our 9+ million clients? Of course not. Does this mean we would use this tool as a backend to a highly available transaction intensive process? Of course not. Only a buffoon would even debate it, and only a buffoon would offer it up as a topic of debate.
At the end of the its all about the tools, and you obviously have only one blade in your knife.
What’s tragic is you seem happy about it.
Hi, Blake, thanks for stopping by. Sounds like we’re both at opposite ends of the spectrum, so I won’t waste your time by talking through points. I appreciate that you’ve got a well-thought-through opinion.
You should totally blog about your experience doing information management on multi-terabyte data stores with regulatory auditing compliance using Access. I think that’d be a post we’d all love to read.
Have a great day!
Good article.
In my case, I’m an old dog that has to learn new tricks. I started working with relational databases back in the mid 80’s; even some OOP designed for the NeXT computer in the early 90’s – then some unfortunate circumstances a derailment of sorts, when I got back into the game it was Access that I picked up on.
I created databases that have been consistently error free. 98% of the user entry forms I create are unbound so that means i use lots of VBA code to do virtually everything on a form.
I have databases that are low volume transactional databases (< 500 entries a day) that have been in operation for more than 10 years and I rarely if ever (after the initial debugging is done with) get any calls for things that are broken or not working correctly. I remember the Maytag Washing Machine Repairman commercials. I DO NOT LIKE support calls to FIX something! I have received calls to add features.
I learned something that Microsoft cared to learn and that is for normal companies or projects support will cost you more than development if you don't get it right! Microsoft never learned this (creating sloppy, inconsistent, bug ridden products) because unlike most companies, they make billions fleecing people by forcing them to purchase paid support.
That being said I'd LOVE to convert everything to the SQL Server Express. But, it appears to be too daunting a task.
My development is probably not like most others. I have a database with 5 different front ends that are subsets (each with a different set of functionality) of a Master Database front end. I wrote an automated tool to create the front ends so when I need to make a change I only have to change the front end of the Master.
What's formidable is that this is a very large application w/more many thousands of lines of VBA code. I like to write self documenting code so there are some table field names that are large. I can't remember if there's anything larger than 32 characters at the moment, but I do have variables in the VBA code that are larger than 32 characters. I heard that that was a problem w/conversions.
I use Access 2003 (I hate Ribbons and don't care to learn a whole new environment) which still serves me well for what I do.
If it was a simple matter of creating an SQL Server and importing tables and relationships and linking the tables like I do now I'd do it. But I'd have to change thousands of lines of VBA code to talk to the back end which in on a network server.
If I had started the projects with the SQL Server then it would have been great.
Right now, I wouldn't know even were to begin if I had to create a new database in SQL Server.
Oh, and in case anybody is interested here are some screen shots of two of the larger DB’s that I have designed. I am in the process of upgrading a Check Cashing Database at the moment.
There is one that I didn’t build a screen shot page for that automated a manual office process.
It grabbed info from a clients agent page, stored this information into an Access Database, calculated commissions, then uploaded the information into a MySQL database I created so that my client’s agents could look at their commissions on line.
This was before I learned about Front Ends and Back Ends.
http://demo.plesk3.freepgs.com/usedcar/
This system has 5 Different Front Ends (each with specific functionality – not shown – the screen shots are from the Master DB)
http://demo.plesk3.freepgs.com/fmsweb/
In reality I really do need to convert the database (fmsweb) to SQL Server.
I can’t even begin to think where I would start. As you can see, it would be a nightmare for me to consider migrating this to SQL Server even though I need to.
Hi! Wow, that’s quite a series of comments here. Glad you got value out of the post, and have a great weekend!
Well there’s not been much talk about database design, a much overlooked subject.
Throwing a few tables together then stitching on a front end that attempts to keep the back end “normalised” is in my experience what makes a database (as opposed to a front end) scalable within its own limits.
Scalable means different things to different people, even databases means different things to different people as this post shows most effectively.
Even harder is writing a set of specifications for a “database” that is accurate, clear and understandable. SQL Server, Oracle and all the big name back ends also suffer from scalable issues it’s just that they start and finish at different numbers so the word scalable actually has no meaning in computing terms, it’s just another variable bandied about by salespersons.
You’d be surprised how many Banks, Telecoms and large Corporations utilise Access (or maybe you won’t) but then again the largest developments in databases here in the UK are government projects and they all have a failure rate well up in the 90% region, none of these uses Access so what’s the issue?
It’s the design that’s the problem and the lack of developers who have the knowledge required.
There’s nothing wrong with Access or its scalability within its own limits, what’s wrong is developers who think they know what they’re doing when in fact they don’t and if there is a problem with Access it is that it’s too easy to use badly.
It’s not what you put in that count’s it’s what you leave out.
“There’s nothing wrong with Access” – okay, I’ll bite. How do you back it up when users leave the client open on their desktop, and the Access file is on a network share?
Well its simple really all the repairs I undertake to Access apps usually suffer from this or related issues so one of the first things I implement is a hidden form that loads on all the front ends, its purpose it to trap the user attempting to close the application (which they may have done by mistake) but its also on a timer set to fire every hour where a message box pops up (say) after 21:00 hrs asking the user if there still there, if they don’t respond within two minutes the connection to the back end is terminated (on another timer) and the application front end is closed. Using another utility (dll) you can also shut down the machine as well.
I’ve been using this for over a decade with no issues.
Wow, Brent — even your avatar telegraphs mocking condescension. Although you do have some valid points about the limitations of Access (everything has its limitations), your entire philosophy smacks of “real IT” snobbery. Ten years from now, when most IT “departments” have been outsourced to a server room in Bangalore, you might be wishing you had paid more attention to what the “rubber meets the road” guys are doing with the tools that come installed on their desktops.
Not that I haven’t tried to do things the way IT *says* they want it done. Except that when you ask for the tool set and the database space — the suddenly nobody has the budget, there are security concerns, performance concerns, etc. Or, you could get the IT guys to add the functionality you need (let’s say a table with about 20 columns) but it will be six months and $500,000. Obfuscation sophistry, misdirection, the motives of which are clear.
Let’s take your most recent objection to what is “wrong” with Access:
<>
Really, you can’t think of a way to do it? It’s just a complete impossibility isn’t it? Wrong.
If you IT guys would come down out of your ivory tower once in a while and actually talk to what the users need, you might be able to find the right blend of true enterprise level products (not a single one of which can be all things to all users) and rapid application tools. But don’t worry, even if you are not so philosophically inclined, business are moving forward — users are moving forward. We’re getting smarter all the time about finding solutions that give us what we need, when we need it, without having to go a-begging the IT priesthood. Everything is gonna be alright…
Secca – thanks for your comment, and have a great weekend!
I want to see all these awesome access databases and access front-ends work on mobile, desktop, tablets, as well as communicate with machines as web services.
The real problem is that access databases are outdated technology that only fit into a business that is microsoft centric. The problem with modern computing is that nothing is microsoft centric besides the people that acquired these skills and refuse to move forward out of the dark ages.
On another site I noticed a ‘access developer’ saying how their access database had almost 900,000 records in ti! HOLY COW! 900,000! That’s really ironic considering I’ve worked with databases verging on billions of entries and can cross over into trillions and still scale.
900,000 records won’t even cover geodata for the united states postal regions and that’s a tiny data set.
Regardless I think we can easily see why people use this tool – it’s something they can build things in quickly, have no clue about design, no clue about normalization (or proper denormilization for that matter), and it will never have to scale for anything. It’s great for businesses that don’t understand proper data management or have value in that.
In the end it just makes me laugh when people would choose these poor tools when such superior options exist. Sure you won’t be able to point and click your way to garbage like you do with MS Access but that’s actually a very good thing.
Now that I think about it – more people should use access – it keeps proper engineers paid more and gives entry level developers things to convert into real workable, scalable applications that can be run concurrently by thousands of users – not just a handful running MS windows that own a license for office professional.
Lots of companies are Microsoft-centric. And probably always will be, for as long as most of us are still working, anyway. So what? I know a lot about database design, normalization, scale, etc. And many of my clients/employers need small-scale tools that can be built relatively quickly and cheaply, and don’t require a 4-week wait to be documented, discussed, tested, approved, thrashed by “Agile” teams, etc etc, when you just need to change the caption on a button or add a parameter to a report or query. I’ve built some applications used by 100-200 users with an Access front end and SQL Server or Oracle back-end that have been running pretty much bulletproof for the environment where they’re needed for 10+ years and no complaints. Every circumstance is different and Access certainly can be the right tool in some situations.
I use whatever tool is right for the job. MS Access is effective for a number of applications, not so for others. Being a technology snob gets you nowhere and impresses no one. Some IT people say Access is a toy, it’s not even a real database, Visual Basic is the Fisher-Price of programming, it’s a scripting language not a real programming language, blah blah… So what? If my boss needs a report and I can build it in 10 minutes with MS Access and it works fine for my boss’s need, so be it. If I need a web-based application for hundreds of thousands of users, I’ll build it with something completely different. No technology is “best” without the context of what are you using for?
Didn’t see a Reply button for this comment. ?
Jason – you wrote:
“MS Access is an easier and less expensive way..It comes packaged with MS Office…..”
Interesting. How much does Office cost per user these days, by the way
Are you suggesting Businesses go without Excel, PowerPoint, Word??
Jason – certainly not. There’s more than one business version of Office, and note that they don’t all include Access:
http://www.microsoftstore.com/store/msusa/en_US/cat/Compare-Office-suites/categoryID.68155000
Access isn’t free. Nice try, though.
Many businesses choose not to lock themselves into a single proprietary vendor; your question makes it sound as if there are no other spreadsheets or word processors in the world! I work in data analysis and don’t have Excel, PowerPoint or Word installed on my PC. And I definitely don’t have Microsoft Access.
Brent Ozar is a putz.
Indeed.
Downsides to the file sharing type database using Jet are: –
Users logging into the back end on a server share using a Wi-Fi connection can destroy or corrupt the back end database… sooner or later.
For SMB’s there is no other downside.
Anyone storing a trillion records in any single database needs to update their knowledge on database design.
No other downside for SMBs, which are up to 1k users? Interesting! Didn’t know Access could handle 1k concurrent users. That would be quite the interesting network connection there. Well, sounds like you’ve got it all under control. Enjoy!
Some of you guys are such tools… I am sick of the sophistry and the willful ignorance demonstrated here. It is easy to take pot shots at Access. So, go do whatever it is you do but please stop the putdowns — the rest of us will keep rolling up our sleeves and cranking out the kind of solutions that take days, not months or years to complete. No bowing or scraping to the IT Priesthood. No walking down the aisle swinging our incense burners croaking Gregorian chants about Boyce-Codd Normal Form. Just solid workmanship using reasonably priced and useful tools in the proper manner — not trying to drive a nail with a crescent wrench as some of here might contend. I never found a better tool for analysis than Access in conjunction with Excel against an Oracle back end. I can assure you, in my hands… and others who have diligently learned its ins and outs, Access rocks, and rocks mightily.
Secca – thanks for stopping by. Interesting that you didn’t refute any of the points in the post, just relied on emotion in your response. Hmm. Probably not related to your choice of tools though. Have a great week!
You criticize me for not refuting the points in the original post, which was someone else’s list to begin with (not mine), and contained your original 10 “rebuttals” which long ago I found to be less than rigorous at best and snarky at worst. I did not use emotion as much as imagery to make my point. And my points (stripped of any possible “emotional” allusions are:
1.) I can get actual work done in days, not weeks or months
2.) I don’t have to make a case with anyone for why my work needs doing — I just do it. As opposed to getting an IT budget, a justification for that budget, approvals, design document, etc. ad nauseum)
3.) The tool that allows (mostly) me to accomplish the above is Microsoft Access.
Hmm, was that really that difficult to gather from my post? i don’t think so. I once thought this thread might lead to some better insights into the proper place for Access in the grand scheme of things — now I think you disingenuously troll the Access community. After all, you SQL Server guys use “real” tools and make “real” money… When you go there it’s a pretty huge putdown.
And ending you little snark blurbs with a polite cliche and and an exclamation point doesn’t really make up for being so smug.
And one more thing, Brent. What makes you think my employer gives me a “choice of tools”? Some of us have great jobs but we don’t necessarily get to pick and choose when it comes to what we use on a daily basis.
So don’t worry about replying to me — just throw your head back and laugh the hearty laugh of your avatar, because after all, you are the one and only Brent Ozar!
Secca – I promise you that I never worried about replying to you. 😉
Oooh, what a burn…. get some new material
Funny, I would give you the same advice about your tooling, but you’re not quite as receptive there.
Dude! You utterly missed my point! (2nd to last paragraph)
OK, Brent, you WIN !! SQLSERVER ROCKS AND ACCESS SUCKS. Hope you get a lot of clients now.
Using SQL back end and Access front end, reason being access auto generates forms for me and gives me an easy place to build a UI. There may be a much better front end, but is it as easy to use for a non-developer?
This has begun to turn into a good old-fashioned Usenet-style flame war!
Not really… old fashioned Usenet flame wars are primarily characterized as being dumb … this is not like arguing whether Ringo is the best rock drummer of all time…. so, please, don’t try to stir it up again, thank you. It is far too tedious…
I had a good laugh readying your post. To bad it isn’t serious. I could write up a serious article going over the various issue with Access, but this one was just a joke. Thank for the entertainment though.
Danny boy,
Your withering sarcasm would be too much for me — however, your ridiculously bad spelling and grammatical errors convinced me that your “serious” article might not be such a “serious” read.
Visual Basic and Access – Laurel and Hardy
Hakan N.,
Congratulations on your extremely clever analogy… just another typical IT snob — nothing real to contribute to the discussion except more snark
I really enjoyed reading the comments posted here.
While I generally agreed with Brent, some defenders of Access gave some well thought out reasons for a different viewpoint.
David Fenton was one of those defenders.
While trying to understand where his viewpoints were coming from, I tried looking David up on the web.
I’m sorry to say that a google search brought me to the following link that says David Fenton passed away in 2011.
https://groups.google.com/forum/#!topic/comp.databases.ms-access/OPJKKwxdsUs
I’m not 100% sure that is the same “David Fenton” that posted here, but the text on that web page leads me to believe that it is.
This would be a good thing to be wrong about. Someone please correct me if I have gotten the wrong “David Fenton”.
I didn’t know that. David Fenton was extremely knowledgable about Access many people will miss his advice.
Instead of thinking of him as having passed away, I prefer to think of him as having hit his 2GB limit.
🙂
Hey, I’ve been a fan of Access for a long time. True, it doesn’t scale beyond maybe 20-25 users (using Jet/Ace) BUT within that range it’s impossible to beat the sheer development speed and convenience of Access. VBA isn’t .Net, of course, and I like .Net better, but in ease of development (real programming, not macros) Access stomps .Net + T/SQL.
But the combination of VBA with something like DAO (I know, showing my age :)), a report writer and advanced forms (including sub-forms) makes for one heck of a compelling package.
I created a 100,000 line Access application that basically handles every aspect of our company. We’ve been using Access as the frontend/backend for 17 years now (and yes, we’re stressing it to the max at this point). I’m in the process of replacing it with a .Net/SQL Server solution so I know both sides.
Can I just say that for development, as nice as the .Net framework and languages are, the new development environment SUCKS compared to the ease of development in Access.
Yes, Access is limited, and yes, it’s security is pitiful (blame Jet) but for sheer speed of development nothing touches Access. One IDE, one language, one access method (if antiquated) vs half a dozen different tools, two different languages (one a dinosaur irt syntax).
Having said that, would I ever develop another system in Access? Nope, I’m all too aware of Access’s flaws, especially in the newer versions.
But it’s interesting that the 100,000 LOC Access version is now 560,000+ LOC in .Net + T/SQL for pretty much the same basic functionality, split evently between two vastly different languages (not to mention the reports have to use Crystal Reports, sigh).
If I were granted a magical wish I’d wish .Net/SQL Server were at least as well integrated as Access, without the latter’s problems. I know, if wishes were horses… 🙂
>but for sheer speed of development nothing touches Access.
What about Python?
Not even Python (which I love, BTW). Because Python doesn’t have a built in IDE for code, *reports*, *data design*, *data lookup*, AND immediate mode debugging all in one easy to use package.
One tool, one stop. The whole point of Access is ease of development, and it allows very sophisticated code to be written very quickly.
Unfortunately, MS left in some very crippling deficiencies so they could rape corporations with exorbitant prices ($11,000+ for SQL Server? Really?) like passwords that could be recovered, like a file based engine, etc.
Still, for what it was, it was beautiful. Alas, it hasn’t aged well…
Here’s why access is still valid: use sql as back-end, let power-users create simple apps/reports. There’s many users who currently build departmental solutions, which don’t need to scale, that simply can’t invest time to learn MVVM or whatever the new flavor of the day is. For ‘professional developers’ it may be a questionable tool, but it still rocks for power users.
There are a lot of developers and DBAs who are very snobby about tools. For example, real programmers only use SQL Server or Oracle or DB2, and anything else is like a Fisher-Price toy platform. Meanwhile, there are thousands of shops where you can do a perfectly fine job creating apps, even if they’re just front ends, using MS Access, VB or whatever because you’re doing the heavy lifting on a server, and for simple applications that have to be built, deployed and modified at a moment’s notice, sometimes those are just the right tools. It’s not a matter of only using certain tools, it’s a matter of selecting the proper tool for the job at hand.
Different use cases entirely. I don’t see the point of comparing a capable standard and integrated client based application with a light driver model, to a bloated resource hungry client-server model SQL Express + whatever third party frontend developed in anything whatsoever. SQL Express backend tools are horrible and not even remotely similar to something like Access. The cost is also irrelevant since it comes with the o365 subscription.