Full 97 - 03 Runtime: ODBC Call Failed

[tkelley]

I have inherited a behemoth of an application developed in A97. I have
finally completed a successful compile and conversion to A2003. The users'
front-end is now being deployed in A2003 Runtime.

There is one place where the code is failing. However, it does not fail for
every user. All users can successfully run in A97. Most users can
successfully run in A2003RT. But there are a handful of users who get the
'ODBC Call Failed' error in A2003RT. It is happening in the code below.
I've marked the failing line with **************.
--------------------------
Public Sub ExecuteSP(strSql As String, strTarget As String)
Dim qdTmp As QueryDef

Set qdTmp = currentdb.CreateQueryDef("")
qdTmp.Connect = "ODBC;DSN=" & strTarget
qdTmp.ReturnsRecords = False
qdTmp.SQL = strSql
qdTmp.Execute dbSQLPassThrough **************
qdTmp.Close
Set qdTmp = Nothing

End Sub
--------------------------
I did not write this code. But when I re-wrote this using ADO, it worked
fine. However, I'm hoping I can find an explanation since it works for the
majority of users. To rewrite every call to ADO would be a daunting task,
and open other margins of error.

Thanks!

[Alex Dybenko]

Hi,
code looks ok, I think the problem can be in DSN settings or security
try to get full error info using errors collection:

If Errors.Count > 1 Then
For Each errX In Errors
strMsg = strMsg & "Error #: " & errX.Number & vbCrLf &
errX.Description & vbCrLf
Next
Else
strMsg = strMsg & "Error #: " & Err.Number & vbCrLf &
Err.Description
End If
MsgBox strMsg
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by tkelley
I have inherited a behemoth of an application developed in A97. I have
finally completed a successful compile and conversion to A2003. The users'
front-end is now being deployed in A2003 Runtime.
There is one place where the code is failing. However, it does not fail for
every user. All users can successfully run in A97. Most users can
successfully run in A2003RT. But there are a handful of users who get the
'ODBC Call Failed' error in A2003RT. It is happening in the code below.
I've marked the failing line with **************.
--------------------------
Public Sub ExecuteSP(strSql As String, strTarget As String)
Dim qdTmp As QueryDef
Set qdTmp = currentdb.CreateQueryDef("")
qdTmp.Connect = "ODBC;DSN=" & strTarget
qdTmp.ReturnsRecords = False
qdTmp.SQL = strSql
qdTmp.Execute dbSQLPassThrough **************
qdTmp.Close
Set qdTmp = Nothing
End Sub
--------------------------
I did not write this code. But when I re-wrote this using ADO, it worked
fine. However, I'm hoping I can find an explanation since it works for the
majority of users. To rewrite every call to ADO would be a daunting task,
and open other margins of error.
Thanks!

[tkelley via AccessMonster.com]

That is helpful, thanks. I'm still stuck though. It says the ODBC driver
can't find a sproc that exists. I can't find an explanation of why this same
exact code runs fine on his 97 version, but not his 2003RT version. He has
all of the proper rights. He also has the same ODBC driver version as
another user that does not encounter this problem.

I'll keep dredging away ... thanks again.

Post by Alex Dybenko
Hi,
code looks ok, I think the problem can be in DSN settings or security
If Errors.Count > 1 Then
For Each errX In Errors
strMsg = strMsg & "Error #: " & errX.Number & vbCrLf &
errX.Description & vbCrLf
Next
Else
strMsg = strMsg & "Error #: " & Err.Number & vbCrLf &
Err.Description
End If
MsgBox strMsg

Post by tkelley
I have inherited a behemoth of an application developed in A97. I have
finally completed a successful compile and conversion to A2003. The

[quoted text clipped - 28 lines]

Post by tkelley
Thanks!

[tkelley via AccessMonster.com]

Okay. I don't have the why, but I have the what. The code uses a system DSN
for the connection. The user's DSN works perfectly when testing connection.
It also works in this exact same code in the A97 version, but not in 2003RT.

However, when I change the connection string from DSN-based to an explicit
DSN-less connection string, this same code now works for the user in 2003RT.

While I now have a solution, I'm still puzzled. The user has the same
version of MDAC that I do, and that others have who aren't having this issue.
So if anyone can enlighten me, that would be wonderful.

Post by tkelley via AccessMonster.com
That is helpful, thanks. I'm still stuck though. It says the ODBC driver
can't find a sproc that exists. I can't find an explanation of why this same
exact code runs fine on his 97 version, but not his 2003RT version. He has
all of the proper rights. He also has the same ODBC driver version as
another user that does not encounter this problem.
I'll keep dredging away ... thanks again.

Post by Alex Dybenko
Hi,
code looks ok, I think the problem can be in DSN settings or security

[quoted text clipped - 16 lines]

Post by Alex Dybenko

Post by tkelley
Thanks!

[Alex Dybenko]

Hi,
if you are sure that security in database gives enough rights to this user -
then something wrong with DSN string, but if DSN-less works - then it is
even better
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by tkelley via AccessMonster.com
Okay. I don't have the why, but I have the what. The code uses a system DSN
for the connection. The user's DSN works perfectly when testing connection.
It also works in this exact same code in the A97 version, but not in 2003RT.
However, when I change the connection string from DSN-based to an explicit
DSN-less connection string, this same code now works for the user in 2003RT.
While I now have a solution, I'm still puzzled. The user has the same
version of MDAC that I do, and that others have who aren't having this issue.
So if anyone can enlighten me, that would be wonderful.

Post by tkelley via AccessMonster.com
That is helpful, thanks. I'm still stuck though. It says the ODBC driver
can't find a sproc that exists. I can't find an explanation of why this same
exact code runs fine on his 97 version, but not his 2003RT version. He has
all of the proper rights. He also has the same ODBC driver version as
another user that does not encounter this problem.
I'll keep dredging away ... thanks again.

Post by Alex Dybenko
Hi,
code looks ok, I think the problem can be in DSN settings or security

[quoted text clipped - 16 lines]

Post by Alex Dybenko

Post by tkelley
Thanks!

--
Message posted via http://www.accessmonster.com