microsoft

Microsoft Access needs a complete rethink – or retirement

15 Comments

Microsoft Access is now thoroughly out of sync with the company’s wider database technology. I’m writing an introductory piece on database applications, and the failure of Access to keep pace with what is happening elsewhere is glaringly apparent.

Let’s look at what database formats Access understands. There is its own native .MDB format, now ACCDB. ACCDB is the updated and incompatible form of MDB introduced in Access 2007. Then there is SQL Server via the Access Data Project, now deprecated, which connects with OLEDB. Then there is the possibility of linking to external data via an MDB or ACCDB, which means ODBC or ancient drivers for things like dBase and Paradox. Finally, there are some special drivers for Excel and for Sharepoint, which do not interest me greatly in this context.

What’s missing from this picture? Primarily ADO.NET, the core database technology in the .NET Framework. For example, what if you want to connect to a SQL Server Compact Edition database using Access? Microsoft in its wisdom does not provide an ODBC driver for SQL Server Compact Edition. There is an OLEDB driver, but you can only use this from VBA, not with the interactive Access user interface. In effect, Access is hopeless for working with SQL Server Compact Edition, which is a shame because this is an otherwise attractive choice for a file-based desktop database. There is an ADO.NET provider of course; but Access cannot use it because it does not understand .NET.

Microsoft gets grief from time to time over why it does not use the .NET Framework for its own Office applications. Although the Visual Studio Tools for Office (VSTO) link Office somewhat effectively with Word, Excel and Outlook, the core applications are native code, and the core macro language for them is Visual Basic for Applications (VBA), essentially the same language Microsoft retired for general development back in 2001 when VB.NET appeared. Access also has its own form engine, also ancient; it does even use standard VBA forms.

While there are good reasons why Office remains native code, it is Access that has suffered the most from the lack of .NET. It seems to me that Microsoft should either rebuild the product using the .NET Framework; or retire it. I suppose it could also do some clever integration work, adding .NET language and forms to the product, but for the effort involved it hardly seems worth it.

I have never much enjoyed programming with Access, but used to like it for interactive work and reports. I rarely use it now, for the reasons stated above.

The problems with Access hit home and small business users who start off with Microsoft Office and build a custom database, most likely an MDB or ACCDB. At some point they want to take it to the next step, maybe as it becomes a more sophisticated application, or needs to support more users, or be migrated to the Web. They then need to abandon most of their work, exporting the data and starting again. It’s become an embarrassment; it needs a complete rethink, or retirement.

15 thoughts on “Microsoft Access needs a complete rethink – or retirement”

  1. The Access security model has also been junked. I used to program in Access a lot in the early days and really liked its ease of use combined with the flexibility and power it gave, but I hated the security model as it was a pain in the backside. I think that Microsoft are trying to open a gap between Access and more robust and enterprise ready SQL Server, by the looks of it.

  2. Access long ago served its original purpose: putting Borland’s Paradox and dBase out of business (along with MS’ acquisition of Fox Software).

  3. This would be similar as discussing the good old strategies Microsoft has used for its Windows OS. Notice how they sort of “force” peopel to get the latest Windows by removing support to their earlier edition.

    This here sounds nothing new. I believe Access, as Scott points out, served its purpose in the eyes of Microsoft. Its a company that seemingly has a way to put you “out of business” with something and then let that something “die off” in exchange of yet a new “something”, which they sort of force feed you to get. In this case it would probably be the Microsoft SQL 2008. So basically I think Access will be in the retirement phase unless Microsoft decides to re innovate it. Maybe they’ve been working on Access.NET :p

  4. This topic has spurned some curiosity on my part. I’ll be looking into this as I keep searching for more information on database and DBMS. This comes from the fact that I found a professional site that seems to actually flourish from Access: GI Business Solutions

  6. I’ll probably be borrowing your blog’s link. Specifically to point out this discussion here. If anything I can also borrow the article and post it at my blog again with the link’s here for anyone to discuss the matter. Well I guess I will contact GI, find out how well their company does. They seem to be hardcore Access users.

  7. Also I wanted to mention. It might be weird but MS Access is STILL considered among the top 5 desktop databases as read from here

    So this is what I “think” might be happening.

    MS Access is the desktop one offering a quick, powerful, inexpensive solution for small apps good for small businesses. Since it comes with MS Office most people will be familiar with it. This can also be seen from GI Why MS Access section.

    Then MS covered the server DBMS area with MS SQL Server 2008. And I quote: “Also, Access serves as a great front-end for any ODBC-compliant server database (like SQL Server or Oracle).” So perhaps MS was thinking on terms of desktop= MS Access, server= MS SQL Server, which = money given you’d have to get SQL Server for high end apps. Or so that might be what it seems. I might be wrong though. I contacted GI maybe they know something we don’t since they work extensively with Access. I hope they answer as well as the barrage of questions I threw their way 😉

  9. Interesting post Tim

    You said “I have never much enjoyed programming with Access, but used to like it for interactive work and reports. I rarely use it now, for the reasons stated above.”

    What do you use instead?

    Dennis

  11. Hi Tim,

    I thought Visual Studio was a graphical front end to create underlying systems? Perhaps in VB.NET or C#?

    I used Delphi many years ago, I did not realise it was still going, the company I worked for at the time would not buy it, they were strictly ‘Microsoft’.

    Dennis

  13. Well, LightSwitch is the way moving forward, but obviously couldn’t reuse a thing from your Access app…

    But I don’t think it’s a good idea either, wish there are more options for users to take the advantages of the web database, as well as local storage when offline (using HTML5 indexed db or something cleve)…

  14. I’e been saying this for years now. There needs to be a .net app that repalces access, basically allowing rapid forms generation, native storage using sql ce, connectivity to remote databases (and with capabilities to do joins on tables on different servers- this was one of the most powerful things I used to use access for). Make it all use a .net api, and allow modifying the built-in forms etc if a developer wishes to enhance it. There’s gotta be a place in the market for this, a quick small database and forms generator, with the capability to scale up larger to a remote db etc, just like access was used for.

  15. I’ve used Access since ’95 for rapid database development that in many cases have worked as prototyping for larger systems to be. Through this experience I’ve been able to do this work interactively with the end users – almost like writing specs for the system but involving the users directly and thus committing themselves because they’ve actually seen ‘it’ working. In some occasions we have successfully used the Access Upgrade Wizard transferring data and structure to SQL Server in less than 15 minutes.

    Unfortunately MS has changed the interface from 2007 onwards making it much more time consuming for developers to use – after 3 years with 2007/2010 I still spend at least! 15% more time compared to developing using Acc2003. Good enough if one is paid by the hour – otherwise a real pain.

Comments are closed.