Hello:

I use the following VBA/ADO code to execute a Oracle stored procedure. This worked - until someone moved the database to another location. Then this code causes an error.

Code used:
Dim objCmd As New ADODB.Command
Dim objParam1 As ADODB.Parameter
Dim objParam2 As ADODB.Parameter
Dim objParam3 As ADODB.Parameter
Dim objParam4 As ADODB.Parameter
Dim objParam5 As ADODB.Parameter
Dim objParam6 As ADODB.Parameter
Dim objParam7 As ADODB.Parameter
Dim RsChange_Status As New ADODB.Recordset
Dim sSQL As String
Dim RsQC_ReadyResponse_RT As New ADODB.Recordset
Dim Ans As Integer
Dim StatusCN As New ADODB.Connection


On Error GoTo Status_Change_Err



glbCNN = ("Driver={Microsoft ODBC for Oracle};Server=FRED_APP;Uid=;Pwd=;")
If StatusCN.state <> adStateClosed Then
StatusCN.Close ' close the currently used connection
StatusCN.Open glbCNN ' use this connection rather than current connection
Else
StatusCN.Open glbCNN
End If

' objCmd.CommandText = "fred_rcv.SP_CHANGE_STATUS"
objCmd.CommandText = "fred_rcv.SP_CHANGE_STATUS_USER"
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = StatusCN

Set objParam1 = objCmd.CreateParameter("@County", adVarChar, adParamInput, 30)
Set objParam2 = objCmd.CreateParameter("@Route_Type", adVarChar, adParamInput, 30)
Set objParam3 = objCmd.CreateParameter("@Route_Num", adVarChar, adParamInput, 30)
Set objParam4 = objCmd.CreateParameter("@Status_From", adVarChar, adParamInput, 30)
Set objParam5 = objCmd.CreateParameter("@Status_To", adVarChar, adParamInput, 30)
Set objParam6 = objCmd.CreateParameter("@Req_ID", adInteger, adParamInput)
Set objParam7 = objCmd.CreateParameter("@User_ID", adVarChar, adParamInput, 30)
objCmd.Parameters.Append objParam1
objCmd.Parameters.Append objParam2
objCmd.Parameters.Append objParam3
objCmd.Parameters.Append objParam4
objCmd.Parameters.Append objParam5
objCmd.Parameters.Append objParam6
objCmd.Parameters.Append objParam7
objParam1.Value = StatusCounty
objParam2.Value = StatusRT
objParam3.Value = StatusRTNUM
objParam4.Value = Status_From
objParam5.Value = Status_To
objParam6.Value = lngKey
objParam7.Value = glbUserID

Set RsChange_Status = objCmd.Execute

When above statement executes the error handler returns this:

Error in Status Change procedure – 2147217900 (Microsoft) (ODBC driver for Oracle) Syntax error or access violation.

The bofune who over seas the DB says he can run the SP on this server but my code fails - it worked before the DB was moved.

Thanks,

Frustrated and PO'd at idiot who moved DB