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

    Unanswered: ODBC error while looping

    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
    Apr 2004
    Posts
    29
    bump - any ideas?

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It looks to me like you are killing rs2 when you still need it.

    Set rs2 = Nothing

    You are permanently killing the variable after the first person in rs1 is calculated. Move this statement out of the loop and put it at the end.

    If this is in Access you will also want to change your '+' to '&' when you are concatenating you SQL string. In Access + may give you unexpected results. It is primarily used for addition.

Posting Permissions

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