Import full path of photos into OLE OBJECT data type

[tnguyen315]

Hello all,
I have an access 2010 frontend and Sql server 2008 backend.

I was wondering, if I have a csv file containing a the full file path to a lot of photos, the case numbers that the photos are associated with, and the name I would like for each of the photos, Is there a way to import the photos from those full path from CSV file and upload the photos from those path into database as an OLE object data type?


I have table named "ImageOLEs"
Field names:
1. ImageID - Autonumber (primary key)
2. csnumb - number (required field) (case number)
3. ImageShortName - text (required field)
4. ImageItem - OLE Object

Thank you very much,

Twee

 

[MajP]

If using 2010 I can think of absolutely no reason to use the OLE data type. If you want to physically store the object use the attachment data type. If you just want to render them from a path then simply use the bound image control.

 

[MajP]

BTW here is how to load an attachment from a path.

In this example I have a table called "tblPics".
It has an attachment field called "attchPics"

Public Sub loadAttachFromFile(strPath)
  Dim rsAll As DAO.Recordset
  'An attachment field has a recordset of attachments stored behind the scenes
  Dim rsAtt As DAO.Recordset
  Set rsAll = CurrentDb.OpenRecordset("tblPics", dbOpenDynaset)
  'Add a new record to the tables recordset
  rsAll.AddNew
    'This is the confusing part.  The value property of an attachment field returns a recordset of attachments
    Set rsAtt = rsAll.Fields("attchPic").Value
    rsAtt.AddNew
       'All recordset of attachments has a field named filedata which holds the data. 
       'The loadfromfile data loads an attachment from a path
       rsAtt.Fields("FileData").loadFromFile (strPath)
    rsAtt.Update
  rsAll.Update
End Sub

 

[tnguyen315]

Thanks MajP for the quick respond. I also have another field named "FullPathFile" to store the path of photo/image.
However, i would like to know if we can import the Full Path of Those images from a csv file and converting them to OLE OBJECT.

For example: my csv file

csnumb Name Path
45748 front \\PhotosImages\45748\45748_front.jpg
45748 back \\PhotosImages\45748\45748_back.jpg
75214 front \\PhotosImages\75214\75214_front.jpg
75214 back \\PhotosImages\75214\75214_back.jpg

Thanks.
Twee

 

[MajP]

Can we agree we are not using OLE object, but either attachments or simply rendering the images. Unless you can convince me there is some need for this. I have seen code using BLOBS to do this, but never had success.

1) If the user is always going to have access to the server then simply store the path to the image in the database. All you have to do is use a bound image frame and it will render the object. No reason to store the images in the database. This will be the preferred solution especially if you have 100 of megs or more of images. It is easy, requires no code, and even works in continous forms
2) If you absolutely have to store the images. Then it link your CSV as a linked table. The code would be something like.

Public Sub loadAll
  dim strPath as string
  dim csNumb as string
  dim imgName as string
  dim rs as dao.recordset
  set rs = currentdb.opendrecordset("tblCSV")
  do while not rs.eof
    strPath = rs!Path
    csNumb = rs!csNumb
    imgName = rs!name
    loadAttachFromFile(strPath,csnumb,imgName)
    rs.movenext
  loop
end sub


Public Sub loadAttachFromFile(strPath as string,csNumb as string, imgName as string)
  Dim rsAll As DAO.Recordset
  'An attachment field has a recordset of attachments stored behind the scenes
  Dim rsAtt As DAO.Recordset
  Set rsAll = CurrentDb.OpenRecordset("tblPics", dbOpenDynaset)
  'Add a new record to the tables recordset
  rsAll.AddNew
    'This is the confusing part.  The value property of an attachment field returns a recordset of attachments
    rsAll!csNumb = csNumb
    rsAll!imgName = imgName
    rsAll!imgPath = strPath
    Set rsAtt = rsAll.Fields("attchPic").Value
    rsAtt.AddNew
       'All recordset of attachments has a field named filedata which holds the data. 
       'The loadfromfile data loads an attachment from a path
       rsAtt.Fields("FileData").loadFromFile (strPath)
    rsAtt.Update
  rsAll.Update
End Sub

The above assumes I have a clean table called "tblPics"
It has fields:
csNumb
imgName
imgPath
attchPic (attachment field)

 

[MajP]

Nevermind, now I see that you are using SQL server as the backend. Sorry I missed that. I have some code for OLE objects, let me see if I can find it. Never had success. Can you simply store the path?