How to compare recordsets in access vba

[NeoPa]

i think we are talking about the sql in post no 10.
When i try it the query runs for more than 8 to 10 hours and then crashes

No. I'm referring back to my post #15, which is referring to Fish's post #4.

It's not as clearly stated as I'd intended it to be, so apologies for that. Let me try to clarify with an illustration. Assuming tables [T1] & [T2], each with four fields (A, B, C & D) to compare against as well as an AutoNumber index (ID), we would be looking at some SQL similar to the following :

SELECT T1.A AS [A1],
       T1.B AS [B1],
       T1.C AS [C1],
       T1.D AS [D1],
       T2.A AS [A2],
       T2.B AS [B2],
       T2.C AS [C2],
       T2.D AS [D2]

FROM   [T1] INNER JOIN [T2]
  ON  ((T1.A=T2.A)
  OR   (T1.B=T2.B)
  OR   (T1.C=T2.C)
  OR   (T1.D=T2.D))

This won't do the whole job for you, but it will avoid a full cartesian product and can be processed by your code much more quickly than that could.

This is what Fish was alluding to in post #4.

[atksamy]

Thanks Neopa I ll try to get something going through this and get back in case i get stuck

[NeoPa]

Sure thing. Bear in mind though, that some JOIN criteria are not able to be displayed in Design view. You may have to work on this in SQL directly if it comes to that.