delete a record in Access based on a reference in Excel Field

[gmazza76]

Good afternoon

I have written some VB code to open a database look at a specific table and if the reference is in my spreadsheet reference "UniqRef" I want it to delete it from the table so I can upload the amended version.
I used some code I have used previously that finds the row with the values and deletes it so I can re update it. Instead of deleting the information i get a "Run-time error '-2147217904 (80040e10)': No value given for one or more required parameters" which I havent seen before.
I have checked the database and can see the reference "AY100531MA2703AG" in the table as well as seeing it identified in the code stepping through as being the same

Dim oRs As Object
Dim sConn, strSQL, UniqRef As String

StrDBPath = "\\rbsres01\shareddata\ctcbir\Everyone\Business Unit Admin – AD1000\MI Folder\Businessline Quality SharePoint\2023-03-20CallQualityDatabaseHR.accdb"
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"
                             
Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

oConn.Open sConn

strSQL = "DELETE * FROM ObservationTable WHERE [UniqRef] IN (" & UniqRef & ");"

oConn.Execute strSQL

oConn.Close
 
Set oConn = Nothing
Set oRs = Nothing

 

[shknbk2]

This worked for my test database: single table named "ObservationTable" with 1 column "UniqRef" and 4 rows of data. Two rows had "AY100531MA2703AG" and 2 didn't. After running, the desired rows were eliminated from the table.

I got the same error you did with brackets and no single quotes in the parentheses. Removing brackets only still produced same error. Adding single quotes did the trick. So, after playing around, this was the successful line:

strSQL = "DELETE * FROM ObservationTable WHERE UniqRef IN ('" & UniqRef & "');"

 

[gmazza76]

thanks for that @shknbk2 it has worked