store an image in access database

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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:

If you're willing to buy that, it would be your simplest way to do this.
Dale FyeOwner, Developing Solutions LLCCommented:
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.
Gustav BrockCIOCommented:
We used the csXImage control for exactly this.
Not free, but support is excellent, should you need it.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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...
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.
João serras-pereiraAuthor Commented:
Welll ...
But how exactly? can anyone send me a sample code?
Scott McDaniel (Microsoft Access MVP - EE MVE )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)
  Dim rsAttach As DAO.Recordset2
  Set rsAttach = rs.Fields("YourAttachmentField).Value
  rsAttach.Fields("FileData").LoadFromFile "Path To Your Image"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
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.
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?
Gustav BrockCIOCommented:
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.
    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
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?
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.
João serras-pereiraAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Images and Photos

From novice to tech pro — start learning today.