Add if does not exist.

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Add if does not exist.

Post by adam »

Hi anyone,

I'm trying to insert a record if it does not exist in the table. if exits then do nothing.

Code: Select all

strSQL = "INSERT INTO CustomerList ([CU No], [Customer Name],[Contact No]) SELECT '" & Me.txtCUNo.Value & "', '" & Me.TextBox2.Value & "', '" & Me.TextBox3.Value & "' WHERE NOT EXISTS (Select [CU No] From CustomerList WHERE [CU No] ='" & Me.txtCUNo.Value & "') LIMIT 1;"
However I'm getting syntax error on this.

Any help would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add if does not exist.

Post by HansV »

1) You want to insert fixed values, not records from a table or query.
2) LIMIT is not a valid keyword in Access SQL.

Does this work?

Code: Select all

strSQL = "INSERT INTO CustomerList ([CU No], [Customer Name],[Contact No]) " & _
    "VALUES ('" & Me.txtCUNo.Value & "', '" & Me.TextBox2.Value & "', '" & Me.TextBox3.Value & "') " & _
    "WHERE NOT EXISTS (Select [CU No] From CustomerList WHERE [CU No] ='" & Me.txtCUNo.Value & "')"
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Add if does not exist.

Post by adam »

I'm getting the error “Query input must contain at least one table or query”? the name of the table is CustomerList.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add if does not exist.

Post by HansV »

I have moved this thread from the Excel forum to the Access/SQL forum since it has nothing to do directly with Excel.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add if does not exist.

Post by HansV »

You'll have to check whether a record exists first, and only if not append a record.
How are you connecting to the database?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Add if does not exist.

Post by adam »

Here's the full Excel Vba I'm using to connect to the database.

Code: Select all

Private Sub CommandButton2_Click()
    Dim cnn         As ADODB.Connection
    Dim strSQL      As String
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\NEW\Database.accdb;"
    
strSQL = "INSERT INTO CustomerList ([CU No], [Customer Name],[Contact No]) " & _
    "VALUES ('" & Me.txtCUNo.Value & "', '" & Me.TextBox2.Value & "', '" & Me.TextBox3.Value & "') " & _
    "WHERE NOT EXISTS (Select [CU No] From CustomerList WHERE [CU No] ='" & Me.txtCUNo.Value & "')"

   cnn.Execute CommandText:=strSQL, Options:=adCmdText

    cnn.Close
    Set cnn = Nothing
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Add if does not exist.

Post by HansV »

Try this version:

Code: Select all

Private Sub CommandButton2_Click()
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim strSQL      As String
    Dim lgnCount    As Long
        
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\NEW\Database.accdb;"
    
    strSQL = "SELECT Count(*) FROM CustomerList WHERE [CU No] ='" & Me.txtCUNo.Value & "'"
    Set rst = New ADODB.Recordset
    rst.Open Source:=strSQL, ActiveConnection:=cnn, Options:=adCmdText
    lngCount = rst(0)
    rst.Close
    
    If lngCount = 0 Then
        strSQL = "INSERT INTO CustomerList ([CU No], [Customer Name],[Contact No]) " & _
            "VALUES ('" & Me.txtCUNo.Value & "', '" & Me.TextBox2.Value & "', '" & Me.TextBox3.Value & "')"
        cnn.Execute CommandText:=strSQL, Options:=adCmdText
    End If
    
    cnn.Close
    Set cnn = Nothing
End Sub
Best wishes,
Hans