Excel passing parameters to Access for queries

[RichardMGreen]

Hi all

I've got a piece of code in Excel which links to an Access database and runs queries in it.
Some of the queries need parameters which are being passed to it using this piece of code:-

If param1 <> "" Then cmd.Parameters(0) = param1

The query I'm running requires 1 parameter which is contained in param1 (and I've stepped through the code to make sure it's there).

When I try to pass the parameter, I get the following error message and the code stops:-
Item cannot be found in the collection corresponding to the requested name or ordinal

If I miss out the the line of code above (leaving the parameter empty) and try to execute the query, I get the following error message:-
Too few parameters. Expected 1

It looks like I need to pass the parameter, but when I do it isn't accepted.

Anyone any ideas?

 

[RoryA]

I do have a recollection of having to specify adcmdtable as the type when opening an Access parameter query before. Might be worth a try.

 

[RichardMGreen]

So would that be cmd.type=adcmdtable?

::edit::

Ignore that, I've just found it.

I'll give it a go and see what happens.

 

[RichardMGreen]

OK, I've altered the commandtype to adcmdtable and I'm now getting a "syntax error in from clause" when the code hits cmd.execute.

 

[xenou]

Are you absolutely sure there's a parameter in that query? The fact that your query works without a parameter is a little odd, and I get the same error when I try to add a parameter that isn't there. Also switching from a query with a parameter to one without a parameter (but not doing anything with the cmd object's parameter collection) is something I'm not sure about and may/may not be a problem.

I don't like this test:
if param1 <> "" ...
A cell can be = "" but that wouldn't be what you'd consider a member ID.

--------------------------------------------------------
For those interested in trying it out, my test code. I was commenting things out and switching the command text around to have a play with this.

assume a database one table in it:
Table1 (ID, F1 Text, F2 Number, F3 Date)

[COLOR="Navy"]Option Explicit[/COLOR]

[COLOR="Navy"]Private[/COLOR] rs [COLOR="Navy"]As[/COLOR] ADODB.Recordset
[COLOR="Navy"]Private[/COLOR] cmd [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]New[/COLOR] ADODB.Command
[COLOR="Navy"]Private[/COLOR] cn [COLOR="Navy"]As[/COLOR] ADODB.Connection
    
[COLOR="Navy"]Sub[/COLOR] Foo()

[COLOR="Navy"]Dim[/COLOR] param1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]

    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler
    
    [COLOR="SeaGreen"]'Set cmd = Nothing[/COLOR]
    [COLOR="Navy"]With[/COLOR] cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        [COLOR="SeaGreen"]'.CommandText = "SELECT * FROM Table1 Where F1 = [Equal To]"[/COLOR]
        .CommandText = "SELECT * FROM Table1 Where F1 = 'A'"
        .Parameters(0) = Sheet1.Range("A1").Value
        [COLOR="Navy"]Set[/COLOR] rs = .Execute
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs.EOF [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] rs.Fields(0).Value
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

My_Exit:
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit:

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] DBOpen()
[COLOR="Navy"]Dim[/COLOR] sCn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="Navy"]Call[/COLOR] DBClose
    [COLOR="Navy"]Set[/COLOR] cn = [COLOR="Navy"]New[/COLOR] ADODB.Connection
    [COLOR="Navy"]Set[/COLOR] cmd = [COLOR="Navy"]New[/COLOR] ADODB.Command
    [COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]New[/COLOR] ADODB.Recordset
    
    sCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\<user>\Desktop\db2.mdb;Persist Security Info=False"
    [COLOR="Navy"]Call[/COLOR] cn.Open(sCn)

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] DBClose()

    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] cn [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        cn.Close
        [COLOR="Navy"]Set[/COLOR] cn = [COLOR="Navy"]Nothing[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
        rs.Close
        [COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Set[/COLOR] cmd = [COLOR="Navy"]Nothing[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[xenou]

Okay,
this seems to help (and it apparently isn't a concern changing the query text to one with or without params as I was wondering in my last post):

    [COLOR="Navy"]With[/COLOR] cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM Table1 Where F1 = [Equal To]"
        [COLOR="SeaGreen"]'.CommandText = "SELECT * FROM Table1 Where F1 = 'A'"[/COLOR]
        [COLOR="Red"]If[/COLOR] [COLOR="Red"]cmd.Parameters.Count > 0[/COLOR] [COLOR="Red"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Len(Range("A1").Value) <> 0 [COLOR="Navy"]Then[/COLOR]
                .Parameters(0) = Sheet1.Range("A1").Value
            [COLOR="Navy"]Else[/COLOR]
                MsgBox "Error: A required Parameter is missing"
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Red"]End[/COLOR] [COLOR="Red"]If[/COLOR]
        [COLOR="Navy"]Set[/COLOR] rs = .Execute
        [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] rs.EOF [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] rs.Fields(0).Value

Note: my error message isn't especially useful here ... it will tell you about the problem and then run right into it anyway ... but at least it gives you an idea what's up. It would be interesting to get the parameter count on your "problem query" too...

 

[RichardMGreen]

The parameter count is 0 (zero) on the problem query when doing the test, but it complains if you don't try and pass it a parameter.
If you try to explicitly pass it a parameter it complains as well.

I've wrapped an if statement around the parameter passing for just that one query and everything seems to work fine.1

 

[xenou]

Okay. Well, the obvious problem is that there really is no parameter in the query. This is why the parameter count is zero and why you can't pass it a parameter. The field you *think* is a parameter might be an actual field in the data source ... (?) That also explains why the "parameterized" query works without a parameter.

It might be just my way of thinking about things, but I don't really like the idea of passing a parameter to a query if the parameter exists. To me, its the other way around - if the the query requires a parameter then you should give it one. I think testing for the query parameter count rather than whether the parameter equals/does not equal "" is better (or both - test if the query takes a parameter and and if you have a value for the parameter - then go ahead, all is well...).

 

[RichardMGreen]

Sorry to resurrect this thread, but I'm having the same problem on a different database.
If I run the query in the database it wants a parameter, if run if from Excel via ADO the parameter count is zero. If I don't pass it a parameter it says it needs one further down.
This is the relevant section of code:-

        cmd.Parameters.Refresh
        If cmd.Parameters.Count > 0 Then
            cmd.Parameters(0) = querydata.Range("F5")
        End If
'----- Pick up information on where data is to go -----
        Set wsDst = Worksheets(rngData.Offset(, 1).Value)
        Set rngDst = wsDst.Range(rngData.Offset(, 2).Value)
'----- Retrieve data from database and insert into correct cells -----
        Set rs = cmd.Execute

The parameter count is zero (first red bit) but the execute (second red bit) says it's missing a parameter.

Anyone any ideas?

 

[RoryA]

Not without the SQL.

 

[RichardMGreen]

Erm .... yeah
Here's the SQL from the query that's causing me problems:-

SELECT DISTINCT qry_Nottingham_C4C_IDs.C4C_ID, qry_Nottingham_C4C_IDs.PCT, Member_Case_Lookup.Case_State, Member_Data.IPG5
FROM (qry_Nottingham_C4C_IDs INNER JOIN Member_Case_Lookup ON qry_Nottingham_C4C_IDs.C4C_ID = Member_Case_Lookup.MEMBER_C4C_ID) INNER JOIN Member_Data ON qry_Nottingham_C4C_IDs.C4C_ID = Member_Data.[C4C-ID]
WHERE (((qry_Nottingham_C4C_IDs.C4C_ID) Is Not Null) AND ((Member_Case_Lookup.Case_State)="open") AND ((Member_Data.IPG5)=[BlahBlah]))
ORDER BY qry_Nottingham_C4C_IDs.C4C_ID;

I used BlahBlah to make sure I wasn't confusing an input header with a field name.