CODEWITHSUNDEEP
ms-accessvba
Dan
Dan

Reputation: 15

Insert into Access DB using VBA trying to exclude duplicates but getting an error

I'm sure it's something simple, but, I keep getting an error when trying to run this. I've create a DB and want to import some data. I've parsed the import file, and assigned the relevant data to variables.

Next step is to insert into the DB, which I've managed, but, I'd like it to ignore the row if it finds a duplicate.

The bit of code is:-

strSQL = "INSERT INTO PubHol " & "(HolidayDate, HolidayName)" & " VALUES (""" & dtOutDT & """, """ & strOutDesc & """ )" & " WHERE NOT EXISTS (SELECT * FROM PubHol as ph WHERE ph.HolidayDate = CDate ('" & dtOutDT & "'))"
MsgBox strSQL
dbCurr.Execute strSQL, dbFailOnError

The MsgBox shows the following output:-

---------------------------
Microsoft Access
---------------------------
INSERT INTO PubHol (HolidayDate, HolidayName) VALUES ("01/01/2018", "New Year's Day" ) WHERE NOT EXISTS (SELECT * FROM PubHol as ph WHERE ph.HolidayDate = CDate ('01/01/2018'))

All of which seems to be a valid SQL statement to me, but I get a VB Error

Run-time error '3067' Query input must contain at least one table or query.

Can anyone shed any light on where I'm going wrong?

Or any other tips on avoiding duplicates getting added to my DB?

Thanks

Upvotes: 0

Views: 1174

Answers (2)

Zev Spitz
Zev Spitz

Reputation: 15327

You can't use the INSERT INTO .. VALUES syntax together with a WHERE clause. (I would guess this is because WHERE is designed to limit the records being inserted, which only makes sense if you're trying to insert multiple records; VALUES in Access SQL inserts a single record.)

Ideally, you should apply a constraint and handle the resulting error, as in Parfait's answer.

If you can't do this, (because the constraint only applies to the import and not the regular business data, or for some other reason), then either make the decision whether to INSERT the records in VBA, not in SQL:

Dim recordCount As Integer
'get the number of records matching the criteria
If recordCount = 0 Then
    'INSERT records here
End If

Or, if you still want to do this completely in SQL, I would suggest the following:

INSERT INTO PubHol (HolidayDate, HolidayName)
SELECT TOP 1 "01/01/2018", "New Year's Day"
FROM dummy
WHERE NOT EXISTS (
    SELECT * 
    FROM PubHol as ph 
    WHERE ph.HolidayDate = CDate ('01/01/2018')
)

dummy is any table in your database which has at least one record.

NB. You might want to consider using SQL parameters; you could avoid having to manually construct string and date literals, and also to avoid the date conversion within the query.

Also, it seems as though you are importing a single record at a time. If you can create a linked table to your import source, it might be better instead to create an INSERT query which would massage the data from the linked table into the right shape for the destination table.

Upvotes: 2

Parfait
Parfait

Reputation: 107632

In Access SQL dialect, you cannot run WHERE without a data source in query. And looking specifically, you may do well with a constraint of unique values on HolidayDate and HolidayName that disallows duplicates:

dbCurr.Execute "ALTER TABLE [PubHol] ADD CONSTRAINT uniq_holiday" _
                 & " UNIQUE (HolidayDate, HolidayName)"

Then run a regular parameterized append query where any attempt to add duplicate values will return as an exception and be rolled back.

SQL (save as a query object, resuable for other holidays)

PARAMETERS [HolidayDateParam] Datetime, [HolidayNameParam] TEXT(255);
INSERT INTO PubHol (HolidayDate, HolidayName)
VALUES ([HolidayDateParam], [HolidayNameParam])

VBA 

Dim qdef As QueryDef

...
Set qdef = dbCurr.QueryDefs("mysavedquery")

qdef![HolidayDateParam] = dtOutDT
qdef![HolidayNameParam] = strOutDesc

qdef.Execute dbFailOnError

Set qdef = Nothing

Upvotes: 2

Related Questions