Is Access Really Dead?
One reader asked my opinion about Access, and is it dead. So, here is where I stand.
Back in the early ‘90s I was actually a Microsoft Certified Developer with my primary tool being Microsoft Access. It really bothered me that developers hated the tool. I thought it was cool. I could do a LOT of stuff REALLY FAST! And, that was my job. I was dedicated to helping divisions handle software problems for things that would never get done by the 90+ developers with a 5 year backlog of projects.
I was king of my hill, and very popular with the vice president of every division in the company. I could put together easy to use, visibly pleasing, efficient applications in a short time.
Then they wanted to expand the applications to multiple users. That’s when the problems arose. File corruption was the frequent problem. We then moved the applications to use SQL Server as a data source. Then we had locking problems, or optimistic concurrency problems.
Then we needed to have multiple programmers working on the same project. We tired the SDK for Access allowing it to integrate with Visual Source Safe. That didn’t work well either. It corrupted the application as often as not. So, we had a lot of contention or merging issues when more than one developer worked in the same project.
I have found it to be a great report definition tool, departmental tool for when Excel is not enough, that kind of thing. But, IMHO, don’t try to build an enterprise app using Access. You can do it successfully. However, the constraints placed on you to make it work successfully are easier to do in other programming platforms.
Regarding FoxPro, Microsoft did a great thing when they purchased that. It’s a harder tool to corner since it does so many things, and is a more sophisticated tool allowing version control, multiple developers, and multiple data stores in a natural coding environment.
Moreover, I am told without coboration that the RushMore technology from FoxPro was added to both the Jet and the SQL Server engine. There was a marked performance boost when the next SQL Server release came after the FoxPro purchase. Who knows if there is any relationship there…that’s just gossip.
One thing is for certain…the fox pro engine was the fastest database running on x86 machines for years, even over a network with multiple clients having to do their own indexing and file management on shared files.
Reader Comments
Jack:
In your article on “Access Dead”, you didn’t mention FoxPro / VFP? Just wondering why, I was a VFP developer for over 20 years and loved the language – especially the direct access to data – both a benefit and detriment from a corporate perspective.
Thanks for the provocative article
Michelle:
Despite our unwritten policy to “let ACCESS die”, it seems instead to be gaining an infusion of life support. As more and more IT resources are outsourced overseas creating ever-lengthening cycle time, more internal interest is generated in ACCESS and the do-it-yourself approach. Officially, IT doesn’t support ACCESS, but as users become a bit more sophisticated, they are getting exponentially more frustrated with the lack of an ACCESS expert on the premises. ACCESS users are loosely attempting to fill the role, but the jury is still out as to whether success can be achieved.
Brad:
I am currently porting an overly big operations and billing access database to SQL server. The pain the people inflicted on themselves using access to run an accounting department is staggering. I believe access should only be used for micro one off projects. The lack of scalability, overuse of stored queries and importing and linking to excel spreadsheets not to mention the use of the file system hard coded in VBA is the reason I am killing this access database. Its demise cannot happen soon enough.
Chris:
I am someone who started out “developing” on MS Access 97. The reason I think Access is so widely used is because of its fast development time. I can put out a fully functional form in less than a hour where a web page takes significantly longer. Not having to reinvent the wheel every time I make a program is defiantly an advantage.
The main reason I got into it though was because IT was too busy to work on any of my departments issues. This gap made space for our group of “rogue” developers who needed to get beyond the limitations of Excel (64,000 rows only at the time), but were not allowed to do any web development because that was IT’s job. Talking to other colleagues I have met through the years, this was prevalent with many different types of industry, so I am not alone in this career path.
The one issue I find now is that there is quite a gap from Access VBA to C# web development. Looking at LightSwitch, I can see this tool bridging that gap. I haven’t dug into it yet, but if it can do all the minimal work and let me work on the custom code parts, that would make it a lot easier.
Bob:
I read your article but then realized you had no opinion. Do you think Access is a development language? Do you think LightSwitch is a development language?
I develop software with Microsoft products. Have never looked at any Java products. I am finding development with Microsoft tools to be more difficult every day. I looked at LightSwitch but the lack of reporting makes the product unusable.
Editor Note: I don’t think lightswitch is ready to replace Access for everything it can do. I do think that for the things lightswitch can do, it will allow departments to solve problems, and then developers to take it over without having to throw everything away and start from scratch. They can simply take the LightSwitch generated bits and walk away from LightSwitch.
I would imagine that Lightswitch in conjunction with Reporting Services could make a good combination…but haven’t tried that myself. Anyone out there have any experience to share?
Jeff:
Just a brief comment — Access, using an ADP file, is a nearly perfect instant front-end to a SQL Server database. As such, it becomes a sort of zero-client database browser, giving end users instant access (so to speak) to SQL Server tables, including the ability to modify the underlying data, controlled by security on the dbms. Extremely useful. An ADP file pointing at a SQL Server database can, in many cases, replace a bunch of Excel-based tables on a file share or in a Sharepoint folder, with many advantages. I agree that using Access as a shared, file-based dbms is hideous, but that’s by no means the only use.
Randy:
LightSwitch has all the simplified development tasks that should have been standard issue and included "Free Of Charge" with the 2010 Professional Visual Studio .Net release. On a separate note: I concede that LightSwitch pushes Access VBA closer to the disposal unit.
Aaron:
thank goodness, I never thought I would hear those words.
I love ligthswitch, I hope and pray that MS Access is dead!!!
David:
Well you are forgetting on major player that was giving SQL Server a run for its money and was multi-user capable from the beginning, and that is FoxPro with the Rushmore engine. Many large scale application were written using FoxPro like the U-Haul Sales and Reservations system. I have worked in a company where there was FoxPro and SQL Server, and many on the stored procedures were recreated as FoxPro programs and they ran faster in FoxPro than the best coded stored procedure did on SQL Server. This was a hard pill for me to swallow at the time. But it was the 4GL nature of FoxPro that made it more efficient than SQL Server in those cases. And I have yet to see an Access applicatrion that can host the user count and has the perfomance capability of FoxPro.
I do agree that Access needs to be twilighted because in many environments, it is the Access connection to SQL Server that causes perfomeance issues on the SQL Server. In many cases it is because the users do not understand how to buffer transactions or how to construct efficient query pass though to the SQL engine. In many cases I have seen Access being used as the entire application with SQL Server having little more functionality than a filing cabinet. If you looked at the database, there were no stored procedures, no views, no functions or other typical constructs found in a properly designed SQL Server application.
Acces was always a problem in a SQL Server environment and it is about time it went away.
Tony:
I read your article and felt compelled to give you my thoughts as a SQL Server DBA/Developer who came to SQL after many years as a developer, consultant and trainer in Access.
This question comes up every few years and after virtually every new iteration of Access is released. And thankfully Access has well and truly outlasted the arguments against it by many of it’s critics. After 18 (!!) years using it and developing many databases and apps, as well as teaching and helping many individuals and organisations to use it, I still feel it’s the best tool around for quick application modelling, report design and crunching of data. There are so many things that I use it for, which are just too hard to do in Excel and would take long to do with Visual Studio, given current budgets and time constraints.
I developed complex systems that were used by between 1 and maybe 20 users, fully coded in VBA, and often utilising reference libraries from other MS products, as well as DLLs provided by other third parties, mainly to pull and push data between applications. I developed one such app that ran the ordering system for a growing company for many years.
When the company outgrew the Access database we spent a year upsizing it to SQL Server and redeveloping the Access front-end, converting all the bound forms and reports to unbound. This investment has paid off, many times over, in that, although most of the original functionality has been rewritten in .Net, we are still using the front-end for some tasks, and especially for developing ad hoc reports. I STILL work mainly in Access 2003 and it is STILL powerful enough to do everything I need for this company, which has now grown very large in terms of locations and users.
The only things that I have found Access particularly lacking in are Web front-end development (those DAPs always seemed to be half-baked) and distributing apps with the run-time. I tried developing several Access run-time based apps but it was always tricky getting the references right, especially with the many different versions of Office that were already installed on most users’ PCs out there.
Thanks for raising the issue and also for pointing me in the direction of LightSwitch, which I will now have a good look at. Can it possibly be as good as Access, or have as long a lifespan?
Share your opinion with the rest of our readers by sending your response to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting SQL Server 2008 Business Intelligence Development Studio
In this article, Alexander Chigrik explains some problems that you can have with SQL Server 2008 Business Intelligence Development Studio.
Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
Written by Quest Software
When you’re looking for a reliable tool to diagnose … (read more)