Access VBA - if record does not exist then insert to another table

Question

Completely new to Access VBA so please be nice!

I have tblNew and tblTeam.

I want to loop through each record in tblNew to check if it already exists in tblTeam. The identifier is the email address. If the record does not exist then I want to add the record to tblX.

I'm stuck on searching to check if the record exists and how to loop through each record in the tblNew.

Apologies if I've duplicated any questions, I really couldn't understand or find what I was looking for.

Are you searching to see if the entire record is in tblTeam, or just the unique identifier of EmailAddress? — Doug Coats
– Doug Coats, Commented Aug 22, 2018 at 21:06
Don't "Loop" through a table. That is an absolute last resort. Instead get a "Set" that matches your definition SELECT tblNew.emailAddress FROM tblNew LEFT OUTER JOIN tblTeam ON tblNew.emailAddress = tblTeam.EmailAddress WHERE tblTeam.EmailAddress IS NULL GROUP BY emailAddress that will give you a list of distinct email address in tblNew that isn't tblTeam already. Now stick INSERT INTO tblX (emailAddress) <that same SELECT statement> to insert them all at once. No loops and just on SQL statement will do this. — JNevill
– JNevill, Commented Aug 22, 2018 at 21:11

Lee Mac · Accepted Answer · 2018-08-22 21:13:34Z

2

VBA is not necessarily required for this task - it can be quite easily achieved using an INSERT statement (aka Append Query), for example:

INSERT INTO tblX
SELECT 
    tblNew.*
FROM    
    tblNew LEFT JOIN tblTeam ON tblNew.EmailAddress = tblTeam.EmailAddress
WHERE 
    tblTeam.EmailAddress IS NULL

answered Aug 22, 2018 at 21:13

Lee Mac

16.1k6 gold badges38 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access VBA - if record does not exist then insert to another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related