0

I have a table with a field that is of type OLE Object. I then have a form with a number of fields. Some are just text fields. But, I also have an Image object on the form. When a user clicks a button, a dialog opens and they can pic a photo. Once they select the photo, the Image object displays that picture. I want to save that image to database. What I have only seems to save a reference to where the file is located, on the computer. So, this would not work if the db is moved. Here is the code I have:

Dim wrkCurrent As DAO.Workspace
Dim dbs As DAO.Database
Dim rstPerson As DAO.Recordset

Set wrkCurrent = DBEngine.Workspaces(0)
Set dbs = CurrentDb
Set rstPerson = dbs.OpenRecordset("SELECT * FROM tbl_person WHERE id =" & ID)

With rstPerson
 .Edit
      !bio_photo = Me.Image37.picture
 .Update
End With

How would I save the image file to the OLE Field, so that I could load it back to the Image object, on the form, at a later time?

Thanks

Answer:

So, what I ended up doing was following this https://support.microsoft.com/en-us/kb/210486 [EDIT: that link is now dead, try this instead]

I use the readBLOB function to read the file and save it into the database. Then, when I run a report or open a form that has the picture, onload, I use the WriteBlob function to write the file to a temp folder and then use that path to populate an Image object.

desertnaut
  • 57,590
  • 26
  • 140
  • 166
jason
  • 3,821
  • 10
  • 63
  • 120
  • The link the OP provided is no longer online, however, the article is still available in the Microsoft KB Archives here: https://mskb.pkisolutions.com/kb/210486 – Sean Cushman Feb 13 '21 at 11:24

1 Answers1

1

Databases can grow to enormous sizes when you start embedding images, leading to slower queries, crashes, timeouts, and dissatisfied users. Use OLE with caution when adding images into a database

Simple Google search has how to load ole object from folder into table

And a search on this site comes up with auto attach images using vba

Community
  • 1
  • 1
dbmitch
  • 5,361
  • 4
  • 24
  • 38
  • Yes, I know saving objects in a table is not always the best idea but we are capping the save to 100kb so I think it will be OK. And, considering the business case, it's the only option I have. Thanks for the response. – jason Jun 10 '16 at 17:53
  • So, this method seems to not work for OLE Object field types but for a field that is an Attachment. I read it was better to save files as OLE. – jason Jun 10 '16 at 18:30
  • Any luck with above url? – dbmitch Jun 12 '16 at 22:37
  • Sorry for not responding. Switching between projects can make me lose track. I actually ended up using this: https://support.microsoft.com/en-us/kb/210486. I'll post more into my original questsion – jason Jun 23 '16 at 18:50