Run Time error: -2147217887(80040e21) The system cannot find a message text for the message number 0x80040e21 in the message file for OraOLEDB

I am uploading the data in an Excel sheet to an Oracle database of 167 columns using Excel macros. I am getting this error in the execute statement.

I am facing difficulties in debugging this error.

Run Time error: -2147217887(80040e21) The system cannot find a message text for the message number 0x80040e21 in the message file for OraOLEDB

strSQL = "Update T_SAP_ZSSTABL_NEW set TSZ_CHARG = ?, TSZ_MATNR = ?, TSZ_MAKTX = ?, TSZ_MATKL = ?, TSZ_SEC1_MAX = ?, TSZ_SEC2_MAX = ?, TSZ_THICKNESS = ?, TSZ_TDCNO = ?," _
& "TSZ_LENGTH = ?, TSZ_OUT_DIA = ?, TSZ_IN_DIA = ?, TSZ_FKDAT = ?, TSZ_WERKS = ?, TSZ_CUSCD = ?, TSZ_FTRAMT = ?, TSZ_VRKME = ?, TSZ_FKIMG = ?, TSZ_RECDATE1 = ?," _
& "TSZ_QCODE = ?, TSZ_ZWERKS = ?, TSZ_AUART1 = ?, TSZ_MRK_CUST_NM = ?, TSZ_SHTO = ?, TSZ_CHARG1 = ?, TSZ_MATNR1 = ?, TSZ_TOTVAL = ?, TSZ_SPART = ?, TSZ_MANDT = ?," _
& "TSZ_LZONE = ?, TSZ_ZSSCAMT = ?, TSZ_PROD_DATE = ?, TSZ_PROD_ITEM = ?, TSZ_POEDAT = ?, TSZ_ERDAT = ?, TSZ_REFSO = ?, TSZ_AR4DAT = ?, TSZ_AR4NO = ?, TSZ_AR4_TYPE = ?," _
& "TSZ_SECHECESS = ?, TSZ_LIFNR = ?, TSZ_SHIPMD = ?, TSZ_MRK_DEST = ?, TSZ_ZTILAMT = ?, TSZ_ZFC1AMT = ?, TSZ_ZFMCAMT = ?, TSZ_ZINAAMT = ?, TSZ_ZEF0AMT = ?, TSZ_ZWF0AMT = ?," _
& "TSZ_ZCHAAMT = ?, TSZ_ZDCAMT = ?, TSZ_KZWI4 = ?, TSZ_KZWI3 = ?, TSZ_LISTPRICE = ?, TSZ_GROSSWT = ?, TSZ_LAND1 = ?, TSZ_NIELS = ?, TSZ_MVGR1 = ?, TSZ_KONDM = ?, TSZ_AUART = ?," _
& "TSZ_KURRF = ?, TSZ_PGROUP = ?, TSZ_DRAW_TYPE = ?, TSZ_AESKD = ?, TSZ_LOADCHRG = ?, TSZ_LGORT = ?, TSZ_NAME2 = ?, TSZ_SHTNAME = ?, TSZ_TOCIND = ?, TSZ_MARK_CUST_DESC = ?, TSZ_TARE_WEIGHT = ?," _
& "TSZ_GROSS_WEIGHT = ?, TSZ_NET_WEIGHT = ?, TSZ_VKBUR = ?, TSZ_MVGR2 = ?, TSZ_VTWEG = ?, TSZ_SPEC = ?, TSZ_LEN2 = ?, TSZ_LEN1 = ?, TSZ_CLASS = ?, TSZ_END_FINISH = ?, TSZ_SUR_FINISH = ?," _
& "TSZ_CATEGORY = ?, TSZ_MILL = ?, TSZ_RUNDATE = ?, TSZ_CESSVAL = ?, TSZ_WAERK = ?, TSZ_IDLEFRT = ?, TSZ_FRTACCR2 = ?, TSZ_FRTACCR1 = ?, TSZ_STPRS = ?, TSZ_BSTDK = ?, TSZ_POSEX = ?," _
& "TSZ_BSTNK = ?, TSZ_WCAINV = ?, TSZ_J_1ICHID = ?, TSZ_LENGTH_CR = ?, TSZ_PCODE = ?, TSZ_EIKTO = ?, TSZ_KDMAT = ?, TSZ_CPUDT = ?, TSZ_TRGRP = ?, TSZ_DRCPT = ?, TSZ_TRTIME = ?," _
& "TSZ_GRQTY = ?, TSZ_GRNO = ?, TSZ_KDKG2 = ?, TSZ_WAGON_RLY = ?, TSZ_HUBLIFNR = ?, TSZ_TNAME1 = ?, TSZ_LLIEF = ?, TSZ_TRNSIND = ?, TSZ_REGIO = ?, TSZ_DESTDESC = ?, TSZ_DESTCD = ?," _
& "TSZ_VGBEL = ?, TSZ_POSNV = ?, TSZ_VBELV = ?, TSZ_AUPOS = ?, TSZ_AUBEL = ?, TSZ_MRP = ?, TSZ_DISCLP = ?, TSZ_LSTPRC = ?, TSZ_CAMNO = ?, TSZ_KZWI1 = ?, TSZ_DUEDT = ?," _
& "TSZ_ZTERM = ?, TSZ_NETWR = ?, TSZ_OTHERS = ?, TSZ_KWERT = ?, TSZ_SERIAL = ?, TSZ_STCEG = ?, TSZ_TOTAX = ?, TSZ_SRTAX = ?,TSZ_ADDSTAMT = ?, TSZ_STAMT = ?," _
& "TSZ_BSTCSTVAL = ?, TSZ_BSTCSTPERCENT = ?, TSZ_IND = ?, TSZ_EDAMT = ?, TSZ_MATLVAL = ?, TSZ_MATLRATE = ?, TSZ_QTY_METER = ?, TSZ_QTY_NO = ?, TSZ_KUNRG = ?," _
& "TSZ_CUSNAME = ?, TSZ_TYPE = ?, TSZ_PERIOD = ?, TSZ_VKORG = ?, TSZ_FKART = ?, TSZ_ETOIND = ?, TSZ_ZCHARG = ?, TSZ_ZMATNR = ?, TSZ_VTEXT = ?, TSZ_KALKS = ?," _
& "TSZ_VPRS_COST = ?, TSZ_WBS_ELEMENT = ?, TSZ_PRODCTN_ORDER_NO = ?, TSZ_SHOPPING_CART_NO = ?, TSZ_WOODEN_PALLET_WT = ?, TSZ_ZFINCHRG = ?, TSZ_ZATXAMT = ?, TSZ_VBELP1 = ?, TSZ_VBELN1 = ?, TSZ_VBELN_M = ? where TSZ_VBELN = ? and TSZ_POSNR = ?"


cmd.CommandText = strSQL

Set MyParameter = cmd.CreateParameter("CHARG", adVarChar, adParamInput, 10, VBELN)
cmd.Parameters.Append MyParameter

Set MyParameter = cmd.CreateParameter("MATNR", adVarChar, adParamInput, 6, POSNR)
cmd.Parameters.Append MyParameter

Set MyParameter = cmd.CreateParameter("MAKTX", adVarChar, adParamInput, 10, CHARG)
cmd.Parameters.Append MyParameter
.
.
.
.
.

Doing this for each parameter

And lastly

 cmd.Execute

With this many parameters, the issue can be any one item including mismatched data type, data length, etc. Here is where the art known as debugging must be exercised.

1. Carefully check the data types of table (T_SAP_ZSSTABL_NEW). Oracle may have data types that are not immediately translatable toby ADO types such as BLOB, CLOB, BFILE, high precision numeric types, maximum character types, or extension types (XML, media, etc.).

2. Try extending data lengths (or leave as blank in parameter assignment):

   Set MyParameter = cmd.CreateParameter("MATNR", adVarChar, adParamInput, 100, POSNR)
   
   Set MyParameter = cmd.CreateParameter("MATNR", adVarChar, adParamInput, , POSNR)
   

   If needed, check variable lengths with VBA's Len() to raise a user warning prior to appending parameters.

3. Try running the SELECT version of query with same parameters to return a single row of results. If same error occurs, start with first param, then add a second or batch of 10 sequentially to isolate the problematic item.

   SELECT ? AS TSZ_CHARG_PARAM, ? AS TSZ_MATNR_PARAM, ? AS TSZ_MAKTX_PARAM,
          ...
   FROM T_SAP_ZSSTABL_NEW
   WHERE TSZ_VBELN = ? AND TSZ_POSNR = ?
   

4. Refactor code for consistency, as you may have missed one or misaligned one which can occur with many repetitive lines. Always aim for DRY (Don't Repeat Yourself) code. One option is to use a dictionary with key/value pairs instead of 160+ variables or named ranges:

   Dim key As Variant
   Dim paramDict As Object
   Set paramDict = CreateObject("Scripting.Dictionary")
   
   For ...
       paramDict.Add "KeyX", "ValueX"
   Next ...
   
   ...
   
   With cmd
     .ActiveConnection = myOracleConn
     .CommandText = strSQL
     .CommandType = adCmdText
   
     For Each key In  paramDict.Keys
        .Parameters.Append .CreateParameter(key, adVarChar, adParamInput, , paramDict(key))
     Next key
   
     .Execute
   End With
   

   If you have mixed types, try creating different dictionaries for each types and iterate them in sequence to append parameters. And adjust UPDATE with all same types near each other. Recall UPDATE does not need to follow the order of columns in table definition.

5. If possible (or long-term solution), consider re-designing the wide format table of TSZ columns into a normalized form of long format.

   ID   POSNR   VBELN   Indicator   Value
    #     XXX     XXX       CHARG     XXX
    #     XXX     XXX       MATNR     XXX
    #     XXX     XXX       MAKTX     XXX
   ...
   

   Though this will add 167 rows for each unique identifier, in database design rows are much cheaper than columns and so querying will be easier, and data storage more scalable, and maintenance more efficient. For instance, a new metric does not need a new defined column with all meta info but simply a new row. And with Oracle's PIVOT you can easily re-render the wide format.

All in all, there is no single, clear way to debug a runtime error like this borne from an API extension like database connection. SQL syntax or VBA object referencing can easily be handled but data-specific issues requires creatively digging through the overall process.