MS Access Insert where not exists

Question

I have the following table:

+-----------+--------+
| FirstName | Active |
+-----------+--------+
| Rob       | TRUE   |
| Jason     | TRUE   |
| Mike      | FALSE  |
+-----------+--------+

I would like to insert 'John' (with Active=True) only if an entry for John doesn't exist already where Active=True. I try the following:

insert into testTable (FirstName, Active) values ('John',True) where not exists (select 1 from testTable where FirstName='John' and Active=True)

but i get 'Query input must contain at least one table or query'.

Can anybody help with what I am trying to achieve?

Ternion

I'm sorry about the format of the table. I am still not sure how to paste a table onto here (any help there would also be appreciated..) – Ternion

Answer 1

You can't combine Values with a WHERE clause. You need to use INSERT INTO ... SELECT instead.

Since you don't want to insert values from a table, you need to use a dummy table like this (assuming that testTable has an index):

INSERT INTO testTable (FirstName, Active) 
SELECT 'John', True
FROM (SELECT MIN(id) FROM testTable)
WHERE NOT EXISTS (select 1 from testTable where FirstName='John' and Active=True)

SELECT MIN(id) FROM testTable always returns a dummy table even in case testTable is empty.

Datura

Thanks, this works if I am executing directly in Access, but given I am generating the input query via a VB script and executing it from there with and ("ADODB.Connection") (Provider=Microsoft.ACE.OLEDB.12.0), I get an 'Record(s) cannot be read; no read permission on 'MSysObjects'. Any other ideas? thanks a lot – Ternion

Well, use testTable instead of MSysObjects as your dummy table if you're sure that always contains records, or create your own dummy table. – Datura

Answer 2

In my case the field already exist in the table so I changed it from an INSERT to an UPDATE query and it worked.

Definition