INSERT INTO without Duplicates

[cefrancke]

I'm trying to insert records in a table that has a "no duplicates"
index on two columns.

The following snippet shows where I started off...

INSERT INTO Table_A (ID_A, ID_B)
VALUES (334, 2057)

However, if these two values exist already as a "key" combination then,
the MS Access
error on the index raises (no duplicates on the index).

The following snippet shows what I want to do (in theory)...

INSERT INTO Table_A (ID_A, ID_B)
VALUES (334, 2057)

WHERE ((ID_A <> 334) AND (ID_B <> 2057))

This of course, does not work.
But after research, it is suggested to try this...

INSERT INTO Table_A (ID_A, ID_B)
SELECT 334, 2057 FROM Table_A
WHERE
NOT EXISTS (SELECT ID_A, ID_B FROM Table_A
WHERE ((ID_A <> 334) AND (ID_B <> 2057)))

This works in MS Access, but only if the Table_A has records.
If this is run on the first try, where there are no records in Table_A
(say in the beginning of a project), the INSERT does not "insert"
records. (I guess because the first SELECT returns no records)

SQL Server allows this with no reference to a table.
One can just issue a SELECT statement with "hard" values and no table
reference. The result will be the "hard" values.

In SQL Server....

SELECT 334, 2057

returns

334 2057
Is there a way to do this in MS Access, or do I just need to catch the
error code in the VBA code that runs the SQL statement? (I run this SQL
on a button click event.)
Thanks In Advance,

Christopher

[pietlinden]

If you do something like

strSQL = "INSERT INTO ..."

and then

currentdb.execu te strSQL, dbFailOnError

duplicate values will fail, but quietly. So your code will continue.