store an image in access database

Hi.
I am developing an ms/access database that needs to store images (simple JPEG, around  50K each. In total, we expect to have a maximum of 500 - 700 images, so I am thinking to use OLE or attachment fields and embed.
The images are stored in files.
Can anyone help me and let me know
1. should I go to Attachment or OLE?
2.  how to set up the field so the existing file will be incorportated
3. how to show the file on (i) screen and (ii) report
any help will be greatly appreciated

PS. I am ready to buy an Active X that helps me
João serras-pereiraAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
To use an Attachment field, just add the field to your table, add the Attachment control to your Form, and set the Control Source of that Attachment control to the Attachment field. You can then move just drag/drop your pictures into that Attachment field.

Dim rs As DAO.Recordset2
Set rs = Currentdb.OpenRecordset("SELECT * FROM YourTable WHERE SomeField=SomeValue")
Do Until (rs.EOF and rs.BOF)
  rs.Edit
  Dim rsAttach As DAO.Recordset2
  rs.Edit
  Set rsAttach = rs.Fields("YourAttachmentField).Value
  rsAttach.AddNew
  rsAttach.Fields("FileData").LoadFromFile "Path To Your Image"
  rsAttach.Update
  rs.Update
  rs.MoveNext
Loop
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
1. Attachment fields are the suggested way to do this.
2. You can import those to your records, either manually or by code.
3. Access includes an Attachment control that lets you show users the items on your Forms or Reports.

FWIW, the DBPix control is highly regarded by those who work with images in Access:

http://www.ammara.com/

If you're willing to buy that, it would be your simplest way to do this.
0
 
Dale FyeCommented:
but keep in mind that images can take up a lot of the available space in a database.  Most developers who work with Access and images or what could be attachments prefer to store the images in a folder on a file server, and then save the pointer to the file in the database.
1
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gustav BrockConnect With a Mentor CIOCommented:
We used the csXImage control for exactly this.
Not free, but support is excellent, should you need it.
0
 
João serras-pereiraAuthor Commented:
Hi -

Thanks for the replies. I reckon that I was not explicit on my question. I do have a JPEG file that I construct on a different part using an active X
What I want now is tio have the file embedded in my access DB. The volume is no big deal as the app should last 4 years, at the most, and capture around 400 - 500 images 200-300 Kb images in total; that is,  less than 200 Mb or 10% of the current max capacity of Access. So OLE.

But I simply do not know how to put the file INSIDE the OLE :(
This is what I am asking help for.

I've found an  old msoft article on this, but I am a bit unsure so I asked for help...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should NOT use OLE to store images in an Access database. You should use an Attachment field, or store a link to the image.
0
 
João serras-pereiraAuthor Commented:
Welll ...
But how exactly? can anyone send me a sample code?
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Well, you could store the pictures directly in the database - but that should be for the convenience of the application, not as a storage for the pictures, as it is can be quite cumbersome to extract the pictures from OLE fields.

This is very easy, but do a test first to study the size of the database, as it may grow faster than the size of the picture files may indicate.
If too much, do as we did: Save the pictures in a separate database file with one table with two fields: ID (unique) and Picture. Then link this table, and whenever a picture is needed, join the picture table.
This way, you will have a "clean" 2 GB database file size for the pictures while your main database backend will remain untouched by the pictures.
0
 
João serras-pereiraAuthor Commented:
Hi Gustav -
Yes, I admit it is for the convenience of the app (and backup); my problem is that, in the first place, I do not know how to put the file in the OLE (and then show it on the screen). And your suggestions is quite good so I'll naturally follow it,

On Scott's example, I am scratching my head a bit: what happens if the file path and name are always the same?
0
 
Gustav BrockConnect With a Mentor CIOCommented:
That's where the picture control comes into play, here using csx and setting the background picture of a modal popup form (the simplest method of fullsize display of a picture) from a presaved TIF fax image:

    With csx
      .ReadBinary gfTIF, Me!ImageDate.Value
      ' Open modal form.
      Forms!FrmDisplay.PictureData = .PictureData
      ' Clean up.
      .Clear
    End With

Open in new window

That said, using an attachment field requires no third-party ActiveX control.

Still, the really simple method is to copy the pictures to a folder and name them with some unique filenames, and then retrieve them as needed. See the demo here:
Show pictures directly from URLs in Access forms and reports
0
 
João serras-pereiraAuthor Commented:
Hi -
I reckon that I must follow your advices, namely for CSX. I am hesitating  on attachment as it is not  upward compatible with sql server and I may find myself in a big trouble.

One last question, for Gustav: do you know if CSX does have scroll methods?
0
 
Gustav BrockCIOCommented:
I don't think it had, but it is may years ago I used.
There was a sample to download - an Access file - and play with using the evaluation license.

But the quick method to find out is to ask Simon - the developer.
0
 
João serras-pereiraAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.