Wednesday, October 30, 2013

Provider Microsoft.Jet.OLEDB

In the past couple of years, I've worked with several DBMS: Oracle, SQLServer, MySQL, DB2, SyBase, PostgreSQL and MSAccess. And in several "flavors". I even worked with MySQL when it had no Stored Procedures support! Which one is the best? Well, as most answers in the IT world: it depends. Just like that! However, the most "Buggy" to me is undoubtedly MSAccess. And if the idea is just to have the database file along side a simple WPF app and connect to it using JET OleDBProvider, then it gets even worse!

Here goes a simple one:
      ISNULL is a Built-in function available in SQLServer. So, one might wonder: MSAccess is just another product in the Microsoft product line. It probably has this. Well, no, it doesn't. It has the NZ function. It does have IsNull, but with a different meaning (VB Style).

So far, I'm cool with it. But if you're going to connect using Microsoft OLEDB Provider for Microsoft JET, the "NZ" function is not available. At the best, you have the "ugly" IIf. Don't like!
I know that MSAccess is from another Microsoft's product family and, for historical reasons, it has its "VBLook", but damn it's annoying!

More info:
Cool article on some MSAccess "Common Query Hurdles";
Yet another bug list: Allen B. tips for Microsoft Access (Scroll down to the "Flaws in Access")
(I've already had the displeasure to see some of them live!)