case sensitive join

[agr1259]

I have a query joining 2 SQL 2000 database tables (ODBC connection) the
tables are both case sensitive, however when I return the results in the
query it is matching records where the capitalization is not the same. ie.
One table has Ab and the other table has records Ab and AB the query matches
both and returns double records. The database was created in Access 2000 and
is still in Access 2000 format, I am now running Access 2007. Has anyone else
encountered this?

 

[Kristibaer]

You could try going into Design View and either checking the format of the
column to see if you can force the display to ignore case and see (and
display) your records of that column in a uniform format. You could also try
high-lighting the column (still in design view) to group your data with
totals. If you still get duplicate results, check your query results to see
if other data in your query returned in other colums with Ab and AB are
different. That would force this column to display seemingly duplicate
results.

Hope this helps

 

[Kristibaer]

Are there other fields that could be joined besides Ab and AB? It sounds like
the join properties are forcing the match between Ab and AB.

 

[John W. Vinson]

The problem is it is a 1 to 1 join. The join shouldn't be making a match.
"Ab" should not match "AB" in the second table. I know the fields are case
sensitive, becuase if I filter them individually "Ab" does not return result
"AB" This query functioned as intended when running Access 2000 and 2003.

That's very odd indeed, because Access 2000 and 2003 have *NEVER* been case
sensitive, and it's been quite difficult to force a case sensitive join. I
don't know what provision A2007 makes for such joins, but I'd suggest you
recheck how your previous versions implemented this!

John W. Vinson [MVP]

 

[agr1259]

The tables are SQL tables linked into Access, and since SQL has been
configured to be case sensitive Access does not ignore that fact. Well it
didn't ignore it untill 2007. I know my tables are case sensitive becuase if
I filter them I have to capitalize correct.

 

[Michel Walsh]

Make a VIEW in MS SQL Server that will make the join. From Access, pump the
data from the MS SQL Server view, (or from a store procedure, or even from a
ODBC-direct query).


If you are using a dot-mdb file, JET is in charge and JET does not make
comparison case sensitive (by default). Jet has to be in charge, since the
linked tables can be from MS SQL Server, or from Excel, or from ... name
who. Since Jet is in charge, it asks to other 'engine' about their data, but
in a way Jet can understand (here, not case sensitive) since, consider for
a moment, the join may involves a table from MS SQL Server and a table from
Excel.

If you are using a dot-adp file, then MS SQL Server is in charge, and JET is
not present at all. Maybe you convert your dot-adp application into a new
dot-accdb file? That would explain what you described: before, MS SQL Server
was indeed, in charge, but now, it is Jet ! If so, again, have the join
being made by MS SQL Server, through a view, a stored proc, an ODBC-direct
query, ...


Vanderghast, Access MVP