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. <\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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. … Continue reading Microsoft Access needs a complete rethink – or retirement<\/span>