I am trying to call an Oracle 19c stored procedure from Excel VBA. I should preface by saying that I can query data without issue. When the command is executed, Excel crashes. Below is my code followed by the entry in the Event View.
Dim Oracon As ADODB.Connection
Dim cmd As New ADODB.Command
Dim objErr As ADODB.Error
Set Oracon = GetNewConnection
With cmd
.ActiveConnection = Oracon
.CommandText = "SP_UPDATE_CLIENT_OPT_ISSUES"
.CommandType = adCmdStoredProc
.NamedParameters = True
.Parameters.Append .CreateParameter("NAN", adVarChar, adParamInput, 6, "100100")
.Parameters.Append cmd.CreateParameter("OPTIMIZATION_ID", adNumeric, adParamInput, , 1)
.Parameters.Append cmd.CreateParameter("DATE_OPENED", adDBDate, adParamInput, , Now)
.Parameters.Append cmd.CreateParameter("DATE_CLOSED", adDBDate, adParamInput, , Now)
.Parameters.Append cmd.CreateParameter("OPENED_BY", adVarChar, adParamInput, 255, "Todd")
.Parameters.Append cmd.CreateParameter("DESCRIPTION", adVarChar, adParamInput, 4000, "Test Description")
.Parameters.Append cmd.CreateParameter("NOTES", adVarChar, adParamInput, 4000, "Test Note")
.Parameters.Append cmd.CreateParameter("STATUS_UPDATE", adVarChar, adParamInput, 4000, "This is a status update")
.Parameters.Append cmd.CreateParameter("SR_NUMBER", adVarChar, adParamInput, 20, "3-1234567890")
.Parameters.Append cmd.CreateParameter("ISSUE_TYPE", adVarChar, adParamInput, 20, "Issue Type")
.Parameters.Append cmd.CreateParameter("STATUS", adVarChar, adParamInput, 20, "Analysis")
.Parameters.Append cmd.CreateParameter("SCREENSHOT", adVarChar, adParamInput, 100, "c:\temp\test.png")
.Execute
End With
The code for obtaining a database connection is:
Private Function GetNewConnection() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim strConn As String
strConn = "Driver={Oracle in OraClient12Home2_32bit};DBQ=SERVER_ADDRESS;Uid=XXX;Pwd=XXX;"
oCn.Open strConn
If oCn.State = adStateOpen Then
Set GetNewConnection = oCn
End If
End Function
The entry in Event Viewer is:
Faulting application name: EXCEL.EXE, version: 16.0.10730.20102, time stamp: 0x5b938e29
Faulting module name: SQORA32.DLL, version: 12.2.0.1, time stamp: 0x58a24eba
Exception code: 0xc0000005
Fault offset: 0x000381b2
Faulting process id: 0x28cc
Faulting application start time: 0x01d73226e4ff39f4
Faulting application path: C:\Program Files (x86)\Microsoft Office\Root\Office16\EXCEL.EXE
Faulting module path: C:\app\client\product\12.2.0\client_1\SQORA32.DLL
Report Id: 6b6153b3-9e1a-11eb-a325-005056ab5187
Faulting package full name:
Faulting package-relative application ID:
User contributions licensed under CC BY-SA 3.0