VBA Access: Run-time Error '2176'-The setting for this property is too long

[Prithvi Ragu]

There is an access database table called DemoImageT with a field called Image to store images (using OLE Objects). It has another field called ID which is a text field. It has values 1,2,3..etc. I have an Image holder called ImageBox1 in an Access form. When a button is clicked I want to display the image stored in the table in the image holder on the form. I executed a query and stored the results in a recordset. Then I set the picture property to the retrieved image. My code was:

Dim myConnection1 As ADODB.Connection
Dim myRecordSet1 As New ADODB.Recordset
Set myConnection1 = CurrentProject.AccessConnection
Set myRecordSet1.ActiveConnection = myConnection1

myRecordSet1.Open "SELECT * FROM DemoImageT WHERE ID = '1'"

If IsNull(myRecordSet1.Fields(1)) = False Then
  MsgBox ("Image present")
  ImageBox1.Visible = True
  ImageBox1.Picture = myRecordSet1.Fields(1)
Else
  MsgBox ("No image")
End If

I get the message box Image present. But then I get:

run time error 2176- The setting for this property is too long.
The error occurs in the line:

Me.ImageBox1.Picture=myRecordSet1.Fields(1)

Is there something wrong with the code? Is there any other way to retrieve images stored in an Access database using VBA and display it on a form? If the above method is correct, what might be wrong?

[zmbd]

Because displaying images stored within the database file itself is not directly supported, I normally do not recommend storing images within an Access database as they do fill up the file very rapidly. I would recommend storing only the link to the image within Access.

There is a way with a lot API calls, you basically store image to disk in the temp folder and then pull the link to the image back in to the OLE

ACC2010 has the image gallery, and that may make things a tad easier; however, you haven't indicated which version you are trying to use.

Also, why are you using "ADODB" instead of "DAO"?

BTW: The error is beacause you are trying shove the binary data into a text field.

[Prithvi Ragu]

@zmbd: I am using Access 2010 only. This application is going to be run in different systems. So,I stored the image itself as an OLE Object in the database. It would be difficult for the images to be stored in the same location in every system. So,I didn't store the link to the image in the db.
Is there any way to display the image like I am trying to do? Should I convert the binary stream to an image?

[zmbd]

OK,
One really should avoid using the OLE to store the pictures, it creates alot of bloat...

So, when all things look bad, I go back to the Northwinds example database, I was originaly going to look at the 2003 version; however, you've said you are using ACC2010 so I thought I'd take a look at the newer versions. Looking at the 2007 version, it appears that MS has actually done something worth while - the attachment field data type.

>NOTE> If you use this field upsizing later more than likely will result in some headaches, loss of sleep, the desire to bang one's head against the wall, and huge amounts of caffine containing products to disappear without a trace! You are warned! <<

So, what I think I would do is go back into the table design, delete all of currently stored images, change the field type to attachment, re-store your images, and then you can simply add this field to the form.
Attach files and graphics to the records in your database (ACC2007/2010)
I did this in a test database, worked like a charm - go figure.

In that you are going to install on various PCs, I think that the best thing is to store the image filename in the table, storing all of the images in a subfolder within the directory of the database. Thus, you can use the application.pat h property to get the current path to the where the database is located and then build the path to the folder. When you need the image, then you append the stored filename to the image path. This is what I do as it keeps the datafile smaller.

[Prithvi Ragu]

@zmbd: Thanks a lot. Thats exactly what I did!!