Using insert into where not exists in VBA

Question

I have a lovely form and a lovely table in MS access (I promise). I would like to insert into this table at the press of a button using where not exists but I am getting a not-so-friendly run-time error 3067: "Query input must contain at least one table or query."

My query already does...

strSQL = "insert into tbl_MAP_systemTask (TaskID, SystemID) " & _
            " Values (" & taskID & ", " & sysID & _
            ")  where not exists " & _
            " (select M.TaskID, M.SystemID from tbl_MAP_systemTask as M where M.TaskID = " & taskID & _
            " and M.SystemID = " & sysID & ");"

Debug.Print strSQL
DoCmd.RunSQL (strSQL)

strSQL is now

insert into tbl_MAP_systemTask (TaskID, SystemID)  
Values (1, 1)  
where not exists  
(select M.TaskID, M.SystemID 
from tbl_MAP_systemTask as M where M.TaskID = 1 and M.SystemID = 1);

Can anyone shed any light on
a) what I broke?
b) how to fix it?

PaulFrancis · Accepted Answer · 2015-02-19 12:01:23Z

3

Well instead of using a SubQuery, you could use a Domain function to get this going,

If Dcount("*", "tbl_MAP_systemTask", "TaskID = " & taskID & " AND SystemID = " &sysID) = 0 Then
    strSQL = "INSERT INTO tbl_MAP_systemTask (TaskID, SystemID) " & _
             " VALUES (" & taskID & ", " & sysID & ")
    CurrentDb.Execute strSQL
Else
    MsgBox "The Data already exists in the table, so nothing was added."
End If

answered Feb 19, 2015 at 12:01

PaulFrancis

5,8191 gold badge23 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JohnHC Over a year ago

Well that one did the job. You are a gentleman and a scholar!

EngJon · Accepted Answer · 2015-02-19 11:32:34Z

0

Try this:

strSQL = "insert tbl_MAP_systemTask (TaskID, SystemID) " & _
            " select " & taskID & ", " & sysID & _
            " where not exists " & _
            " (select M.TaskID, M.SystemID from tbl_MAP_systemTask as M where M.TaskID = " & taskID & _
            " and M.SystemID = " & sysID & ");"

=>

insert tbl_MAP_systemTask (TaskID, SystemID)  
select 1, 1
where not exists  
(select M.TaskID, M.SystemID 
from tbl_MAP_systemTask as M where M.TaskID = 1 and M.SystemID = 1);

and seems to work in my case. Seems like the where not exists needs a select statement, so you have to model your insert like this.

answered Feb 19, 2015 at 11:32

EngJon

9979 silver badges20 bronze badges

Comments

izzymo · Accepted Answer · 2015-02-19 11:36:29Z

0

Maybe you can use a recordset to insert these values.

Dim rs as Recordset
Set rs = Currentdb.openRecordset("SELECT * FROM tbl_MAP_systemTask WHERE TaskID=" & Me.TaskID & " AND SystemID=" & Me.SystemID)

if not (rs.eof or rs.bof) then
 rs.addnew
 rs.TaskID = Me.TaskID
 rs.SystemID = Me.SystemID
 rs.update
end if

rs.close
set rs = nothing

answered Feb 19, 2015 at 11:36

izzymo

9361 gold badge10 silver badges14 bronze badges

1 Comment

JohnHC Over a year ago

Nope, this one threw a whole collection of unsupported method errors.

The Hungry Dictator · Accepted Answer · 2017-04-19 07:21:55Z

0

TOP 1 clause is must in the main query.

INSERT INTO tbl_MAP_systemTask (TaskID, SystemID)
SELECT TOP 1 1 AS TaskID 1 AS SystemID
FROM tbl_MAP_systemTask
WHERE NOT EXISTS (SELECT TOP 1 TaskID,SystemID FROM 
tbl_MAP_systemTask WHERE TaskID = 1 and SystemID=1);

If tbl_MAP_systemTask table is empty or if there is only one record in the table then TOP 1 clause must be omitted in sub-query. I have included Top 1 clause is sub-query for performance purpose.

edited Apr 19, 2017 at 7:21

The Hungry Dictator

3,4845 gold badges41 silver badges56 bronze badges

answered Apr 19, 2017 at 4:28

ShamBhagwat

994 bronze badges

Collectives™ on Stack Overflow

Using insert into where not exists in VBA

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related