Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Question Unanswered: Run-time error -214721790 - problem with ADODB recordsets

    Hi

    I'm trying to pull two different query outputs into recordsets to allow for line-by-line comparison. The code that I'm using is:
    Code:
        Dim rstUnit As ADODB.Recordset
        Dim rstAlt As ADODB.Recordset
        Dim strSQLUnit As String
        Dim strSQLAlt As String
        Dim sglGoal As Single
        
        '1.   Generate the SQL statements
        strSQLUnit = "SELECT [Unit], [Alt], [Ratio], [Calc] " & _
            "FROM qryMovementConversions " & _
            "WHERE [Unit] = '" & strUnit & "';"
       
        strSQLAlt = "SELECT [Unit], [Alt], [Ratio], [Calc] " & _
            "FROM qryMovementConversions " & _
            "WHERE [Unit] = '" & strAlt & "';"
        
        '2.   Open the recordsets
        Set rstUnit = New ADODB.Recordset
        rstUnit.Open strSQLUnit, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        
        Set rstAlt = New ADODB.Recordset
        rstAlt.Open strSQLAlt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    When the code progress hits the line beginning, "rstUnit.Open...", the following error message appears:
    Run-time error -2147217904 (80040e10)
    No value given for one or more required parameters"

    The code itself is part of a function, and the function is called from a form. The query referenced in the SQL statements points to the same form to get its criterion.
    strUnit and strAlt are arguments taken by the function.
    When I paste the SQL statement into a query window, it runs perfectly and produces the output that I would expect to get in the recordset.
    Also, I've used the same code structure to open recordsets in several databases, without problems. I'm using Access 2000 (9.0.6926 SP-3) on Windows 2000 (5.0.2195, service pack 4), if that makes any difference. Any help would be gratefully received!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Don't want this to go unanswered. I suspect you're running into the ADO equivalent of this:

    http://support.microsoft.com/default...b;en-us;209203

    Not sure what the ADO solution is, as I typically use DAO, but maybe that gets you going in the right direction?
    Paul

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might do a

    msgbox strUnit and msgbox strAlt

    before the rstUnit.Open and rstAlt.Open lines. Your ADO code looks like the code I've also always used so I'm guessing the values of the variables might be the issue (which the error seems to indicate) or something with the query: qryMovementConversions.

    I've also used this syntax for the SQL string with a deal of success:

    strSQLUnit = "SELECT [Unit], [Alt], [Ratio], [Calc] " & _
    "FROM qryMovementConversions " & _
    "WHERE [Unit] = " " " & strUnit & " " " " (Note: " spaced out to show the character used - also, no need for ; )

    But it really sounds like a problem with the value of the variables or the query. Otherwise, try putting in actual values versus using strUnit and strAlt in the SQL statement and possibly see if that works. Only other guess is that you look at qryMovementConversions to see if something there is causing the error (maybe redo your query or change it's Recordset Type property to a Snapshot or avoid using the query and make it so the SQL string doesn't use the query but is based off the table.) I once had a problem where using a query for some reason caused the same type of error but when I changed the SQL statement so it was based off a table, it worked. I later redid the query and it worked.

    Only other difference is I've used this sometimes for opening (but I really don't think it's the problem and I also use the adOpenKeyset)...

    rstAlt.Open strSQLAlt, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    Last edited by pkstormy; 07-11-08 at 21:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Smile

    Thanks for the replies!

    I'll try hard-coding the values of the arguments into it to see if that helps. I don't want to have to try coming up with two different versions of qryMovementConversions to code into the function as it's a union query that unites three data sets from one table and two from another. Still, if that's what it takes...

    pkstormy - I used to use the multiple double-quote format, but used to get a in knot when amending the code! Also, being a union query, qryMovementConversions is already snapshot. Still, as I didn't mention it, there's not way that you could have known. I suppose that I could also try writing a couple of statements to select the query's output into two tables and basing the recordsets on them - I'll try on Monday and let you know what works.

    Thanks again!

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Thumbs up Problem solved

    Just to let you know - I added code to dump the two query outputs into holding tables. Then I changed the sources of the recordsets to point to the holding tables rather than SQL strings, and the error stopped happening.

    Thank you!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the update. After reading your last post on dumping the query outputs to holding tables, I recall now that this was a solution I once used (although it was a work-around type solution and I eventually redid it.) I'm glad to hear though that you've solved the problem.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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