Results 1 to 8 of 8

Thread: Parameter Issues

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2020
    Posts
    1

    Parameter Issues

    I have an Access Database that is reading data from a SharePoint list,
    then outputting the data into an excel spreadsheet There are many queries involved, about 40 total.

    Here is the format of each query:

    Set qdf20 = db.QueryDefs("<Query Name Here>")
    qdf20!Param1 = Param
    Set rst20 = qdf20.OpenRecordset()
    qdf20.Close
    rst20.Close
    Set qdf20 = Nothing
    Set rst20 = Nothing

    This is in a 'For' loop, and the 'Param' changes with each iteration of the loop.
    The problem: Some queries work, and some do not. The only thing that is changed
    is the query name, the record set name and the query def name. The errors are
    totally random. The parameters are in an array, and the for the queries that work,
    all the parameters are successfully transferred.

    Solutions I have tried are using the Parameters Collection Object, and using the SQL commands directly in VBA, but that didn't help.
    The error I receive is: "Too few parameters, expected 1". I have read the other posts concerning this same error, but they do not solve the problem.

    I'm at my wits-end trying to find a solution. Can anyone offer any insight?
    It would be very helpful and much appreciated.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Parameter Issues

    Welcome to VBForums

    The cause of the issue is actually likely to be something other than parameters.

    In many cases the issue is caused by a "bad" name for a field/table/query/etc, perhaps because of a space in the name, or maybe because the name is a reserved word. If you can change the names (in the database and your code), it is likely to work correctly.

    For more information (including lists of Reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

    If for some reason you cannot change the name (and it sounds like you might not be able to in this case), then try putting the name inside square brackets, eg: [Select] or [Name with spaces in]

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Parameter Issues

    What about a race-condition?
    He bulk-updates/inserts in first call, in his second call he updates/inserts something else which needs references from the first call (PrimaryKey, whatever), but the backend is still processing the first call.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Parameter Issues

    The errors are totally random.
    this rules a bad name out. race condition yes possible but as long as its only him working on the access db i doubt that inserts/updates/etc run in parallel.
    it would be good to know the error code. could also be a timeout. and i personaly know sharepoint from behaving like a nightmare in some areas, so i could also imagine a crumbled response from sharepoint server.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Parameter Issues

    Quote Originally Posted by digitalShaman View Post
    this rules a bad name out. race condition yes possible but as long as its only him working on the access db i doubt that inserts/updates/etc run in parallel.
    it would be good to know the error code. could also be a timeout. and i personaly know sharepoint from behaving like a nightmare in some areas, so i could also imagine a crumbled response from sharepoint server.
    To be fair, until we know what the errors are, we can't rule anything out. We also can't rule out the nature of the queries... like you mentioned. SharePoint is horrible to work at times and only tolerable the rest of the time. Certainly not a picnic.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Parameter Issues

    Quote Originally Posted by Campbell MH View Post
    I have an Access Database that is reading data from a SharePoint list,
    then outputting the data into an excel spreadsheet There are many queries involved, about 40 total.

    Here is the format of each query:

    Set qdf20 = db.QueryDefs("<Query Name Here>")
    qdf20!Param1 = Param
    Set rst20 = qdf20.OpenRecordset()
    qdf20.Close
    rst20.Close
    Set qdf20 = Nothing
    Set rst20 = Nothing
    I havn't done this for a long time, but the way you pass the parameter looks wrong

    should look something like this
    Code:
    Dim qd As QueryDef
    Set qd = db.QueryDefs("QueryName")
    qd.Parameters("pID").Value = Text1.Text
    'more parameters
    Set rs = qd.OpenRecordset
    'etc...
    HTH
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Parameter Issues

    It's using a different style... it's the same as recordset!fieldname... which works well enough, but can have some limitations.

    But... that does make me think of something. Are all the parameters the same type? OR are some one trype, and others another? And are the ones that fail, are they of a particular type? OR of a type DIFFERENT from the previous query?

    Again, this is why the error message(s) are important.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Parameter Issues

    Quote Originally Posted by techgnome View Post

    But... that does make me think of something. Are all the parameters the same type? OR are some one trype, and others another? And are the ones that fail, are they of a particular type? OR of a type DIFFERENT from the previous query?

    Again, this is why the error message(s) are important.

    -tg
    I think that's the issue
    if you create the ParmeterQuery with Code you have to pass the Type
    Code:
     
            Set qdf = dbs.CreateQueryDef("myQuery")
            strSQL = "PARAMETERS Param1 TEXT, Param2 INT; "
            strSQL = strSQL & "SELECT * FROM [Table1] "
            strSQL = strSQL & "WHERE [Field1] = [Param1] AND [Field2] = [Param2];"
            qdf.SQL = strSQL
    this will create the Parameters for TEXT and a NUMBER
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Click Here to Expand Forum to Full Width