Syntax error insert value if does not exist in table

[JackDanIce]

Hi,

I'm trying to write a query that will add a value to a table in Access if the value does not already exist.

Table is: tbl_Client which only contains fields Client_ID and Client_Name

From online reading, I've written in Access query:

IF NOT EXISTS(SELECT * FROM tbl_Client WHERE Client_Name = 'test')
  INSERT Values('test') INTO tbl_Client.Client_Name

However, this errors with: "Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE"

Can someone help correct syntax please?

TIA,
Jack

 

[Special-K99]

I dont know SQL that well but heres a very similar problem

SQL Server Insert if not exists

I want to insert data into my table, but insert only data that doesn't already exist in my database. Here is my code: ALTER PROCEDURE [dbo].[EmailsRecebidosInsert] (@_DE nvarchar(50), @_ASSU...

stackoverflow.com

 

[Joe4]

This can be done in a series of two queries (or in a single nested query):
1st query: Do an unmatched query (to find records that exist in one object that don't exist in the other)
2nd query: Do an append query to add the records from the first unmatched query to your final object

 

[JackDanIce]

Hi Joe, yes I split into two queries, it was more to see if I was on right lines with SQL (never touched it before!) and if it could be optimised into a single (nested) query.

As is, the main macro:

Sub Create_New_Client()
    
    Dim NCN As String: NCN = wMain.Range("New_Client_Name").Value
    
    If Check_Database Then
        Add_New_Name NCN
        Make_New_Forecast_File Make_New_Folder(NCN), NCN, wMain.Range("New_Forecasting_Year").Value
    Else
        NCN = "Client " & NCN & " already exists!" & vbCrLf & vbCrLf & "Please contact Admin"
        MsgBox NCN, vbExclamation + vbOKOnly, "Client Name Already Exists"
    End If
    
End Sub

With:

Private Function Check_Database() As Boolean
    
    With CreateObject("ADODB.Connection")
        .Open Database_Params
        Check_Database = .Execute(Test_Name(wMain.Range("New_Client_Name").Value)).EOF
        .Close
    End With
    
End Function

And:

Private Sub Add_New_Name(ByRef new_name As String)

    With CreateObject("ADODB.Connection")
        .Open Database_Params
        .Execute Replace("INSERT INTO tbl_Client(Client_Name) VALUES('@');", "@", new_name)
        .Close
    End With

End Sub

Thanks for replies and suggestions, I have this working as I need now

Cheers,
Jack

 

[Joe4]

You are welcome.

 

[xenou]

Your sql doesn't look like Access SQL by the way. You might have been looking at an example for SQL server.

 

[JackDanIce]

Hi xenou, I was looking at several sites and mixing search terms of SQL and Access SQL whilst trying to get something to work, not surprised by your comment!

Missed out query:

Replace("INSERT INTO tbl_Client(Client_Name) VALUES('@');", "@", new_name)

Similar to Test_Name above, except to insert, so only using 2 queries in this.

 

[xenou]

Is it actually working now?

 

[JackDanIce]

Hey xenou sorry for late reply and yes working as per #4

 

[xenou]

okay no problem.