adam
SilverLounger
Posts: 2347 Joined: 23 Feb 2010, 12:07
Post
by adam » 25 Oct 2020, 14:38
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
HansV
Administrator
Posts: 78595 Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands
Post
by HansV » 25 Oct 2020, 15:02
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
adam
SilverLounger
Posts: 2347 Joined: 23 Feb 2010, 12:07
Post
by adam » 26 Oct 2020, 04:26
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
HansV
Administrator
Posts: 78595 Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands
Post
by HansV » 26 Oct 2020, 08:40
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
HansV
Administrator
Posts: 78595 Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands
Post
by HansV » 26 Oct 2020, 08:41
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
adam
SilverLounger
Posts: 2347 Joined: 23 Feb 2010, 12:07
Post
by adam » 26 Oct 2020, 08:52
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
HansV
Administrator
Posts: 78595 Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands
Post
by HansV » 26 Oct 2020, 09:25
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