Creating a BLOB in an Access database

Question

Using VB.Net I am trying to create an Access table with a BLOB field. I have successfully created the table using Access itself and declaring the field as "Ole Object". However, for customers, I must create the table programmatically. I have tried:

create table BLOBTABLE (BlobFileName TEXT(255)   NOT NULL ,  BlobData LongVarBinary )

and get a table create error. In my ConnectionString I'm using "Provider=Microsoft.Jet.OLEDB.4.0". I'm guessing it doesn't like the LongVarBinary datatype, but I'm not sure what else to use. Anybody know how to do this?

Thanks for the reply, Steve,. but that list doesn't say what the corresponding oledb data type is for ms Access "Ole Object". Everything I researched leads me to believe it's "LongVarBinary", but as I said, that throws an exception so I must be missing something. — Ron V
– Ron V, Commented Jan 10, 2017 at 1:42
Well with BINARY or LONGBINARY it will work, however without knowing exactly what you want to store there I can only suggest to avoid storing binary data in a database column. Instead store a relative path to the operating system file that contains the data. In this way you can avoid the limitations of MS-Access Jet format (2GB max mdb size). — Steve
– Steve, Commented Jan 10, 2017 at 9:14
HI Steve. Yep, right now I do store the relative path, but the customer wants to be able to store the referenced documents (could be pdf, or doc files) right in the database. So those are the kind of "large binary objects" I need to store. So I guess the question is when you declare a field to be "ole object" in the access application, what oledbtype does it actually translate to, and can, using the jet ver 4.0 engine, create that same type of field in a vb.net application? — Ron V
– Ron V, Commented Jan 10, 2017 at 14:17
Also, my understanding is that the "blob" field is stored separately (like a memo field) and does not contribute to the 2GB limitation of the mdb file. — Ron V
– Ron V, Commented Jan 10, 2017 at 14:18

Ron V · Accepted Answer · 2017-01-10 14:56:54Z

1

The proper way to programmatically declare a "blob" (ie "ole object" type) for an access database is: create table BLOBTABLE (BlobFileName TEXT(255) NOT NULL , BlobData oleobject)

Discovered this by trial and error. Didn't find it documented anywhere.

answered Jan 10, 2017 at 14:56

Ron V

111 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a BLOB in an Access database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related