Consulting

Results 1 to 2 of 2

Thread: Parameter Issues

  1. #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
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It is not clear from your code if the parameter always applies to the same field in the recordset you create.
    Are you changing or replacing the Query Def each time you use it?
    As you are hard coding the parameters into an array have tried running each query with the correct parameter to see if it works?
    You can't use SQL directly, it needs translating, I have a program that will do it for you if you want to try it.

Tags for this Thread

Posting Permissions

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