By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,460 Members | 1,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,460 IT Pros & Developers. It's quick & easy.

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

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. Dim myConnection1 As ADODB.Connection
  2. Dim myRecordSet1 As New ADODB.Recordset
  3. Set myConnection1 = CurrentProject.AccessConnection
  4. Set myRecordSet1.ActiveConnection = myConnection1
  5.  
  6. myRecordSet1.Open "SELECT * FROM DemoImageT WHERE ID = '1'"
  7.  
  8. If IsNull(myRecordSet1.Fields(1)) = False Then
  9.   MsgBox ("Image present")
  10.   ImageBox1.Visible = True
  11.   ImageBox1.Picture = myRecordSet1.Fields(1)
  12. Else
  13.   MsgBox ("No image")
  14. End If
  15.  
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:

Expand|Select|Wrap|Line Numbers
  1. Me.ImageBox1.Picture=myRecordSet1.Fields(1)
  2.  
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?
Mar 24 '14 #1

✓ answered by 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.path 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.

Share this Question
Share on Google+
4 Replies


zmbd
Expert Mod 5K+
P: 5,397
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.
Mar 24 '14 #2

P: 4
@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?
Mar 25 '14 #3

zmbd
Expert Mod 5K+
P: 5,397
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.path 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.
Mar 25 '14 #4

P: 4
@zmbd: Thanks a lot. Thats exactly what I did!!
Mar 28 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.