SQL LIKE case sensitivity issue

I have an Excel 2007 (Win 7) utility worksheet that uses VBA/SQL to extract data from a table located on a separate Excel data worksheet. The code works fine whether or not the data worksheet is open in Excel, except for one thing (at least so far). If I use LIKE in the SQL (e.g. WHERE city_prov LIKE '%NS%') :
- if the data worksheet is open, this expression is case sensitive
- if the data worksheet is not open, the expression is case insensitive
So I can get completely different results depending on whether or not the data worksheet is open when I run the query. Does this make sense to anyone?

I want the LIKE to be case sensitive. I can ensure that this happens by adding code to open the data worksheet before the query is implemented. However, I would be curious to know why there should be any difference in the way the SQL is interpreted in these two cases.

Using Ucase would be fine if I wanted to ensure case insensitivity, but I want the opposite. I want the LIKE to be case sensitive. I want to pick up those cases where city_prov includes NS (e.g. Halifax, NS) but not ns (e.g. Saint Johns, NL). If the data worksheet is open, this is what I get. But I can't understand why the exact same SQL LIKE code is case insensitive if the data worksheet being queried is not open.

Hi Keyston,

That's not one I've come across before and sounds like a bug, but why don't you put in some code to open the source workbook in the background? That way you can guarantee the same results for all workbooks.

Cheers,
Dom

Originally Posted by Dom83

Hi Keyston,

That's not one I've come across before and sounds like a bug, but why don't you put in some code to open the source workbook in the background? That way you can guarantee the same results for all workbooks.

Cheers,
Dom

My original post indicates that this is exactly what I have done - but the mystery remains! If it is a bug, it may be fixed in Excel 2010.