ADODB ActualSize of blob (an xlsx type) retrieved is zero

General
Highlighted
Tourist

ADODB ActualSize of blob (an xlsx type) retrieved is zero

I am trying to retrieve a blob (an excel spreadsheet) by ADODB in MS Access.  I can retrieve/view the file from Teradata SQL Assistant but for some reason upon using ADODB.Recordset the blob object has actualsize of zero and hence unable to get the file.

I am able to connect to Teradata via DSN (ODBC connectivity). 

 

Here are my ODBC User DSN settings:

- The driver is "Teradata Database ODBC Driver 16.10"

- Session Character Set: ASCII

- Use Native Large Object Support is ticked

 

When clicking on the Driver Advanced Options - both Maximum Single LOB Bytes and Maximum Total LOB Bytes per Row are zero.

 

My teradata data table structure is:

CREATE TABLE db.devX_lob (
    id VARCHAR (80),
    file_type VARCHAR (80),
    binary_lob BLOB
) UNIQUE PRIMARY INDEX (id);

 

INSERT INTO db.devX_lob (id, file_type, binary_lob)
VALUES (?, ?, ?B);

 

SELECT ID, file_type, binary_lob from db.devX_lob;

BLOB.JPG

 

My VBA code is (MDAC 2.8)

Public Sub Connectx()
   Dim conn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim rs As ADODB.Recordset
   Dim strSQL As String
   Dim stm As ADODB.Stream
   Dim strF As String
   Dim bytes
  
   strF = "H:\"
     
   Set conn = New ADODB.Connection
   conn.CursorLocation = adUseClient
   'conn.Open "DRIVER={Teradata Database ODBC Driver 16.10};DBCNAME=**bleep**.prod.xxxx.**bleep**.au;Database=db;Uid=UserID; Pwd=MyPwd; Extended Properties=""EXTENDLOBSUPPORT=Yes"";"
   conn.Open "DSN=Teradata;DBCName=**bleep**.prod.xxxx.**bleep**.au;Database=db;User Id=USERID;password=MyPwd;Extended Properties=""ExtendLOBSupport=Yes;"";" 'OLEDB EnableLOBSupport

                                                                                                      
   strSQL = "SELECT binary_lob FROM DB.DEVX_LOB WHERE id = '20180222.xlsx';"
   Set rs = New ADODB.Recordset
   rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
   strF = strF & "20180222.xlsx" 'rs.Fields("ID").Value
   '
   Debug.Print rs.Fields("binary_lob").ActualSize ' is zero
  
   '
   'Set stm = New ADODB.Stream
   'With stm
   '   .Type = adTypeBinary
   '   .Open
   '   .Write rs.Fields("binary_lob").Value
   '   .SaveToFile strF, adSaveCreateOverwrite
   '   .Position = 0
   'End With
     
   Call DropObject(stm)
   Call DropObject(rs)
   Call DropObject(conn)
End Sub

 

Public Sub DropObject(obj As Object)
On Error Resume Next
   obj.Close
Exit_method:
   Set obj = Nothing
End Sub

 

Any advice? Thank you!