I have a SQL Server 2000 stored procedure for sending e-mails which I call from an A97 front end. My problem is that it works on some PCs, but fails on others. It fails in Access - not SQL Server, so I am posting in this forum. The code is as follows:
Dim wsp As Workspace
Dim qdf As QueryDef
Dim cnn As Connection
Set wsp = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
Set cnn = wsp.OpenConnection(Name:="", Options:=dbDriverNoPrompt, ReadOnly:=True, Connect:=G_CONNECTION)
Set qdf = cnn.CreateQueryDef("qry", "{ call send_Sys_Email(?,?,?,?,?) }")
With qdf
.Parameters(0).Direction = dbParamInput
.Parameters(1).Direction = dbParamInput
.Parameters(2).Direction = dbParamInput
.Parameters(3).Direction = dbParamInput
.Parameters(4).Direction = dbParamInput
.Parameters(0) = stFrom
.Parameters(1) = stTo
.Parameters(2) = stSubject
.Parameters(3) = stBody
.Parameters(4) = stCC
.Execute
.Close
End With
The error I get is "ODBC--call failed". It occurs at the .Execute line.
Why might this work on some and not on others? The builds should be similar - all are Win 2k - some have A97 runtime, some full A97, some full A2K, some two or more, but there is no pattern between the version(s) of Access installed and the PCs on which the code fails. I know the connection strings are correct, and permissions on SQL server are not the problem. I also have code opening recordsets on all the PCs and sucessfully returning records - none of these connections fail. Are there extra optional components which need to be installed for this sort of connection to work?
Dim wsp As Workspace
Dim qdf As QueryDef
Dim cnn As Connection
Set wsp = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
Set cnn = wsp.OpenConnection(Name:="", Options:=dbDriverNoPrompt, ReadOnly:=True, Connect:=G_CONNECTION)
Set qdf = cnn.CreateQueryDef("qry", "{ call send_Sys_Email(?,?,?,?,?) }")
With qdf
.Parameters(0).Direction = dbParamInput
.Parameters(1).Direction = dbParamInput
.Parameters(2).Direction = dbParamInput
.Parameters(3).Direction = dbParamInput
.Parameters(4).Direction = dbParamInput
.Parameters(0) = stFrom
.Parameters(1) = stTo
.Parameters(2) = stSubject
.Parameters(3) = stBody
.Parameters(4) = stCC
.Execute
.Close
End With
The error I get is "ODBC--call failed". It occurs at the .Execute line.
Why might this work on some and not on others? The builds should be similar - all are Win 2k - some have A97 runtime, some full A97, some full A2K, some two or more, but there is no pattern between the version(s) of Access installed and the PCs on which the code fails. I know the connection strings are correct, and permissions on SQL server are not the problem. I also have code opening recordsets on all the PCs and sucessfully returning records - none of these connections fail. Are there extra optional components which need to be installed for this sort of connection to work?
