Too few parameters

[MarkS]

Hi,

I use this query in Access
Select Max([Version]) As VerionNo From [Gen - ES Sites] Where [Scheme] = "REC"
And it works fine

In Excel VBA I use this code
'Provider = Microsoft Access
ACCESSConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=S:\STAR\risk\ECO\ECO.mdb;"

' Create Connection Object and open it on files.MDB
Set connMelbourne = New ADODB.Connection
connMelbourne.ConnectionTimeout = 60
connMelbourne.ConnectionString = ACCESSConnect
connMelbourne.Open
connMelbourne.CommandTimeout = 620

' Get the latest version
sSql = "Select Max(Version) As VerionNo " & _
"From [Gen - ES Sites] " & _
"Where Scheme = " & Chr(34) & "REC" & Chr(34)
' Run Query and load record set
rsADO.Open sSql, connMelbourne, adOpenStatic, adLockReadOnly

and at the last line I get this error

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

I have tried putting square brackers around both column names, unfortunatly
that didn't work

Has anyone got any sugestion

Thanks MarkS

 

[paulgu]

Mark,
Yes, you need to use single quotes in VBA where double quotes are used in Access. So your where clause should be:
"Where Scheme = '" & "REC" & "'"
That's a bit hard to read but copy and paste it into your VBA editor and it should work. VBA can't cope with double quotes in SQL because it already uses them as string delimiters so it uses single ones instead.
HTH
Paul

 

[FSt1]

hi
i've gotton this error before BUT..... when using MS query and the code was
prompting for parameter input FROM the access side. in this case, the prompt
for parameters must be on the excel side and the access query must not have
parameters.
i am guessing and cannot test but try removeing one of the parameters.
the access query has already selected your data so i would try the excel
side first.

my thoughts
regards
FSt1