Search code examples
sql-serverms-accesshashvbavarbinary

How does varbinary datatype work to store image for a user to view in Access front end?

I did some research and the web has taken me all over the place and I still haven't found a suitable solution or guide to what I am trying to achieve.

I read up a similar post to what I am asking. Except that's #php Retrieving Image in SQL Server (varbinary)

Update: The "conventional" way is the create file directory and file path. This was my initial method to get the front end up and running for the user. My sup actually advised that I use this hash method nonsense which he can't even explain properly to me.

A user must be able to insert an image on the form, generate a report displaying the image as well.

How does the varbinary work to link sql and Access for what I am trying to achieve? I am currently playing around with my development to resolve it.

Any clarification? I have no other way of putting this question after my first edit.

This is the form The Picture field is OLE Object type because the SQL datatype is varbinary. I think this is how Access recognises it.

For each form entry a user should insert a picture to support the Findings.

Solution

  • A VARBINARY field in SQL server is a field that stores binary data, and is often used for storing files. You can fit entire files in this field.

    If you have a VARBINARY field in SQL server, and create a linked table in Access to the table with that field, it gets interpreted as an OLE object, since OLE objects are binary data too.

    You can use the Bound object frame control in Access to save images into OLE objects, both as an image (which is displayable), or as a package. This does not require any code. You can right click the field -> Insert Object -> Bitmap Image to insert bitmap images into an OLE field. However, this stores OLE object data along with the image data, which makes it very hard to work with using code, and nearly impossible in non-vba applications. Using this approach is only justifiable if you're sure you're going to stick with Access for the lifetime of the database, in my opinion.

    I've shared an example on working with VBA code and the OLE object here, but that uses hacky code that executes GUI operations. It's nearly impossible to port to other applications. I recommend you use the next way instead.

    Alternatively, you can directly store binary data in the OLE object/Varbinary field. This makes it a lot easier to deal with using VBA code or any future application, since it's just the file data stored in the field, there's no OLE object data stored with it. I've shared code to load binary data in a field and save it back to disk here.

    The hard part of this puzzle, if you're just working with binary image data, is displaying the image to the user. I've shared 3 approaches here, with code for one one of them. However, that's quite complex VBA code.