Access 2003: Join expression not supported with VBA type conversion

One of the stranger bits of my life is that I often do data manipulation using Microsoft Access 2003 in a Windows 2003 server VM.

 

It's a long story. I suspect I'm not completely alone however. Access 2003 is unbeatable for certain kinds of data wrangling across disparate data sources. Access 2007 was a quite severe regression (post-2000, software  regressions are increasingly common).

 

So perhaps it's worth sharing the weirdest bug I've seen in a while. I was unable to do a "left" join on two subqueries where one of the keys involved a type conversion (string/long using VBA CStr() or CLng() - I tested both ways). I got a meaningless "Join expression not supported" error message.

 

I think this has worked fine for years. I wonder if a forced security update broke something. I found if I switched the query from 'Dynaset' to 'Dynaset (Inconsistent Updates)' it worked. Snapshot didn't work.

 

Now you know.

 

For me, it's a sign that I either need to switch to Access 2007/2010 or switch jobs. (I don't think 2010 is 64bit, so I still face the problems with ODBC drivers and bitness. I might be better to try Access 2007 in an XP VM.)

Update: I'm seeing other issues. It feels like something very bad has happened, probably as a result of a Microsoft security update, but I can't tell what.

 

Update 7/4/2012: I'm not quite sure what's going on, still having some issues. It might be an ODBC issue. I think the error message is a red herring.