0

I support an application that has a SQL Server backend and an Access front end. A signature image is stored in a varbinary(max) column in a table in SQL Server, the Users table. The value in this field is displayed in various Access reports.
Periodically, a new User is added to the system. I have not succeeded in finding a way to perform this task programmatically. No matter how I load the image into the field in SQL, it will not display in the Access reports. I have to perform the following steps to get the image loaded and displaying correctly:
1. Open Doc.
2. Copy signature to clipboard.
3. Open Word.
4. Paste signature into Word.
5. Copy signature from Word to clipboard.
6. Open Access. Open linked Users table.
7. Paste into OLE signature field.
8. Update record.
I suspect the magic is in the copy of the image from Word to the clipboard, that it somehow creates an OLE object of some kind that is copied to the clipboard, then is able to be pasted into the varbinary(max) field via Access.
Can anyone shed any light on how one can programmatically (C#, Access, or?) load an image into a varbinary(max) field that Access can display as an image? I want to create an application that the end user can use to upload the signature image.

John Brainerd
  • 1
  • 2
  • *Only* the read/write of the binary stream for persistence is related to the database - as long that is successfully done the problem (or task) lies elsewhere. Also, for SQL Server check out the FILESTREAM type. – user2864740 Aug 28 '14 at 04:58
  • Right, I am able to load data to the column in the table. The problem I believe is that the image needs to be an OLE object, whatever it is that I am able to copy from the Word doc and paste into field in the Access linked table. – John Brainerd Aug 28 '14 at 16:05

1 Answers1

0

How do I display an image from Sql Server with Microsoft Access?

When an OLE field is bound to a 'Bound OLE Control' in a form, and a file is pasted into the control, the file is stored in a binary access-specific OLE wrapper. The control also expects the wrapper when displaying files/images, but if the file was saved into the table with code rather than pasting, the wrapper is not there.

Using unbound controls bypasses the issue as these controls don't require the OLE wrapper.

Community
  • 1
  • 1
Ben McIntyre
  • 1,972
  • 17
  • 28
  • Hm, that is some help. However, this is a fairly large app at this point, with 50+ Access reports all using the OLE field, etc. I guess I could change all of the reports, other controls, etc. to be unbound, but... – John Brainerd Jun 29 '18 at 22:16
  • Wow, that was a pause! It's not too hard to strip away the Access wrapper to make the data a 'raw' blob (if you need it, there's code at www.arrow-of-time.com in the Access Extension Framework - disclosure - my product). But adding a wrapper is very difficult. – Ben McIntyre Jul 01 '18 at 01:03