DAO Error 3001 Saving a byte array to ole object field

i am looping through a large amount of information stored in an Access 2003 DB and storing it into another DB.

The point of the process is to have the data saved back into the DB as a byte array.

This works fine 95% of the time, but there are cases where my code throw an error 3001 Invalid argument. Please see the code below:
private sub MoveData
With rsSrc
            If .RecordCount > 0 Then
                .MoveLast
                .MoveFirst
                
                While Not .EOF
                
                    DoEvents

                    ReDim bArray(0)
                    strBuf = ""

                    
                    If Not IsNull(!Data) And !Data.FieldSize > 0 Then
                        strBuf = !Data
                        
                        ' Save to array
                        bArray = StringToByteArray(strBuf)
                         
                        rsDest.AddNew
                        rsDest!Date = !Date
                        rsDest!Data = bArray ' error 3001 here
                        rsDest.Update

                    End If

                    .MoveNext
                Wend
            End If
        End With
end sub
Public Function StringToByteArray(varInput As Variant) As Byte()

' ***************************************************************************
' Routine:       StringToByteArray
'
' Description:   Converts a string of data into a byte array [Range 0, 255]
'
' Parameters:    strInput - data string to be converted into a byte array
'
' Returns:       Byte array
'
' ===========================================================================
'    DATE      NAME / eMAIL
'              DESCRIPTION
' -----------  --------------------------------------------------------------
' 03-OCT-2000  Kenneth Ives  kenaso@home.com
'              Modified and documented
' ***************************************************************************
    
' ---------------------------------------------------------------------------
' Define local variables
' ---------------------------------------------------------------------------
  Dim lngIndex     As Long
  Dim lngLength    As Long
  Dim bytBuffer()  As Byte
  Dim bytData()    As Byte
    
' ---------------------------------------------------------------------------
' Store length of data string in a variable.  Speeds up the process by not
' having to constantly evaluate the data length.  Works great with loops
' and long strings of data.  Good habit to get into.
' ---------------------------------------------------------------------------
  lngLength = Len(varInput)
  If lngLength < 1 Then
      ReDim bytData(0)
      StringToByteArray = bytData
      Exit Function
  End If
  
' ---------------------------------------------------------------------------
' Resize the array based on length on input string
' ---------------------------------------------------------------------------
  ReDim bytBuffer(lngLength)
  ReDim bytData(lngLength)
    
' ---------------------------------------------------------------------------
' Convert each character in the data string to its ASCII numeric equivalent.
' I use the VB function CByte() because sometimes the ASC() function returns
' data that does not convert to a value of 0 to 255 cleanly.
' ---------------------------------------------------------------------------
  For lngIndex = 0 To lngLength - 1
      bytBuffer(lngIndex) = CByte(Asc(Mid$(varInput, lngIndex + 1, 1)))
  Next
    
' ---------------------------------------------------------------------------
' Copy data from memory to variable
' ---------------------------------------------------------------------------
  CopyMemory bytData(0), bytBuffer(0), lngLength
  
' ---------------------------------------------------------------------------
' Return the byte array
' ---------------------------------------------------------------------------
  StringToByteArray = bytData()
  
' ---------------------------------------------------------------------------
' Resize arrays to smallest size
' ---------------------------------------------------------------------------
  ReDim bytData(0)
  ReDim bytBuffer(0)
  
End Function

Open in new window


Also is there a better way of doing this or a way to compress the Array so it take sup less space in the DB?
DevLSSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
Hi

Why are you using an explicit Byte Array at all to perform this task?
I say explicit as strings are essentially that anyway, and you're bringing this data via a string - so it is clearly Unicode supported...
What sort of data is stored in this field?

Cheers.
DevLSSAuthor Commented:
the string data is RTF formatted
Leigh PurvisDatabase DeveloperCommented:
Yeah.  Why aren't you assigning it directly?
Is there some String simplification going on when you attempt that? (i.e. RTF formatting is lost? :-s)

        With rsSrc
            While Not .EOF                    
                If Not IsNull(!Data) And !Data.FieldSize > 0 Then
                    rsDest.AddNew
                    rsDest!Date = !Date
                    rsDest!Data = !Data '<--- With the value assigned directly? String altered?
                    rsDest.Update
                End If
                .MoveNext
            Wend
        End With
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

DevLSSAuthor Commented:
it was a size issue with the destination Database. I modified my code to track the size of the new database and if the new database was within 100MB of the 2GB limit, a new destination DB is created.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
That certainly falls into the category of "hard to diagnose remotely" ;-)
My question still stands though.

Cheers.
DevLSSAuthor Commented:
because the data in the source is not in RTF format, and the destination must be in RTF format. Otherwise, yes, it would have been a no brainer. :-) Thanks for your help.
DevLSSAuthor Commented:
i figured out the solution myself, it was a size issue with the destination DB
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.