runtime error, does this query look right?

[AngeloUCF]

I keep getting an error at the rsResults.open line of this code:Run-
time error '-2147467259 (80004005)' ODBC--call failed. From what I've
researched it's fairly common, but no single solution. I get different
messages changing the sql string around, so I was hoping it might be
within the statement and I'm not seeing it. I've already got 3 other
queries being written to other worksheets in the book, just hanging up
on this one. Anyone see something funny? I've put a msgbox(sql) line
in there before executing and it looks fine to me.
P.S. I'm not a programmer, so I'm sure it's not the neatest and most
efficient code.

Public Function sqlNoPayoff(BC1 As Integer, BC2 As Integer)
Dim sql As String, i As Integer
Dim ad As ADODB.Connection
Dim rsResults As ADODB.Recordset
i = 0
sql = ""
'------------------------------
'WRITE RECORDSET TO WORKBOOK
'------------------------------
' Initiate connection to database and start a new recordset
Set ad = CurrentProject.Connection
Set rsResults = New ADODB.Recordset
' SQL for ERM Balance Control account balances
sql = "SELECT BC_Accounts.OW_ACCOUNT,"
sql = sql & " PSADM_PS_CI_FT.BAL_CTL_GRP_ID,"
sql = sql & " PSADM_PS_CI_FT_GL.JOURNAL_ID,"
sql = sql & " PSADM_PS_CI_FT.CUR_AMT,"
sql = sql & " PSADM_PS_CI_FT.TOT_AMT,"
sql = sql & " PSADM_PS_CI_FT_GL.MONETARY_AMOUNT"
sql = sql & " FROM BC_Accounts,"
sql = sql & " PSADM_PS_CI_FT,"
sql = sql & " PSADM_PS_CI_FT_GL"
sql = sql & " WHERE PSADM_PS_CI_FT.FT_ID =
PSADM_PS_CI_FT_GL.FT_ID"
sql = sql & " AND BC_Accounts.DEPTID =
PSADM_PS_CI_FT_GL.DEPTID"
sql = sql & " AND BC_Accounts.OPERATING_UNIT =
PSADM_PS_CI_FT_GL.OPERATING_UNIT"
sql = sql & " AND BC_Accounts.ACCOUNT =
PSADM_PS_CI_FT_GL.ACCOUNT"
sql = sql & " AND PSADM_PS_CI_FT.BAL_CTL_GRP_ID Between " & BC1
& " And " & BC2
sql = sql & " AND PSADM_PS_CI_FT.TOT_AMT = 0"
sql = sql & " AND PSADM_PS_CI_FT.FREEZE_SW = 'Y'"
sql = sql & " AND PSADM_PS_CI_FT.REDUNDANT_SW = 'N';"
rsResults.Open sql, ad, , , adAsyncExecute
Do While rsResults.State = adStateExecuting
DoEvents
Loop
' Write column headers
'ActiveCell.Offset(i, 0).Value = rsResults("OW_ACCOUNT").Name
ActiveCell.Offset(i, 1).Value = rsResults("BAL_CTL_GRP_ID").Name
ActiveCell.Offset(i, 2).Value = rsResults("JOURNAL_ID").Name
ActiveCell.Offset(i, 3).Value = rsResults("CUR_AMT").Name
ActiveCell.Offset(i, 4).Value = rsResults("TOT_AMT").Name
ActiveCell.Offset(i, 5).Value = rsResults("MONETARY_AMOUNT").Name
i = i + 1
Do While Not rsResults.EOF
' Write query data
ActiveCell.Offset(i, 0).Value = rsResults("OW_ACCOUNT")
ActiveCell.Offset(i, 1).Value = rsResults("BAL_CTL_GRP_ID")
ActiveCell.Offset(i, 2).Value = rsResults("JOURNAL_ID")
ActiveCell.Offset(i, 2).Value = rsResults("CUR_AMT")
ActiveCell.Offset(i, 2).Value = rsResults("TOT_AMT")
ActiveCell.Offset(i, 2).Value = rsResults("MONETARY_AMOUNT")
rsResults.MoveNext
i = i + 1
Loop
rsResults.Close
Set rsResults = Nothing
Set ad = Nothing
End Function

 

[Guest]

Angelo

You are correct that "ODBC--call failed" is fairly common and that there is
no single solution. The reason why is that the error message is not
descriptive as to the actual problem. The message only means that something
is wrong.

Given the size of your SQL statement, it might be difficult for someone to
take the time to debug it. I would suggest that you reduce the size of the
statement to its simplest form and try again. If it fails at that point it
will be much easier to debug. If it works you can add sections back to it
until the failure returns. Once that happens you will at least have the
problem isolated and should be able to focus in that area.

Jack Cannon

 

[AngeloUCF]

Thanks Jack, I did just that and narrowed the problem to the sql line
of:

sql = sql & " AND PSADM_PS_CI_FT.BAL_CTL_GRP_ID Between " & BC1 & "
And " & BC2

I have no idea what the problem could be. I originally have the
function passing an integer for BC1 and BC2, but changed them to
strings and the problem persists. The field in the table is a "number"
and bal_ctl_grp_id > 5 works just fine in the previous query (passed
as an integer). I also tried hardcoding the numbers and got the same
error, so I'm stumped.

 

[Guest]

Angelo,

I see nothing wrong with the specific line of code that appears to be
causing the error. It could be that this particular line of code meets some
form of a threshold point within the statement that was being composed.

I have no experience with very large SQL statements. But I would at least
question whether a maximum limit exists to the size of the statement. Try
removing some of the portions prior to the line of concern so that you will
be testing the line of code as much as possible by itself. That should allow
you to concentrate on the specific line or whether you have an overall
problem with the actual statement.

Jack Cannon

 

[J_Goddard via AccessMonster.com]

Hi -

It's a longshot, but try putting PSADM_PS_CI_FT.BAL_CTL_GRP_ID Between " &
BC1 & "
And " & BC2 in brackets:

sql = sql & " AND ( PSADM_PS_CI_FT.BAL_CTL_GRP_ID Between " & BC1 & " And
" & BC2 & ")" & ....

I wonder if the parser is getting confused with too many AND's in a row?

Just a thought..

John