CODEWITHSUNDEEP
vbams-access
Sn_Srm
Sn_Srm

Reputation: 101

Linked table OLE Object shows as null in a recordset while it is displayed on query view as OLE Object

I have the following Oracle table connected to Access via ODBC

ID (INT) FILE_TYPE (VARCHAR(20)) BLOB_DATA (BLOB)

The BLOB_DATA field holds excel files and I want to download them.

In the Access Query view, the field is shown as OLE Object

I have written the following code till now - 

Dim db as Database
Dim rst as Recordset

Set db = CurrentDb

Set rst = db.openrecordset("SELECT BLOB_DATA FROM MY_TABLE;")
Dim fld as Variant

fld = rst.Fields(0).value

When I inspect fld field, it shows up as null even though the Access Query view shows it as an OLE Object. Does it have to do something with ODBC and linked tables? or am I missing something?

Upvotes: 0

Views: 328

Answers (1)

Erik A
Erik A

Reputation: 32682

You can't just set variants equal to the value of OLE or BLOB fields, since they can contain very large objects. You need to use .GetChunck on the field to return chuncks of data of the file, so you don't have to load the entire BLOB into a variable.

Working with .GetChunck enables you to have only a small part of the file in memory when writing the entire file to disk.

The code required is not small, but the following Microsoft article describes it well: https://support.microsoft.com/en-us/help/210486/acc2000-reading--storing--and-writing-binary-large-objects-blobs (yes, it is Access 2000, but I couldn't find a more up-to-date source from Microsoft).

If you have remaining questions, feel free to ask them in the comments.

Upvotes: 2

Related Questions