Compare data between 2 tables before query

[VeryNew]

Hello,
I have an append query that updates a permanent table with data from a temp table. (the temp table contains data from an imported text file). What i need to do is BEFORE the append query is run I'd like to make sure that the data in the temp table is not already in the permanent table.
Any idea how i could do this in code?

Thanks!

 

[MasterPO]

In code, you can simply execute a query that will return any records that are an exact match. If any records are found, these records already exist in the permanent table:

Dim rst as New ADODB.Recordset
Dim strSQL as String

strSQL = "SELECT * FROM temp_table INNER JOIN Permanent_Table ON Temp_Table.testfield = Permanent_Table.testfield;"

rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

If rst.RecordCount > 0 then
  Msgbox "Duplicate Records Found!"
End if
rst.close

 

[VeryNew]

Thanks!
I'll try it right now...

 

[VeryNew]

Sorry,
I should have mentioned I'm not too swift with all this yet.
What if i want only the records where ALL fields match except the autonumber PK - would i do the INNER JOIN Permanent_Table ON Temp_Table.testfield = Permanent_Table.testfield AND Temp_Table.testfield2 =Permanent_Table.testfield2;
AND testfield3 etc.? (When i do that i get an error with the SQL code)

 

[MasterPO]

You're on exactly the right track! Here is the SQL statement that joins three common fields in each table:

SELECT * FROM Temp_Table INNER JOIN Permanent_Table ON (Temp_Table.Testfield1 = Permanent_Table.Testfield1) AND (Temp_Table.Testfield2 = Permanent_Table.Testfield2) AND (Temp_Table.Testfield3 = Permanent_Table.Testfield3);

You could extend this to as many fields as needed (although I think there is a limit of 10 joins in an Access query). Fields do not need to have the same name, but they must be the same datatype or you will receive a 'Type Mismatch' error.