Data Connection to .mdb access database

[Emmett52]

Apologies not sure if below is correctly formatted

I receive error message error 1004, Too few parameters, Expected 1 (I've spent hrs googling and changing sql connection and the vba code to no avail)

Basically I want to filter in table [Despatch] field [Serial] where contains or is like Cell B2 text (im not sure on difference between Contains and Like either)

Any Ideas where im going wrong, i suspect its to do with incorrect use of * or %, and ` or ""


Any Help much appreciated!

Sub Test1()


Application.DisplayAlerts = False
Application.ScreenUpdating = False


Dim Serial As String


Serial = Sheets("Home").Range("B2").Value
Debug.Print Serial


    With ActiveWorkbook.Connections("Despatch").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array("SELECT * FROM `K:\Inventory Engines`.`Despatched` `Despatched`" & Chr(13) & "" & Chr(10) & "WHERE (Despatched.`Serial No` Like '%' & [" & Serial & "] & '%')" & Chr(13) & "" & Chr(10) & "ORDER BY Despatched.`Eff Date`")
        .CommandType = xlCmdSql
        .Connection = _
        "ODBC;DSN=MS Access Database;DBQ=K:\Inventory Engines.mdb;DefaultDir=K:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With


    ActiveWorkbook.Connections("Despatch").Refresh
    
Application.DisplayAlerts = True
Application.ScreenUpdating = True
    
End Sub

 

[Emmett52]

Is it possible to set-up a "contains" via an excel - access data connection ?

Above is simply what I ended up with in an exercise to try and learn filtering a connection!

 

[Norie]

Why are you enclosing the value from Serial in [ ]?

The [ ] could lead to the value being seen as a field rather than just a value, and that could be why you are being asked for a parameter.

Also, why is % enclosed in single quotes?

Try this.

.CommandText = Array("SELECT * FROM `K:\Inventory Engines`.`Despatched` `Despatched`" & Chr(13) & "" & Chr(10) & "WHERE (Despatched.`Serial No` Like '%" & Serial & "%')" & Chr(13) & "" & Chr(10) & "ORDER BY Despatched.`Eff Date`")

 

[Emmett52]

Perfect, Thanks Norie!

The [] was included as I originally used macro recorder to set-up a new access connection, used contains ? in the filter wizard, then in sql editor, changed from '?' to ?, which prompted me for location of parameter.
I guess I was confusing setting paramater using vba, with setting parameter in the parameters section of connection settings