Retrieve Blob/Memo text fields using MS Access

[TomPose]

I have been trying to retrieve Blob memo fields from an Oracle database – unsuccessfully, for some time. When I open the oracle database using Access I see ‘OLE object’ in the field, but cannot see the underlying text. I can see the underlying text using other software (TOAD) but that does not allow me to manipulate the data as I would like.

How can I retrieve the underlying text using MS Access?

 

[goaway1234]

Unfortunately you are out of luck unless you are familiar with writing modules for access. BLOB/OLE Object fields are usually used to store non-character data, and access doesn't provide any built-in interface to edit or view their contents.

If it is feasible, consider changing your BLOB (binary-large-object-block) to a CLOB (character-large-object-block). Access will then treat it as a Memo field and you can view/edit the column in any way you want.

If the field must remain a blob, then you are stuck with dealing with using Recordsets in VBA code. There is a method, GetChunk, that you can use to get a byte array from the field. Then you will have to convert that byte array into a string. A simple example:

Public Sub Foo()
Dim rst As New ADODB.Recordset
Dim b() As Byte

    rst.Open "select * from doj_nd_test;",_
             CurrentProject.AccessConnection

    b = rst("mytext").GetChunk(16)
    
    Debug.Print ByteArrayToString(b)

    rst.Close
End Sub

Public Function ByteArrayToString(bytArray() As Byte) As String
    Dim sAns As String
    Dim iPos As String
    
    sAns = StrConv(bytArray, vbLowerCase)
    iPos = InStr(sAns, Chr(0))
    If iPos > 0 Then sAns = Left(sAns, iPos - 1)
    
    ByteArrayToString = sAns
 
 End Function

This will print out the contents of your field in lower case. This code may require some tweaking if the text in your blob is unicode. If it is stored as ASCII, then this will work fine. Look at the access help file for StrConv.

Nick Durcholz