Tuesday, December 12, 2006

Microsoft's Data Access APIs: ODBC to LINQ

Mike Pizzo, an Architect on the Data Programmability team at Microsoft, has started a four part series that captures the history of Microsoft's alphabet soup of data access APIs and the layout of acronyms to come. Here are the topics, with a link to the first two:

I'll update this post as new episodes hit the blog.


It's back to the "early years" for Access 2007. The Access team now recommends abandoning Access Data Projects (ADPs) with SQL Server tables connected by OLE DB to DocObject front ends and ADODB in favor of old-timey ODBC-linked tables and DAO.

Here's Mike's take on ODBC and DAO in Access:

And then something happened. Visual Basic became popular as a scriptable "automation language". ODBC, being a C-style interface, was not directly consumable from VB. However, some of you clever folks figured out that Microsoft Access supported executing queries against ODBC Datasources, and that Access did support scriptable automation through its Data Access Object (DAO) API. Voila! Now you could write applications against ODBC sources using VB.

However, DAO went through Access's internal "Jet" (Joint Engine Technology) database engine, which defaulted to building local keysets for each result in order to do advanced query processing and cursoring against the remote data. This was fine if you needed that functionality, but significant performance overhead and additional round trips when you didn't.

Déjà vu all over again! DAO is the only API that supports the new Access Attachment data type, multi-select lookup fields, and "rich-text" and append-only memo fields of SharePoint lists.

So the Access team now suggests that developers abandon SQL Server 2005 Express back ends in favor of links to non-relational Windows SharePoint Services (WSS) 3.0 or Microsoft Office SharePoint Services (MOSS) 2007 lists.

Updated: 12/14/2006 for Part II; 12/24/2006 for Part III; 1/24/2007 for Part IV