I agree fully as far as data storage goes but, for some reason, there are a lot of people that are really comfortable with the Access user interface. It would be nice to see Microsoft take the Access front end and throw it on top of SQL Express. That would let people familiar with Access create their GUI front-ends as they always have but leverage the power and "expandability" of SQL Server.

Nice article.

As developer with rich Access history, I can certainly join this call :).

You post clear indicates that you know nothing about Access. For example - "When you out-grow Access, you have to convert to SQL Server ... It's a huge pain" - this is not true, upsizing Access aplication to SQL Server is very simple task, in case you know what are you doing.

Try writing an article withour repeating yourself over and over again.

Try writing an article withour repeating yourself over and over again.

Try writing an article withour repeating yourself over and over again.

What about Foxpro?

With respect to Mr. McCown, he may not be aware of all the areas in which MS Access is helping folks with work tasks. After a small investment in familiarization with the program, a user has a powerful tool available to him/her to assist with everyday tasks. I can imagine that the program may not be as useful to the developer community as some others, but my job is to perform in areas other than programming computers. Access is a big help in keeping me ahead of the competition--from both internal and external sources.

While I agree with your arguments on cost and upgrade ability, there are two main points that are going to prevent Access from just 'dying'. First is the huge number of applications already built with the tool, and as you point out the effort replace those apps is also huge with no payback other than to just replace an existing piece of functionality. The second, is that Access does provide a layer of services that the free products do not yet provide, and it is enough to make a difference to a group of people who understand the uses of a database, but are not at the level of a true developer. I have written many applications in both Access and the .NET products, and Access is a RAD (Rapid Application Development) tool, and when I am getting paid by the hour, provides a cheaper solution for small projects. It carries all the overhead you point out, but if those are acceptable risks, it is the cheaper solution. If Microsoft (or any other software developer) were to come up with a product that tied the table maintenance, form development, and plumbing to hook it all together as seemlessly as Access but using the standard .NET tools, then you would have an Access killer. Hmmm...sounds like an interesting project. Thanks!

Your point is valid only where prototyping takes place: building a large database by starting with a small one first.

Does the SQL Server Express include a report engine? If not, then keeping Access just for reporting is a big time saver (Geeks like Crystal Reports, and haven't you noticed that it has evolved to be similar to Access' reports in design mode? I wonder why...).

For small databases, SQL Server or Oracle are not only overkill but big time wasters (especially Oracle...Geek turf only there) for managers and executives, who want to do their own reports. They also don't want to find out they need utilities for reports, and in Oracle's case, querying (Outside of IT, who does SQL script? Or want to?).

Your story only looked at the data storage, but ignored reporting. Without the reporting, there would be no need for databases (scary thought, but true).

For mature Access applications that have a frontend / backend separation, what technology do you recommend for connecting the frontend Access app to the backend SQL2K5 Express?

Or do you recommend junking the frontend Access app also?

"SQL is also much easier to manage. With Access, you can have the files anywhere on your workstation, or the server. You don't know where to start looking, and if you have a DB you don't use for a while, and you forget where the file is, what do you do?"

This comment is superfluous. It's not required to justify SQL over Access and it gives a childish tone to the article.

Besides, if you're unable to manage your machine sufficiently well to keep you from losing your important files, SQL won't help.

Hmm. How about NO.

Unless you want us to convert the last of our departmental databases to MySQL.

So, all a small business owner needs to do is to dump Access, download the free SQL Express and then, in his or her spare time, build a front end using Visual Studio Express. Oh, and, of course, our business owner already knows principles of database design, so there's really nothing to prevent producing a useable solution. After all, there are lots of easily understood books that cover the basic issues - right? Just take a little time and that puppy will be up and running in no time!
Look, let's not kill Access or other end user tools - even if they do leave something to be desired - until we have something that can realistically meet the needs of the users.
If you really think that the average small business owner has the time and skill to design and build a front end for a SQL Express database,
you either know some pretty atypical folks or maybe, you need to think about the real life implications of what you are saying.....

All is true. One issue - an Access db file contains code, UI, and data in one place. No code management. If you kill Access, you drive lightweight development for data entry/reporting to Excel and/or Word, suffering performance and UI consequences. Now any real developer would not care about this extra work, but until SQL Express or equivalent maintains code and UI along with the data, Access will live on.

This article does not deal with the real world... There's one HUGE reason why groups can't adopt SQL Server Express--the requirement of a database "server". This is no small thing--think of how many business groups use little Access databases on a network fileshare for some small purpose. With Access, anyone in the group with r/w access to the MDB on the network fileshare can run it and do their thing--and do it simultaneously with their co-workers. With SQL Server Express, a machine needs to be set up for Express, client software has to be written & installed to connect other users to that Express box, and the network has to allow such peer-to-peer communication. No small task for a team's multi-user 2MB contact list...

Access also has a big plus--someone who has never worked with databases can go to a 1 day training class and set up an Access database that the whole team could use--complete with queries, reports, forms, etc. While powerful, SQL Server Express is overkill for such databases, requires a machine to act as a server, and requires client software to be written to connect to it.

Also, the "server" requirement is a problematic one. Even if Express is running in the background on a team member's client PC, it's taking a huge amount of memory and processor time just to sit there--waiting for a query. On the other hand, if you close out of Access, it's closed and the resources released... (Yes, I know Jet 4.0 may still be taking some resources, but generally speaking it's closed).

Yes, I'm disappointed that there haven't been any worthwhile upgrades since Access 95... (The interface hasn't had any notable improvements since then, but Access 2000 had Jet 4.0, which was a vast improvement over Jet 3.5. For those who don't remember, Jet 3.5 would die if a query returned more than ~64,000 records). But hey, Office is Microsoft's cash cow, and none of the Office apps have improved much since then anyway...

Which takes me to the most basic question, Sean... If all a business group has is a Netware/NT fileshare, how is SQL Server Express an option?

While I agree that the database functions of Access are worthless anymore, there is still a real use for it. Many IT shops, including mine at large companies us Access strictly has a frontend for SQL Server and Oracle. Quick queries, data validation, or cross form reports that simply require a frontend to do. Unless you have a developer on staff to always provide you a front-end everytime you need it, or a custom one off query as needed, Access is a good solution for those type of events. I was at a Microsoft Conference in 2000 when they announced that their vision for Access was exactly that. A simple front-end tool for users to be able to access larger databases without the need for development of custom front-ends. This works great in my IT shop, and many others has I suspect.

Access shines for ad-hoc data manipulation, analysis and exploration. The total package of query designers, report designers, macros, and VBA is wonderful. For example, I often need to take data that describes a client's railway operating plan and transform it to a "standard" representation. I've tried other tools -- including SQL Server express 2005 and VS Express, but Access is by far the easiest, most powerful tool that I found. By at least a factor of 2.

I hate the warts of Access - the need to compress, the "invalid argument" when the 2GB limit is nearly reached, and that sometimes it just crashes for the heck of it. But SQL Server lacks the ease of use and integration of tools that Access has.

Can I just say 'BRAVO!', 'BRAVO!','BRAVO!' I couldn't agree with you more abuot the time for Access to go the way of the dinosaur. I work for a very large financial institution, you've all seen the red umbrella, as a software developer and am always amazed that a lot of our departments still use Access for critical applications. One particular thorn in my side is when departments that we interface with and are dependent upon for viatl information still insist on using Access as their primary choice for database/application development. I've pointed out the risks involved in using Access in a networked environment and the time consuming chore of just maintaining it until I'm blue in the face. All this to no avail since it seems that they believe Access to be the be all end all due to what they deem as it's simplicity. I can't tell you the number of hours, days, months spent trying to get accurate information from these systems and heaven help you if someone using the database gets a bit curious and starts poking around where they shouldn't.
I agree with you wholeheartedly that it's time for Access to go the way of the dinosaur. It makes absolutely no sense to keep using somehting that you still pay a heft price tag for when there are far better, and yes free, solutinos out there. I use SQL server on a daily basis and maintain several servers and could not imagine trying to do the same thing with Access. So one and all take up this wholly grail and cry for the banishment of Access!!
My humble or not so humble, your choice, opinion.

This is true. In addition, Access can not provide the security level of SQL Server because with access, no matter what security measurements have been set up, all can be bypassed with a SHIFT key being pressed down at load or a Cntrl + Break to stop startup code.

Ok, as long as you don't move to killing FoxPro I'm with you.

-mcs

This article is partly on target, but overstates the case for killing off Access. It's true that the Jet database engine is somewhat of a joke and far inferior to SQL Server Express. The real strengths of Access are as a rapid development platform for database applications, and apply in two circumstances:
1. Creation of functional database application prototypes;
2. An Access database is an empty shell that serves as a front-end to a higher-end DBMS via OBDC links. (The resulting application is not intended to be scalable, but performance is acceptable with a modest number of concurrent users, e.g., <= 10-15.)
I have yet to see any other platform that lets you build working interfaces (using the various wizards and design tools) in such a relatively short time.

Access 2007 is quite useful - not as a database solution - but as as ad-hoc data prep tool. There are many cases where all I want to do is denormalize a flat-file (e.g. the file has "codes" in some columns and I want to replace them with a meaningful description). When you have more rows than Excel can handle (or you have data with leading zeros Excel strips by default), then Access is a quick Swiss army knife.

As a production system, I agree - the Express versions of SQL and Visual Studio are far better.

Not the fault of Microsoft Access software:

Users who are too clueless or lazy to recognize that it should never be used to store sensitive data.

Developers who are too clueless or lazy to recognize that it should never be used to store sensitive data.

Developers who implement sloppy coding practices in their UI's and make the app perform like a dog.

Microsoft's highway robbery prior-version support practices (hardly any) and overall exorbitant pricing.

Businesses that undervalue IT and force their staff to use ill-fitting software applications to get their work done.

Businesses that just don't have the money for servers or staff to implement daily patches, and must make do the best they can.

"Real" programmers who have always had a bad attitude about Access, no matter how skillful the development in this platform.

Give me a break!

With all respect Mr. McCown, get out of your ivory tower and see what small businesses have to work with, and how properly utilized, well-designed Access applications can do wonders.

Not the fault of Microsoft Access software:

Users who are too clueless or lazy to recognize that it should never be used to store sensitive data.

Developers who are too clueless or lazy to recognize that it should never be used to store sensitive data.

Developers who implement sloppy coding practices in their UI's and make the app perform like a dog.

Microsoft's highway robbery prior-version support practices (hardly any) and overall exorbitant pricing.

Businesses that undervalue IT and force their staff to use ill-fitting software applications to get their work done.

Businesses that just don't have the money for servers or staff to implement daily patches, and must make do the best they can.

"Real" programmers who have always had a bad attitude about Access, no matter how skillful the development in this platform.

Give me a break!

With all respect Mr. McCown, get out of your ivory tower and see what small businesses have to work with, and how properly utilized, well-designed Access applications can do wonders.

To Myke who posted at August 9, 2007 11:05 AM - my company uses an ODBC connection to link the Access front end to the SQL data.

Hope this helps!

Kill off Access. I don't think so. I have been working with Access since 1.0 both in corporations and as a consultant (currently). I won't go over all the reasons as others have already mentioned them. My experience has been not that hard to convert to SQL Server, did a lot of that working for corporations. My clients love Access and most of the small businesses I consult for will probably never get that big, they are happy with their size. The cost for Access is minimal. And I use Access for other tools, quick and dirty data conversion, not just for production databases. There are a lot of small users out there where I think Access can be a great tool.

The 3800 Access databases we have on just one network drive (in a small insurance company) sounds like a vote of confidence in a tool that allows business users to be productive without having to aquire a PhD(MS) first. Scrapping Access would be as daft as dropping the most productive business programming language in history - even if they did replace it with a dialect of C.

I use Access in a major Financial firm. Not a week goes by when I'm not asked to put something together for one group or another. I've got a quantity of reusable modules built for conducting surveys, reporting to Excel, and an assortment of HR apps. I was even asked to put together an HR tracking app for a city government. (After using it, they decided to cancel that module of Peoplesoft.)
I can push out fully-featured Access databases pretty quickly and deal with user's requirements changing. If I had to work with SQL Server, every change would have to roll up through 3 levels of approval, UserAcceptanceTesting, etc, which would delay rollout of any change by a week. In Access, I ask "By what time do you need it?"
While I prefer to work with SQL Server (for the security aspects alone - I'm not fond of working with Access security), corporate overhead keeps people coming to me for Access. It fills a niche that is created, unfortunately, by nitpicking IT departments.

Good luck, man.

You're going to need it.

Everything you offer up is right, of course. But I consulted for 12 years with Fortune 1000 business users (read: *not* IT) and it is hopeless. You talk about SQL Express? They'll never hear of it. They use what IT cops make available to them, and that's Excel and Access. I begged, pleaded, done everything to explain all the reasons that starting that "little pilot app" in Access will kill them down the road. They hear you, but they don't care. Its all they have access (no pun intended) to.

And on top of that, you've got management consultancies coming in and supporting decisions like that (because its the only skills their legacy folks have).

But overall and as usual, corporate IT is the problem. People will use what they have, business users aren't sitting around staying up on the latest tech offerings out of Redmond.

To praphrase, "I reject your premise and substitute it with one of my own."

If you look at Access as purely a database product, then I can accept that SQL Express is a far better solution than access will ever be, but I think that is rejecting the foundations of Access from way back in 1991/2. While Jet was there for data, one of the original premises was that of Access as a promiscous reporting and application package which could connect to just about anything. Remember, this was one of the first ODBC interface reporting and dev apps to ship.

Jet suffered from, in many cases, it's there, it's free, let's use it! Even in many many cases where its use was less than optimal. It is in these situations where the applications designers should have opted for a more robust solution from the outset.

So, as a database solution competing with even FoxPro, SQL Server Express, or other solutions, retiring Jet and replacing it with SQL Server Express under the covers of MSAccess would not be a bad thing. But let's not kill off Access in the areas where it does have strong efficiencies on the ability to connect to just about anything, combine data with other "anything" sources and report in a consolidated fashion.

For starters, if a lot of people use it then it must worth something right? Don't just try to "kill" something that is still well alive.

I don't use as a dev tool but many customers of mine (including very big ones like banks) use it as a daily office tool. The employees build their own databases and UIs on Access, present their data on meetings on reports, copy/paste data to and from Excel to manipulate data and show it as they want, etc.

I don't know if you ever had this experience, but replacing this user experience with an application is a very hard task and you usually end up cloning the MS Office user experience because it's what users like and are used to.

As a project manager I tend to think on the users and then on the tech. Most users just don't care about what tech will you use on their software, they just want it to work, well!

Ever since I graduated from college and began my career in an IBM System/3, Model 6 shop using RPGII as the programming language, I've listened to people brag about how great the system they work on is, and how bad the system I work on is. The IBM System/3 family and RPG language were looked down on by COBOL programmers. Now, I've been developing Access applications and making a decent living for the last 13 or 14 years. But someone that works on a "real" database thinks that the free Express versions (SQL and Visual Studio) are better because they are "free" and so much more powerful. Give me a break! Just as our small business did VERY well with the small IBM System/3 because it fit the company, and was MUCH more agile than the BIG BOYS, and therefore development cost was nothing in comparison to the BIG BOYS. Same thing now with Access vs SQL Server. When you have the need for the big stuff, then use it. Until then, stick with the less expensive stuff. That of course is Access. Maybe where you work the development costs are free if the software is free, but with my clients, I'll continue to charge them for either Access or SQL Server/VB development. Because I can create the app much quicker in Access, unless they need the performance of SQL Server, I'm sure they will continue to "limp" along with Access. Actually, they are all VERY happy with the apps I have written in Access, and with the cost being lower than if the app was written in SQL/VB "just because it's better."

When I was a fresh out of college junior programmer I once heard an evaluation from a senior programmer who was asked to evaluate Access when it first came out back in '93 (or was it '92?). He said, that while it had some nice features it wasn't as powerful a database as people thought it was. That opinion has held true for Access as a database to this day. It was an improvement to dBase and Paradox which were the primary desktop databases in use up to that point. However, it never went far enough to be a really good desktop database.

That said, I think it is very useful for bringing in data from disparate data sources. Whether, it is loading from Excel, text files, xml, or connections to other database platforms via ODBC, Access is very strong in bringing multiple data sources together. It is easier to connect to other databases from it then say from Oracle to SQL Server or vice versa. That is its primary usefulness to me in my job and I am quite pleased with its functionality in that area.

Look, most of the people who want to kill off Access are techie types who have had to clean up user messes.

Any tool in the wrong hands can result in a cloister flock. If they weren't doing it in Access, they'd be doing it in Excel or Word.

Frankly, for fast development I personally think Access is one of the best tools out there. I am currently working to move the existing back ends to a SQL Server while continuing to use Access as the UI tool.

Why would I do that?
1) I don't have to rewrite all of my existing front end apps (I do the front end back end thing for every database)
2) I can prevent unauthorized changes to the code by using an .MDE file or by locking the code down with a password.
3) I can produce new apps really quickly, as I already have an extensive library of modules, functions and classes that I've written in VBA.

Finally, in the last year and a half at my current employer, I've implemented 5 different applications, saved the company half a million in reduced labor costs and just recently implemented an automation process that will provide 3 full time employees in additional productivity.

All in Access. If the department I work for had to wait for IT to work on this, they would still be trying to justify the inflated costs IT would charge just to evaluate the problem.

I personally think the solution for Access in corporations is to allow it, but get the users in training so they understand data normalization, and understand the fundamentals of naming controls etc, so that if IT has to help, they will at least not have to spend the first 2 weeks cleaning up messes.

If SQL Server allowed VBA code I'd use it but the Access front end is ideal for all forms I write. To show that Access can hold its own: The EPA wanted all subissions of air quality data in XML format. Every enironmental department in the coutry was working on code to do that. Most are very large shops and were using STORET, FoxPro, SQL. I work for a Native American Tribe (Kanienkehaka/Mohawk) and wrote the code in VBA using Access. My program allowed us to be the first agency of any type, Native or Non-Native, to complete submission in the required format. I now go to conferences to distribute our software and teach other Tribes how to use it. This saves them thousands of dollars a year in consultant fees by allowing them to do it themselves. Access is far from dead!

Tom Lake
Information Systems Administrator
St. Regis Mohawk Tribe
Environment Division

I don't think you've thought this through.

Maybe, like most developers, you are limiting your expertise to the development environment you know.

To start let's thing about the two environments in which database applications are used, Web and desktop. And then we can talk about the advantages and disadvantages of of data storage solutions.

For Web apps, yes, the dot net environment is the best and SQL Server likewise - no contest. But for desktop application Access wins hands down and this is why MS are moving it to center stage as there most ubiquitous dev environment for desktop database apps. So why?

Distribution is easy with Access and FREE ( MS freely distribute the run type environment for 2007)

Reporting - no contest Access is brilliant and free to distribute. Oh! and EASY to distribute.

Access is much faster to develop a desktop application with by far. You can lock down the code but allow users to make cosmetic changes to forms and reports. (I export reports to the back end when users change them and re-import them after an upgrade).

Visual Basic 2005 has improved but is still less sophisticated in its abilities. It still lacks many of the niceties that Access developers take for granted.

I suppose if you are on an hourly rate then Access will limit your income but, for the client Access wins.

Access can connect easily to a SQL Server back end using ODBC, you can even use ODBC to connect to Jet.

For a back end database it's horses for courses. SQL Server is the best all round but expensive. Both Express and Jet are limited to 2 gig but Express can only handle 4 concurrent users.

SQL for a small business with a few users Jet wins. However if every byte of data is precious then SQL Server is the obvious choice but restoring does need a person who can act as DBA.

So the real position is that the best solution is the one that fits the need, not the one that the developer is most comfortable with.

There is a tool to migrate from Access to SQL Server:

SQL Server Migration Assistant for Access (SSMA Access).

Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005. SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server.

(http://www.microsoft.com/sql/solutions/migration/access/default.mspx)

So there you have it. The overwhelming response is in favour of holding onto Access.
Lots of the people who use Access are, like me, not technically programmers, although after a while you realise you can't do anything short of flexibility so you end up using SQL and VBA and ADO and DAO(and a tad of XML) without the need of being part of some obscure programmers' lodge. I use Access as an invaluable tool to handle data in a way which is much more robust than Excel would ever allow me. That's another plus of Access that no other program has: tight integration with the other Office applications.

" I use SQL server on a daily basis and maintain several servers and could not imagine trying to do the same thing with Access."
Access can interface with SQL server and that's fine but its mission is not to rival or be a subtitute for it. We don't need a dehumanised number-cruncher behemoth; that's not Access' purpose, more like a personal or relatively-small-group productivity tool.

We have a very mature Access app that uses a SQL Cluster as a back end. We would love to dump Access but the problem is the app is so huge and we can't do it a bit at a time so we are stuck with it.

We usually hold off upgrading as long as possible due to all the problems it creates until the balance switches and it creates more problems staying with the old version. We are just moving to 2003 now.

We have looked at 2007 and are fairly certain we will never be able to use it because of the changes to the coding.

Personally I can't believe Microsoft is happy to see generations of users dumped like this. Surely it would have made sense to create a facility in VB.net to mimic development and a really functional development kit that will allow porting of existing applications in a piecemeal fashion.

By piecemeal I mean being able to rewrite certain modules but make it all appear under one App with SDI or MDI.

I totally agree on this. The problem is imho that many firms don't engage somebody for creating a database app who actually is a programmer / dba.
Access is part of office and so just somebody who already works there and knows Access (having created a table once counts) gets that job. And this ends in one large table with all data in it, like an excel spreadsheet.
I have seen this kinda thing in ALL firms (3) I worked so far. And it was always us (the IT)/me who had to get on with it.
But I still use Access as a Frontend. Its difficult to explain somebody who has no idea what you are talking about why its necessary to develop a new FE, if "there is one already" ...

I use it as a transport mechanism in to SQL Server. With the Max records in Excel of 65k (whatever it is) Access doesn't have that limitation. There might be other ways but it is really easy to import data in to it and use the DTS import wizard in to SQL Server. We obviously don't need new versions for that but I hope they keep it round for that reason alone.

My ode to tunnel Vision:

"For lorn that everyone be a DBA with unlimited access to a SQL Server in a corporate environment or small environment." Try installing SQL Express on corporately controlled workstation.

Yes, if MS Access were simply a database storage tool, then yes, you are correct in wanting it to die. However, Access can provide much more than a database tool. The analysis capabilities and data entry control capabilities are not to be dismissed in any environment. I guess you those simple features may have escaped you.

> Access can not provide the security
> level ... because with access, no
> matter what security measurements
> have been set up, all can be bypassed
> with a SHIFT key being pressed down
> at load or a Cntrl + Break to stop
> startup code.

All my Access database have the Shift Key on a toggle so that I can toggle it off when I deploy it.

As much as I hate access it does serve some missing features in the SQL/VS world. When your end users want to build queries is one example. The graphical query builder in access is pretty good.

Reporting Services also has a LONG way to go before it can become a great reporting tool. Try building a label report in Reporting Services. Access is definitely better for building reports and queries.

Martin

You are just SO wrong about this. We wrote our app in Access because it does so many things quickly that would take days with anything else. And properly written Access front ends are MUCH faster than SQL on small tables unless you spend a fortune for hardware and the requisite full version of SQL Server to take advantage of that horsepower.

For our customers, with Access all they have to know for administration is the data filenames to back up and how to repair and compact once a week.
If the need to look at old data - just open an old copy of the file.

When our customers get big enough (15 to 30 users) we simply upsize their data to SQL and attach THE SAME ACCESS FRONT END to SQL. The users see no difference!

Access will even run 2 or 3 users with an ordinary workstation acting as the file server. Actually we have customers using a network storage device as the backend - no server required.

If you can afford knowledgeable staff, sure SQL is the way to go, but with Access I can send a USER (not an MIS guy) an install disk and he can be up and running with a multiuser application in an hour with no outside help and still be confident that the application will grow as his needs grow.

With Access I can build a database, put a password on it, and EMAIL it to several other people to use. Try that with SQL Server.

One of the reasons Microsoft has been so successful is they have always understood that most real innovation comes from the little guys at the bottom and so they make it easy for them to get started and then provide an easy upgrade path that keeps them using MS products. Access is a PERFECT example of that. If some department analyst in Large Corp. needs to start collecting some data to test a theory do you really think he is going to take the time to learn the difference between a 'front end' and a 'back end' and install something like SQL Server and then Visual Studio and learn how to use it? No. Is he going to put in a budget request for developer time to build him a database? No, he is just testing a theory. He is going to open Access and start gathering his data, and if what he is doing turns out to be useful to others he will get you guys who are trying to Kill Access to convert it to SQL for him because his job is analysis and your job is databases.

"someone who has never worked with databases can go to a 1 day training class and set up an Access database that the whole team could use"......... and sorting out the mess they've made and moving the whole thing to VB.net and SQL Server is keeping me in work nicely.

Access is great for the amateur, for home use and one man businesses but never for corporate systems. We don't all expect to do our own car repairs or re-wire our own homes, we employ professionals. In the same way we shouldn't expect to design and write our own database systems - get a professional to do it once and do it right. Access is D-I-Y and amateur and should be limited to distribution with Microsoft "Home" products where it belongs.

Access must go..long live Access

Sean McCown is not worthy of published articles. Anyone who is, researches the topic, which has not been done. Two words: Due Dilligence.

Don't listen to this clown, Sean McCown.

Access ADPs are running in Fortune 500 companies, economically connecting to MSSQL, Oracle, AS400, etc. From my small research, mainly tools and reporting.

As for the Access MDB file, yes, it is limited. MSDE or SQL2005 express do a much better job of storing the data for a local application.

I'm have been a consultant for many years. As my job involves working at many differnet customer sites it's usefull to have a bag of tools to take with me. MS SQL is certainly a more robust database for applications but I find MS Access an invaluable tool for data conversion. I find I can whip up an update query in Access much more easily than in MS SQL. Even SQL2K5 doesn't seem to have the smiplicity of MS Access.

It looks like this article is for IT and application developers. I don't know of anyone in IT who would even remotely consider using Access as the back end for an application they develop, which limits the usefulness of the article.

However, there are some big advantages for the business users - who are able to easily create their own "applications" - data storage and reports - without application development know-how.

The only reason (and, in my opinion, it is a huge reason why I am limiting business users from using Access in my organization) is because the data will mostly reside in an island, and the ability to share that data across the enterprise becomes limited.

You obviously have never used Access. I've used both Access and Visual studio and am heavily entrenched in ASP.NET and Access. Access serves a great niche where Visual Studio just doesn't.

First of all Access can be used simply as a front-end to SQL Server or any server-side database for that matter and most of my apps use it in this way (e.g. against SQL Server, PostgreSQL, MySQL). So your whole argument about upgrade to SQL Server is baloney. You don't need to use it as a database container just because it can do that and it does provide a quick prototyping tool that you can then upgrade fairly easily.

Secondly the reporting tools and form tools in Access and the Query builder is bar none. It allows power users to design forms, and sophisticated programmers to do fairly sophisticated things - so satisfies both camps.

In query builder, you can draw lines between tales, drag drop switch to SQL mode if you need to tweak the SQL or copy it somewhere else. Its export and import features are phenomenal.

It does cross tabs with transform in a very slick and quick way which SQL Server 2005 (even SQL Server 2005 PIVOT is not as good as the transform in Access).

Kudos to all those who responded that exposed the narrow minded scope of the article in that is purveys the attitude of a code-monkey rather than someone with experience with integrating technologies with business operations.

Access is necessary to fill the gap for business users where a pot of gold is not available to hire development staff to manage individualistic needs. Developers in mid to large size companies are predominantly focused on larger projets where Access would never be chosen since it inheritently wouldn't scale.

The sentiment expressed in the article can be felt where problems have occured in many an organization where a knowledgeable employee has built a Access application and decided to 'share' with his colleagues. Suddenly this becomes an unsanctioned system with no sustainability but has curried favor of users. The solution to this is not by eliminating a tool such as Access, but for a company to enforce policies that prevent such workstation tools evolving into the beasts they can become.

I've been a PC developer for over 25 years, certified in .NET and a former .NET instructor. I'm also a big fan of Access - where it makes sense.

Over the years most of my smaller clients couldn't afford nor would they need a VB (Classic or .NET) solution. I've had generally postive experience with trustworthy, intelligent people using Access. Many of them actually build their own queries. Hardly a month goes by that I don't help someone with an urgent need for a special query or whatever that we can do over the phone - no way I could walk them through .NET; perhaps I could dictate the SQL Server query, but that's more difficult.

For the larger, more robust, and sensitive applications I do use .NET and SQL - sometimes after a quick prototype in Access.

I'm now a full-time in-house developer and I've just started looking at Access '07. One of my non-IT co-workers developed his own app using Access '07 that by mutual agreement I'll convert to .NET & SQL - saved me a bunch of time & he got to prototype it exactly the way he wants it. Yeah, the data isn't normalized and there's a bunch of other things a "real programmer" wouldn't do - but I can see exactly what he wants.

Although I'm not keen on several of the "enhancements" for Access '07, overall I feel like it's the best yet.
The right tool at the right time for the right reason.

In the hands of someone who knows what he's doing--this excludes the majority of end users, but also the majority of IT types--Access is super-powerful, especially as a front-end tool across data platforms; nothing else will link up and work with disparate database technologies so easily.

Sure, I could make better money as a .NET programmer, but I prefer building apps that work, and building them quickly, to sitting around in a team and waiting on the other guys who never learned how to do something as elegantly, as efficiently, and as sexily as a smart Access developer can.

The real surprise is that there aren't more products fighting for the Access market share; good RAD front-end tools to server-side dbs are basically non-existent. And since most applications are ideas first, there's no point in investing in anything heavier to get them out of the gate.

Can SQL produce a fucking rich reporting and a gorgeous form like Access. If so let me know ASAP.

Access can be intercoursed with any database like SQL.

I will agree on one point here. Access is not the best back end. For a front end I think that it is great. I have done many things in our Access front end that other IT people in my company thought were not possible. It also has a great report generator. The security on it is not the best but you can lock it down fairly well if you need to.

Access will not die no matter how much the minority of the people want it to die. I say live on Access.

Way too often folks like McCown go off on a rant with incomplete/incorrect information; standing obliviously against their own pompous, attention-seeking ignorance. But then, stupidity is still just a choice.

The year was 1991. Armed with zero database experience, no training and a brand new licensed copy of Access 1.0, I was able to create a suite of modules for a successful print shop in northwest Indiana that do the following:

* Job Tracking
* Point of Sale
* Chart of Accounts
* Accounts Receiveable
* Accounts Payable
* Payroll with check-printing
* General Ledger
* Monthly, Quarterly and Annual reporting
* Annual Account Balancing

Over the years it has been upgraded to Access 2003 and is STILL IN OPERATION TODAY! All records in tact. TCO < $5,000 and totally customized for the shop.

I guess artificial intelligence and planning pay-off in the longrun. Real stupidity pays-off immediately.

Good wind-up, 10 out of 10. Bad appraisal, 1 out of 10.

I'm in IT and have been for 20 odd years. We support both SQL Server and Access databases, routinely. Furthermore, I have been doing this long enough to remember the days before Access, and Windows. I did lots of systems with dBase, FoxPro, Clipper, and the like.

I hear your comments on the weaknesses of Access. I know all of the problems. Access can cause problems it's true. And yet, you are missing something.

Access is a great product for all of it's flaws. For users ready to take the next step up and leave the world of Excel, it's much less intimidating than SQL Server.

Access is included routinely in selected Office bundles, whereas SQL Server is NEVER included, not even the free versions. The clients are likely to have Access already, but ad-hoc software installs are required to get SQL Server Express.

Access revolutionized the usability of databases. The graphical interface, dynasets, a good report writer, access to Windows drivers with all those tasty scalable fonts, proper laser printing. There's a lot to like about Access, and it blows away all its predecessors.

We have done some absolutely massive database migrations. Data transformations of many kinds were required. The tool we chose? MS Access. It was powerful, quick, and gave us the confidence that we wouldn't encounter an ETL issue that we could not resolve.

One of the things I like best about Access is that it is an extremely deep product. You never seem to bottom out on functionality; there's always a way to get the job done.

When you use Access as a front end to a more enterprise class database, the picture gets that much better. Access actually splits the query workload intelligently. All the query primitives that the server supports, get passed along to that server for execution. Anything that the server cannot handle, Access runs locally. That's smart, network aware, and professional grade.

So no, I don't want Access to die. A professional knows his tools, and knows when and where to use each one.

As a Database manager for a large Canadian Financial institution I would say that I have so far never use THE perfect tool. Microsoft Access has its utility as well as Web tools. It all depends on what you want to do, how fast and at was cost and complexity level.

We are using Microsoft Access as a front end to SQL-Server 2005 and both products are coexisting and working very well. We also have Web pages on our national Intranet also connected to SQL-Server. I agree that Web tools offer easy deployment and users only require a browser.

However if you implement a good Access architecture (Compiled MDE files, automatic centralized distribution of new Access front-end versions on users stations, use of ActiveX controls, libraries of codes, SQL stored proc and UDF, good SQL table design, appropriate indexes, adequate form reports population, etc), then Access will also offer you a very good solution to multiple problems. We even have users in remote cities starting their local Access front-end and connecting to the centralized SQL-Server over the WAN without performance problems (provided you slightly change the way you normally open and populate Access forms).

Also, when it comes to complex form design (lots of controls, lots of validation logic (it cannot always be moved on the back-end), rich interface, complex reports (and all this to be done in a very short time) then Access is hard to beat has a good and acceptable solution. On the other hand Access as a back-end database is only good for small tables and a small number of concurrent users. If you intend to use it as a front-end on complex and large databases with many users then SQL-Server as a back-end becomes a must.

I have personally tried a few times to duplicate a fairly complex Access form using a Web development tool. I admit knowing Access a lot better than Web tools and definitely lacking advanced Web development training but so far my experience has been to always come back to Access for rapid prototyping and forms or report development. Maybe I am wrong but I feel that it would take me 15 hours to do in Visual Studio what I can do in 1 hour using Microsoft Access.

Still I definitely intend to increase my knowledge of Visual Studio and get my staff trained on it but I do not think Access has yet reached its retirement time.

Been using Access since Access 2 and all versions there after. I find Access very powerful and allows me to do many different things very quickly. I have connected Access to Sql Server 2000 & 2005 many times and have gerated quite a few cost effective applications that saved my companies big bucks.

I use access for checking data integrity too. It can link to just about anything, so it's quite capable of data discovery and data quality checking.

And, there's much more......

I agree with your comments that the database in MS Access should go the way of all flesh. However, please in future articles - do NOT refer "SQL Server" as "SQL". SQL is an independant language that SQL Server has in common with many other databases. The two should NOT be confused - although, Microsoft would love to have it so......

I am not a big user of Access, however have used access couple of times and found it useful. However, aftre reading this article it makes sense. Access, why dont you die?

http://www.TechHairBall.com