MS ACCESS SQL Join Subquery

Question

I have two tables: newparts, storedparts

I insert the parts of the newparts, which are not jet in the storedparts into the storedparts:

SQL_String = "INSERT INTO storedparts " & _
             "SELECT newparts.* " & _
             "FROM storedparts " & _
             "RIGHT JOIN newparts ON (storedparts.identifier = newparts.identifier) AND (storedparts.timeStamp = newparts.timeStamp) " & _
             "WHERE ((storedparts.AutoID) Is Null);"

This is working fine so far. Now the Problem: Table storedparts is getting so big that the programm is taking too Long for the join process. My solution: Just compare the newparts not to all parts of the storedparts, but just to parts that aren't older than 4 days... I tried a subquery like this, but i can't get it to run.

SQL_String = "INSERT INTO storedparts " & _
             "SELECT newparts.* " & _
             "FROM storedparts (WHERE storedparts.timestamp > Now() - 4) " & _
             "RIGHT JOIN newparts ON (storedparts.identifier = newparts.identifier) AND (storedparts.timeStamp = newparts.timeStamp) " & _
             "WHERE ((storedparts.AutoID) Is Null);"

Any help is appreciated.

Answer 1

This wouldn't be a problem if your tables have indexes.

CREATE INDEX ndx_sp_identifier ON storedparts (identifier);
CREATE INDEX ndx_np_identifier ON newparts (identifier);

Then I suggest you change your query to something like this as @jarlh pointed out.

INSERT INTO storedparts 
SELECT newparts.* 
FROM newparts
LEFT JOIN storedparts 
ON newparts.identifier = storedparts.identifier
AND newparts.timeStamp = storedparts.timeStamp
WHERE storedparts.AutoID Is Null;