How to use if not exists in access?

[halifaxdalRaymondXie]

Happy new year first.

I am just wondering if it is possible to use "if not exist" in Access, just like in SQL?

I want to insert record only when it is not there to prevent any duplicated records.

Here is what I have tried:

INSERT INTO PropertyForSale (Title) values('asdf') WHERE not exists (SELECT PropertyID FROM PropertyForSale where PropertyID = '12456085' )

I tested this query directly in Access DB, it throws me this error:
Query input must contain at least one table or query

If I change it a little bit to:
INSERT INTO PropertyForSale (Title) select 'asdf' WHERE not exists (SELECT PropertyID FROM PropertyForSale where PropertyID = '12456085' )

it throws error:
Syntax error (missing operator) in query expression 'asdf' wehre not exists (SELECT PropertyID FROM PropertyForSale where PropertyID = '12456085' )

If I put this query in my .NET, then it will throw a different error msg:
Missing semicolon ( at end of SQL statement.

My .NET code is as follow:

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim sqlCmd As String
sqlCmd = "INSERT INTO PropertyForSale (Title, Area, PropertyName, PropertyID, Model, PropertyType, PropertySize, TotalPrice, Price, PubDate, Contact, Phone, Company, Branch, Detail) " + _
"Values(@Title, @Area, @PropertyName, @PropertyID, @Model, @PropertyType, @PropertySize, @TotalPrice, @Price, @PubDate, @Contact, @Phone, @Company, @Branch, @Detail) " + _
" WHERE NOT EXISTS (SELECT 1 FROM PropertyForSale WHERE PropertyID = @PropertyID) ;"


cn = New System.Data.OleDb.OleDbConnection(conStr)
cmd = New OleDbCommand(sqlCmd, cn)

cmd.Parameters.AddWithValue("@Title", Title)


cmd.Parameters.AddWithValue("@Area", Area)
cmd.Parameters.AddWithValue("@PropertyName", PropertyName)
cmd.Parameters.AddWithValue("@PropertyID", PropertyID)
cmd.Parameters.AddWithValue("@Model", Model)
cmd.Parameters.AddWithValue("@PropertyType", PropertyType)
cmd.Parameters.AddWithValue("@PropertySize", PropertySize)
cmd.Parameters.AddWithValue("@TotalPrice", TotalPrice)
cmd.Parameters.AddWithValue("@Price", Price)
cmd.Parameters.AddWithValue("@PubDate", PubDate)
cmd.Parameters.AddWithValue("@Contact", Contact)
cmd.Parameters.AddWithValue("@Phone", Phone)
cmd.Parameters.AddWithValue("@Company", Company)
cmd.Parameters.AddWithValue("@Branch", Branch)
cmd.Parameters.AddWithValue("@Detail", Detail)

Try
cn.Open()
cmd.ExecuteNonQuery()



Thanks to everyone who reply and happy holiday again.

[llkhoutx]

Test a recordset of you recordset:

set rs=currentdb.openrecordset("Name",dbopensnapshot)
rs.findfirst where fieldName = "whatever" 'appropriatelt concantenated wth ' or #

If rs.eof and rs.bot then 'recordset is empty.

Alternativlely,

If rs.nomatch then 'no record with requisite filter exists