Put array data into a table only if it doesn't exist

[jadams0173]

I want to put some data from an 2 dim array into a table if it doesn't exist. Here's what I'm doing.

For x = LBound(aryParentChild, 2) To UBound(aryParentChild, 2)

    conn.Execute "Insert Into tblMy_Parent_Child (Parent,Child) " _
        & "values ('" & aryParentChild(1, x) & "','" & aryParentChild(2, x) & "')", , 129
        
Next x

I'm stuck as to how to check to see that the data does not already exist.

I've tried

INSERT INTO tblMy_Parent_Child ( Parent, Child )
SELECT '188D5338G1Q375B' AS Expr1, '19D903197G3375A' AS Expr2
WHERE EXPR1<>'188D5338G1Q375B' and expr2<>'19D903197G3375A';

 

[lespaul]

the logic would be something like this (not sure the exact syntax for Access):

run query "SELECT * FROM tableName where all your criteria"

if the result set is empty
  then execute query "INSERT INTO ... where all your criteria"

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[AlexCuse]

If the table is not too large, perhaps you could bring the relevant portion into a recordset and use this recordset to remove any array items before you try to insert?

This would save you the if, and maybe a couple trips to the database.

Just a thought.

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[jadams0173]

Alex,

I had considered that an option as well. I think that is the road I'm going to take. Thanks to both you and Leslie for the responses.