Data Connection to .mdb access database

Emmett52

New Member
Joined
May 22, 2015
Messages
18
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!

Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Emmett52

New Member
Joined
May 22, 2015
Messages
18
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
.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

New Member
Joined
May 22, 2015
Messages
18
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
 

Forum statistics

Threads
1,143,654
Messages
5,720,109
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top