MS Access Add Row if it does not exist


I have an Access DB with two tables.  One of them (Table1) has most (but not all) of the records in the other table (Table2).  I would lke to write a script which loops through all of the records in Table2, and checks to see if they already exist in Table1 (by UPC).

If they do not exist, then I need to add them.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
You can do it without having to manually loop through the records.

I would recommend using an append query that looks for unmatched records.

hi trman,
- in the following code I use Table1 and Table2 as a sample where they have almost identical field.
- line 1: i specify the target table Table2 and target column
- line 2: i specify which column in Table1 that i need to be inserted into Table2. the column name might not be the same.
- line 4: i put a WHERE clause to compare all the ID (Primary Key data) in Table1 does not exist in ID from Table2

INSERT INTO TABLE2 ( ID, Field1, Field2, Field3 )
SELECT T1.ID, T1.Field1, T1.Field5, T1.Field7
WHERE (((T1.ID) Not In (Select ID from TABLE2)));

- if u running this SQL in Access, you might see this error in the status bar "This action or event has been blocked by Disabled Mode."
- to enabled the INSERT command to execute in Access query, go to "Database Tools" menu.
- under "Show/Hide" panel, click the "Message bar" (turned it on)
- then a message bar will be displayed as "Security Warning. Certain content in the database has been disabled" with a "Options" button
- click the "Options" button and choose "Enable this content" and click "OK" button.
- after that it will run the INSERT command
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
OP_Zaharin, thanks for posting the sample SQL.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

trmanAuthor Commented:
this crashes access when I run it..I also tried a variation of Not exists but it too crashes it.  I looked to see if have the Disable Mode from the above steps, but could not find it.
- do you mind sharing your access file with us?
- what version of access are you using?
- i run the statement above on Access 2007 and it works fine.

- i'm sharing you my sample Access file that i created to simulate the SQL above.
- once open, there will be a "Security Warning" panel appear below the menu panel (it is not pop-up so look for it carefully). click on "Option" button and select "Enabled this content"
- on the left side, you will see 2 table with a similar column which is tblStaff1 and tblStaff2. tblStaff2 have slightly different name and extra column on it. you can check that tblStaff1 have 3 records and tblStaff2 1.
- you will also see a query called "QueryAppend" on the list. right-click on the name and choose "Design View" you will see a similar SQL as i share with you above.
- next step is to run the "QueryAppend". close all open table and query window. double click on the "QueryAppend" OR open the "QueryAppend" then click "Design" from the menu above, and click on "Run" (!) icon on the panel.
- there will be a message box asking for confirmation to append X rows of data so answer yes. you will see the data that is not exist in tblStaff1 being copied over from tblStaff1 to tblStaff2.
- make sure all tables are closed first, if not you won't see the changes correctly


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trmanAuthor Commented:
hello OP

Thank you for your comments.  Unfortunatley, I cannot share the file because it is private property of my client and I signed a NDA.  However, the task has been put on hold for now by my client and he has opted to go another direction for now.  I will be awayrding you this solution for your attempts.  I am sure if Ineeded to I could implement it following these instructions in the future.  By the way, I am using Access 2010
hi rman,
- thank you for the points. i would recommend you to download my access file as per posting above, and have a look at how it runs there, incase your client re-consider that option again  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.