Single-record append query without warnings on duplicates

[HH]

To append a single record, without getting a warning when a record
already exists, I found out the following syntax works fine as Query
in MS Access:

first create a simple 'products' table...

CREATE TABLE products (code TEXT(10), description TEXT(100));

then try this...

INSERT INTO products (code, description)
SELECT code, description FROM (SELECT Count(*) AS X, 'ABC' AS code,
'Product ABC' AS description FROM properties) as sub
WHERE code NOT IN (SELECT code FROM products WHERE code = sub.code);

somebody having a better solution for this?

 

[Marshall Barton]

To append a single record, without getting a warning when a record
already exists, I found out the following syntax works fine as Query
in MS Access:

first create a simple 'products' table...

CREATE TABLE products (code TEXT(10), description TEXT(100));

then try this...

INSERT INTO products (code, description)
SELECT code, description FROM (SELECT Count(*) AS X, 'ABC' AS code,
'Product ABC' AS description FROM properties) as sub
WHERE code NOT IN (SELECT code FROM products WHERE code = sub.code);

somebody having a better solution for this?

If properties is a table that contains the record to be
appended to the products table, I would try using:

INSERT INTO products (code, description)
SELECT P.code, P.description
FROM properties As P
LEFT JOIN products As X
ON P.code = X.code
WHERE X.code Is Null

If you really are inserting values instead of data from
another table, then I would prefer to use code in a form to
check for an existing record before running the query:

If DCount("*", "products", "code=" & Me.txtCode) = 0 Then
db.Execute "INSERT INTO products (code, description) " _
& "Values('" & Me.txtCode & "', '" _
& Me.txtProduct & "')"
Else
MsgBox "already existes"
End If