Why do I get the error "Multiple-step OLE DB operation generated errors" when using Microsoft ActiveX Data Objects (ADO) to write to BLOBs. What can I do?

This article provides a workaround if you're using either the Easysoft InterBase ODBC driver or the Easysoft Firebird ODBC driver to write to BLOBs from ADO and get the following error:

Run-time error '2147217887 (80040e21):'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

To reproduce this error:

1. Create the following table:

   CREATE TABLE
       "BLBCHECK" (
           "KY" INTEGER NOT NULL,
           "BLB" BLOB SUB_TYPE 1,
           PRIMARY KEY ("KY")
       );

2. Using Microsoft Visual Basic or Visual Basic for Applications (VBA), add a reference to Microsoft ActiveX Data Objects 2.5+.
3. Run the following code:

   Dim con As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   
   con.Open "DSN=IB"
   rs.Open "select * from blbcheck", con, adOpenDynamicm, _
       adLockOptimistic
   
   rs.AddNew
   rs.Fields(0) = 21
   rs.Fields(1) = "1"
   rs.Update
   rs.Close
   con.Close

The error occurs when you try to INSERT "1" into Fields(1). To work around this problem, use the AppendChunk method to append the data instead.

Dim conADO As New ADODB.Connection
Dim cmdADO As New ADODB.Command
Dim prmText As New ADODB.Parameter

Dim strSQL As String
Dim strText As Variant
Dim lngTrxNo As Long
Dim lngOccurNo As Long
Dim lngSeq As Long

Dim intChar As Integer
Const conChunkSize = 100

lngTrxNo = 1
lngOccurNo = 1
lngSeq = 13

strSQL = "insert into blbcheck ( ky, blb ) values ( 100, ? )"

' To prove this works, we will insert 2500 characters,
' with a linefeed on every 100th character.
strText = ""
For intChar = 1 To 2500
    strText = strText & "A"
    If intchr = 100 Then
        strText = strText & Chr(10)
    End If
Next

'-------------------------------------------------------'
' Connect to the data source.
'-------------------------------------------------------'
conADO.Open "DSN=IBv4"

'-------------------------------------------------------'
' Once connected, prepare the SQL statement.
'-------------------------------------------------------'
cmdADO.ActiveConnection = conADO
cmdADO.CommandText = strSQL
Set prmText = cmdADO.CreateParameter("blb", adLongVarChar, _
    adParamInput, Len(strText), "")
If lngSize = 0 Then
    lngSize = Len(strText)
End If
lngOffset = 0

'-------------------------------------------------------'
' Insert the bound parameter a bit at a time.
'-------------------------------------------------------'
Do While lngOffset < lngSize
    varChunk = Left(Right(strText, lngSize - lngOffset), conChunkSize)
    prmText.AppendChunk varChunk
    lngOffset = lngOffset + conChunkSize
Loop

cmdADO.Parameters.Append prmText

'-------------------------------------------------------'
' Execute the query.
'-------------------------------------------------------'
cmdADO.Execute
conADO.Close

Use the AppendChunk method on a Field or Parameter object to fill it with long binary or character data. In situations where system memory is limited, you can use the AppendChunk method to manipulate long values in parts rather than in their entirety.