Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    29

    Unanswered: Error in nested loop

    I get an error - ODBC call failed when the 2nd loop tries to run.
    Running VB on MS-Access.
    Any ideas? Thanks.

    -------------------

    Sub Main1()

    Open "U:\out.txt" For Output As #1
    Dim msg As Integer
    Dim match_flag As Integer
    Dim strsql1 As String
    Dim strsql2 As String
    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset

    MsgBox ("Begin the Program")

    strsql1 = "SELECT a.ssn "
    strsql1 = strsql1 + "FROM dental_eligibility a "
    strsql1 = strsql1 + "WHERE a.ssn like '101%' "

    strsql2 = "SELECT b.ssn "
    strsql2 = strsql2 + "FROM dbo_SHPS_EmployeeCoverage b, dbo_SHPS_DependentCoverage c "
    strsql2 = strsql2 + "WHERE b.ssn = c.ssn and "
    strsql2 = strsql2 + "b.plan_type = 'DE' and "
    strsql2 = strsql2 + "c.plan_type = 'DE' "

    Set rs1 = New ADODB.Recordset
    Set rs2 = New ADODB.Recordset

    rs1.ActiveConnection = CurrentProject.Connection
    rs1.LockType = adLockReadOnly
    rs2.ActiveConnection = CurrentProject.Connection
    rs2.LockType = adLockReadOnly

    With rs1
    rs1.Open strsql1
    rs1.MoveFirst
    Do Until rs1.EOF

    Print #1, "#1", rs1!ssn
    match_flag = 0

    With rs2
    rs2.Open strsql2
    rs2.MoveFirst
    Do Until rs2.EOF

    Print #1, "#2", rs2!ssn

    rs2.MoveNext
    Loop
    rs2.Close
    End With
    Set rs2 = Nothing

    Print #1, rs1!ssn, match_flag

    rs1.MoveNext
    Loop
    rs1.Close
    End With
    Set rs1 = Nothing

    MsgBox ("End of Program")

    Close #1
    End Sub

  2. #2
    Join Date
    May 2004
    Posts
    97
    You Set rs2 before the first rs1 loop, then at the completion the first itteration of the rs2 loop you set rs2 = nothing. Beginning the second itteration of the rs1 loop you do not have an rs2 object.

    Then, I see no real reason for using the with blocks. You are using fully qualified objects everywhere. Guess I'm not that up to date on coding techniques.

  3. #3
    Join Date
    Apr 2004
    Posts
    29
    Thanks, still get the same error, any ideas?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What does the ODBC error say?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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