Access and OleDb for the SQL Server programmer

I have been working on a project that uses MS Access, accessed from C# via OleDb. The following are some of the things I’ve discovered. (This list will be updated as time goes on.)

1. Joins need to be encased in nested parentheses, like so:

Select *
From  ((Tbl1 t1
  inner join Tbl2 t2
    on t1.Id = t2.Id)
  left outer join Tbl3 t3
    on t1.Id = t3.Id)

Incidentally, if you have fallen into the habit of dropping the “inner” and “outer” from your join statements, break it. Access requires them.

2. It doesn’t matter what you call your parameters: OleDb will apply the supplied variables in the order the parameters are used. So if you have this SQL statement:

Select *
from   Tbl1
where State = @State
   and (ZipCode = @ZipCode or @ZipCode is Null)

… you need to supply the value for @State first, and then @ZipCode, because that’s the order in which they’re used. (The official way to do parameters in OleDb is with ? rather than a parameter name, but if you use your parameter more than once (like @ZipCode, above), you’d have to provide a value for it, each time.)

3. BUT. There is at least one exception to the previous. If you use the parameters in subqueries in your WHERE clause, those parameters will be supplied with values first, even if another variable is used earlier in the WHERE clause. So if your query looked like this:

Select * from Tbl1 t1
where State = @State
and (select *
  from Tbl2 t2
  where t1.Id = t2.Id
  and t2.ZipCode = @ZipCode)

… you would need to provide the value for @ZipCode before that for @State.

Mercifully, there’s a way to check this and get the necessary order. Open Access, begin a query, switch to the SQL view, and paste in your SQL. Then execute it. The order in which Access prompts you for parameters is the order in which you need to provide the parameters in OleDb.

4. If you’re calculating columns in your query, the syntax “Result = a + b” won’t work. It’ll treat Result as a parameter. You need to use the older syntax “a + b as Result”.