ODBC--call failed.

[tina]

Hello,
Can you hale me to define my mistake please?
I am trying to run a SQL Pass – Through Query from Access 2000 and
inside the record set I am trying to loop for a LoginID. I think
that this function should work.
I get an error at this point Set StoredProcRecordSet =
DB.OpenRecordset(StoredProcQryName, DB_OPEN_SNAPSHOT)

The error is this ODBC--call failed.

Please let me know how can I fix this.
Thank you very much.

Tina

Function GetFullReportCCListServer(MyUnit_Name As String)

Dim StoredProcName As String
Dim StoredProcCall As String
Dim StoredProcQryName As String
Dim StoredProcQueryDef As QueryDef
Dim StoredProcRecordSet As Recordset

Dim DB As Database

On Error GoTo HandleErr
Set DB = DBEngine.Workspaces(0).Databases(0)

StoredProcQryName = "qryGetFullReportCCList"
StoredProcCall = "execute sp_GetFullReportCCList '" & MyUnit_Name
& "'"

Set StoredProcQueryDef = DB.QueryDefs(StoredProcQryName)
StoredProcQueryDef.Sql = StoredProcCall

Set StoredProcRecordSet = DB.OpenRecordset(StoredProcQryName,
DB_OPEN_SNAPSHOT)

StoredProcRecordSet.MoveFirst

Do Until StoredProcRecordSet.EOF

GetFullReportCCListServer = GetFullReportCCListServer &
StoredProcRecordSet.Fields("LoginID") & ";"
StoredProcRecordSet.MoveNext

Loop

GetFullReportCCListServer = Left$(GetFullReportCCListServer,
Len(GetFullReportCCListServer) - 1)


ExitHere:

Set StoredProcRecordSet = Nothing

Exit Function
End Function

 

[Perry]

Replace

Dim StoredProcQueryDef As QueryDef
Dim StoredProcRecordSet As Recordset
Dim DB As Database

by
Dim StoredProcQueryDef As DAO.QueryDef
Dim StoredProcRecordSet As DAO.Recordset
Dim DB As DAO.Database

Will usually cure the case.
Kindly repost if it doesn't.

Krgrds,
Perry

 

[Steve Jorgensen]

If that were the problem, I don't think the error message would say "ODBC call
failed".

 

[Steve Jorgensen]

I can't tell you what the cause of error is, but I can tell you how to find
out more about it.

When you use DAO, and an error is returned from the server or from the ODBC
library, a number of errors are generated, but VB gets only the last one which
is the least informative, and always just says "ODBC call failed". The whole
list of errors is stored in the DAO Errors collection, though, so you just
need to look at Errors(0).Description (I think that's right, it might be 1,
not zero) to see the original cause of the error.