"Delete *" works, but "Delete * where" doesn't

[Reenen]

I am connecting to my Access DB via ADODB connection (from Excel), I can delete all, but I cannot delete a subselection of data.

This works:

sSql = "DELETE ManualSplits.* from ManualSplits"
cn.Execute sSql

This does not work

sSql = "DELETE ManualSplits.* from ManualSplits WHERE ManualSplits.StyleSku LIKE '" & Style & "*'"

My connection code:

    Set cn = CreateObject("ADODB.Connection")
    dbPath = ActiveWorkbook.Path & "\SupplierComms.accdb"
    scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
    cn.Open scn

Can anyone help me with this?


Admittedly I am trying to delete the subsection, because I am not able to successfully run an Update query, so I am deleting and inserting all the info I need. So if you can help me run an update query it would also work.

This is the insert code that currently works:

  sSql = "Insert into ManualSplits ([StyleSku],[DC],[Delnotafterdate],[intostoredate],[Supplier],[Qty]) "
    sSql = sSql & "Select * from [Excel 12.0;HDR=Yes;DATABASE=" & dbwb & "]." & dsh

This is the update code that doesn't work:

sSql = "UPDATE ManualSplits INNER JOIN Select * from [Excel 12.0;HDR=Yes;DATABASE=" & dbwb & "]." & dsh & " as t ON (ManualSplits.Supplier = t.Supplier) AND (ManualSplits.IntoStoreDate = t.IntoStoreDate) AND (ManualSplits.DelNotAfterDate = t.DelNotAfterDate) AND (ManualSplits.DC = t.DC) AND (ManualSplits.StyleSku = t.StyleSku) SET ManualSplits.Qty = [t].[Qty];"

 

[CJ_London]

You are using ADODB rather than a DAO connection.

For ADODB, replace * with %

 

[Reenen]

Thanks! I knew in some languages I have to use %, but I thought I could just copy paste the code from access. I use the thanks button for you!

 

[CJ_London]

No problem. Be aware that with ADODB you can use

*something*
or
something*

but not

*something

and you have more limited options for constructing multiple criteria