Programming with Access? Know this about JOIN.

If you’ve ever implemented a small-scale WinForms .NET project with modest database needs, you’ve probably been tempted to keep it “simple” by using Microsoft Acess (.mdb) files.

This approach has some nice advantages:

• Most users already have Microsoft Office, so if they need to run reports or just look at their data they already have the tools.
• On the other hand, Windows already comes with the OLEDB drivers for Access, so it’s okay if your user hasn’t acquired Access itself.
• You don’t have to acquire or pay for a separate database engine.
• Runtime overhead is relatively light.

Access doesn’t do well under moderate-to-heavy load, but for smal-scale data entry applications I find the performance more than acceptable.

Within .NET, it’s often easiest to create your own queries with the OleDbCommand class. Here’s a quick example:

using System.Data.OleDb;

public int getFooCount()

{

string sql = "select count(*) from foo";

OleDbCommand cmd = new OleDbCommand(sql);

// SET cmd.Connection TO A VALID OLEDBCONNECTION OBJECT HERE.

return Convert.ToInt32(cmd.ExecuteScalar());

}

Now suppose you need a SQL JOIN:
public int getBarValue()
{
string sql = "select bar.field1 from foo join bar on foo.key0 = bar.key0";
 OleDbCommand cmd = new OleDbCommand(sql);
 // SET cmd.Connection TO A VALID OLEDBCONNECTION OBJECT HERE.
 return Convert.ToInt32(cmd.ExecuteScalar());
}

So far so good. But what if you need to join three tables together? This should work, right?
public int getBazValue()
{
string sql = "select baz.field2 from foo join bar on foo.key0 = bar.key0 join baz on bar.key1 = baz.key1";
 OleDbCommand cmd = new OleDbCommand(sql);
 // SET cmd.Connection TO A VALID OLEDBCONNECTION OBJECT HERE.
 return Convert.ToInt32(cmd.ExecuteScalar());
}

Whoops. At runtime–not build time!–you get an error from OLEDB: “Syntax Error in JOIN Command.” I agree that there is nothing wrong with your command, in any sane SQL environment. Try nesting the JOIN statements in parentheses though:
public int getBazValue()
{
string sql = "select baz.field2 from (foo join bar on foo.key0 = bar.key0) join baz on bar.key1 = baz.key1";
 OleDbCommand cmd = new OleDbCommand(sql);
 // SET cmd.Connection TO A VALID OLEDBCONNECTION OBJECT HERE.
 return Convert.ToInt32(cmd.ExecuteScalar());
}

In other words, Access SQL doesn’t accept three-way JOINs. But it likes nested two-way JOINs. I have successfully joined about six tables at one time with this technique.