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

    Unanswered: ODBC -- call failed

    Running this VB program in MS-Access.

    I get an "ODBC -- call failed" error when I am looking for all ssn beginning with one number (see below).

    When I change the like statement to two numbers "ssn like '21%'", I do not get the error and the program runs successfully.

    Any thoughts?


    strsql = "SELECT ssn, activity_date, annualized_salary "
    strsql = strsql + "FROM dbo_salaryhistory "
    strsql = strsql + "WHERE Activity_Date >= #1/1/2004# AND "
    strsql = strsql + "ssn like '2%' "
    strsql = strsql + "ORDER BY ssn, activity_date "

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

    Option Compare Database
    Option Explicit


    Sub Main1()

    Open "U:\out.txt" For Output As #1

    Dim rs As ADODB.Recordset
    Dim tot As Integer
    Dim cnt As Integer
    Dim pct As Double
    Dim strsql As String
    Dim old_ssn As String
    Dim lo_salary As Double
    Dim hi_salary As Double

    MsgBox ("Begin the Program")

    strsql = "SELECT ssn, activity_date, annualized_salary "
    strsql = strsql + "FROM dbo_salaryhistory "
    strsql = strsql + "WHERE Activity_Date >= #1/1/2004# AND "
    strsql = strsql + "ssn like '2%' "
    strsql = strsql + "ORDER BY ssn, activity_date "


    old_ssn = "999"
    cnt = 0
    tot = 0
    Set rs = New ADODB.Recordset

    rs.ActiveConnection = CurrentProject.Connection
    rs.LockType = adLockReadOnly
    rs.Open strsql
    rs.MoveFirst
    Do Until rs.EOF


    If (old_ssn <> rs!ssn) And (old_ssn <> "999") Then
    If cnt >= 3 Then
    tot = tot + 1
    pct = ((hi_salary - lo_salary) / lo_salary)
    Print #1, tot, cnt, old_ssn, FormatCurrency(lo_salary, 2), FormatCurrency(hi_salary, 2), FormatPercent(pct, 2)
    End If
    cnt = 0
    End If

    cnt = cnt + 1
    old_ssn = rs!ssn
    If cnt = 1 Then
    lo_salary = rs!annualized_salary
    End If
    hi_salary = rs!annualized_salary

    rs.MoveNext
    Loop
    rs.Close


    If cnt >= 3 Then
    tot = tot + 1
    pct = ((hi_salary - lo_salary) / lo_salary)
    Print #1, tot, cnt, old_ssn, FormatCurrency(lo_salary, 2), FormatCurrency(hi_salary, 2), FormatPercent(pct, 2)
    End If

    MsgBox ("End of Program")

    Close #1
    End Sub

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    I see the following contradiction: you are working with OLE DB, but you are getting an ODBC error. Did you attach tables to your MS Access database using ODBC?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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