 INSERT INTO without Duplicates
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
| 
