Issue Related to Join in Ms Access Database

MS Access INNER JOIN/LEFT JOIN problems

Had help from a friend and we discovered that it was a casting problem between a linked Oracle table and the Access table. To fix the problem we casted both sides of the linked fields to a string:

CSTR(D.[MATERIAL_NUMBER]) = CSTR(V.[ITEM_CODE])

How does MS Access join linked tables (linked to the same SQL server database)?

I've looked at SQL Profiler (unfortunately i don't have the actual log handy) and here's what i found:

• it selects all records from both tables
• for each record in one table calls an UPDATE
  So this will be slow.

MS Access Inner Join doesn't recognize difference between small and capital letters?

For small data sets, there are any number of approaches using various possible string conversions.

But if your data sets are of any size at all, this will be very slow because it can't use the indexes.

You could possibly optimize by joining case insensitively and then using criteria to test whether the case is the same, e.g.:

  SELECT * 
  FROM a INNER JOIN b ON a.id=b.id
  WHERE Asc(a.id) <> Asc(b.id)

This would at least allow the use of an index join so you wouldn't be comparing "a" to "b" and "a" to "c" (as would be the case with joining on string functions), but only "a" to "a" and "a" to "A".

I would suggest that if your data really needs to distinguish case, then you probably need to store it in a database engine that can distinguish case in joins and then pass your SQL off from Access to that database engine (with a passthrough query, for example).

EDIT:

@apenwarr suggests using StrComp() in the JOIN (as did @butterchicken yesterday), and this SQL raises a question for me (I've updated her/his SQL to use the same table and fieldnames I use above; it's essentially the same as @butterchicken's SQL)

  SELECT * 
  FROM a INNER JOIN b
    ON a.id = b.id 
    AND StrComp(a.id, b.id, 0) = 0

It is a fact that Jet will optimize a JOIN on an index exactly the same way it would optimize the equivalent WHERE clause (i.e., implicit JOIN). Stripped down to just the JOIN (presumably on indexed fields), these two SQL statements will be optimized identically by Jet:

  SELECT * 
  FROM a INNER JOIN a
    ON a.id = b.id 

  SELECT * 
  FROM a, b
  WHERE a.id = b.id 

My question is whether or not these three will optimize identically:

  SELECT * 
  FROM a INNER JOIN b
    ON a.id = b.id 
    AND StrComp(a.id, b.id, 0) = 0

  SELECT * 
  FROM a INNER JOIN b
    ON a.id = b.id 
  WHERE StrComp(a.id, b.id, 0) = 0

  SELECT * 
  FROM a, b
  WHERE a.id = b.id 
    AND StrComp(a.id, b.id, 0) = 0

I'm using SO to avoid work I'm supposed to do for tomorrow, so don't have time to create a sample database and set up SHOWPLAN to test this, but the OP should definitely give it a try and report back on the results (assuming he/she is definitely intending to do this with Jet).

Inner join across multiple access db's

If you have access to the MDBs, and are able to change them, you might consider using Linked Tables. Access provides the ability to link to external data (in other MDBs, in Excel files, even in SQL Server or Oracle), and then you can perform your joins against the links.

I'd strongly encourage performance testing such an option. If it's feasible to migrate users of the Access databases to another system (even SQL Express), that would also be preferable -- last I checked, there are no 64-bit JET drivers for ODBC anymore, so if the app is ever hosted in a 64-bit environment, these users will be hosed.

---

Related Topics