FIX: Error "Not Enough Storage Is Available" with ADO (182423)

SYMPTOMS

When using the AppendChunk method of the ADODB.Recordset object, the following error occurs:

Not enough storage is available to complete this operation.

The error number returned is -2147024882 (0x8007000E).

RESOLUTION

To work around this problem, use one of the following two methods:

Open an ADODB.Connection object and set the CursorLocation property to adUseClient and use this connection with the ADODB.Recordset. -or-
Use only one AppendChunk call and append all of the data with only one call to AppendChunk. Note that this restricts you to a maximum chunk size of 511818 bytes.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

This bug was corrected in ActiveX Data Objects (ADO), version 2.0 and later.

MORE INFORMATION

Steps to Reproduce Behavior

The following sample code demonstrates the error when using Microsoft SQL Server as the database. If you uncomment the line indicated, the error no longer occurs.

Sample Code

   Sub ADOBlobFail()
     Dim conn As New ADODB.Connection
     Dim rs As New ADODB.recordset
     Dim bytBlock(256) As Byte
     Dim i As Long

     conn.Open "<DSN Name>", "sa", ""
     ' Uncomment the next line to avoid the error.
     ' conn.CursorLocation = adUseClient
     On Error Resume Next
     conn.Execute "drop table IMAGE1234"
     On Error GoTo 0
     conn.Execute "create table IMAGE1234 " & _
                  "( ID int primary key, DATA IMAGE )"
     rs.ActiveConnection = conn
     rs.Open "select * from IMAGE1234", , adOpenDynamic, _
             adLockOptimistic, adCmdText

     rs.AddNew
     rs.Fields(0).Value = 1
     For i = 1 To 1000
        rs.Fields(1).AppendChunk bytBlock
     Next i
     rs.Update

   End Sub