Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Resolving Parameters in ADO using Access query as source

    I have an Access query called 'qryFiscalSalesMappedByPriority'. It contains a single parameter field, called Priority. In the Access query, the criteria for this field is [Forms]![frmRefreshData]![cboPriority]. When I try to execute the query using ADO (as follows), I get an error (Too few parameters. Expected 1.):

    Code:
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strQry As String
    
    Set cnn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    rs.Open "qryFiscalSalesMappedByPriority", cnn, adOpenStatic, _
    adLockReadOnly, adCmdStoredProc
    I have tried numerous methods taken from examples on the net of resolving the parameter, but to no avail. I'm at a bit of a dead end with this now, so any help much appreciated.
    Last edited by bcass; 01-24-07 at 06:54.

  2. #2
    Join Date
    Oct 2003
    Posts
    21
    In my experience "(Too few parameters. Expected 1.)" actually means "I don't like your SQL"

    Try something like.....

    Try setting your strQry = "Select * from qryFiscalSalesMappedByPriority"
    and then open your recordset

    rs.open strQry, cnn, adOpenStatic, _

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    That's the way I did it originally, but I get a similar error (can't remember exactly what it was as I'm not at work at the moment).

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'm afraid I can't be give too much help here.

    Is this a pass-thru query? I see you're using adCmdStoredProc in the options argument.

    I think (I haven't done this in a while) you need to populate that "parameter" before executing the query via code. Did you look at
    the parameters collection in the help file?
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    It isn't a pass-through query (just a local Access SELECT query). I think you have to use adCmdStoredProc in ADO when opening an Access query into an rs (I've done this on a non-parametized query and it works).

    I tried this:

    Code:
    strQry = "SELECT * FROM qryFiscalSalesMappedByPriority;"
    rs.Open strQry, cnn, adOpenStatic, adLockReadOnly
    And got this slightly different error: 'No value given for one or more required parameters'.

    I have tried a few ways of specifying the parameters using an ADO command, but without any luck. It's obvious I need to pass the parameter to ADO first, but I just can't find the right code to do it properly.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi All

    I could be wrong, but I don't think you can use a stored query as a 'Table' in an ADO rs sql if it uses form control references for its parameters ??

    No doubt someone will put me right if this is incorrect!


    MTB

  7. #7
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Here's the code I'm trying to use that doesn' work:

    Code:
    Dim cmd As New ADODB.Command
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strQry As String
    
    Set cnn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    With cmd
        Set .ActiveConnection = cnn
            .CommandText = "qryFiscalSalesMappedByPriority"
            .CommandType = adCmdStoredProc
                
            .Parameters.Append .CreateParameter("paramPriority", adVarChar, adParamInput, 6)
    End With
    
    cmd.Parameters("paramPriority") = cboPriority
    rs.Open cmd, , adOpenStatic, adLockReadOnly
    I get an error reporting: 'Parameter [Form]![frmRefreshData]![cboPriority] has no default value.' The code stops at the point where I attempt to Open the rs. While debugging, when I hover the mouse pointer over cboPriority, it shows the value that is selected on the form, as does the cmd.Parameters("paramPriority") value.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It looks like your getting somewhere. That error message makes it look like
    it doesn't have a problem with a parameter, as much as it does the combo-box value.

    Have you tried testing it with a value hard coded in place in cboPriority?
    cmd.Parameters("paramPriority") ="SomeValue"
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I've just solved it - I was using the default of ADO2.1 in my VBA References. After what MikeTheBike said, I thought it might be a feature only supported by later versions of ADO, and it is - the code now works without problem. So, it is possible to use a stored Access query as an ADO rs when using form control references for parameters. Thanks for the help everyone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •