Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

create an Access database based on 750 unique images

Posted on 2012-09-17
28
Medium Priority
?
357 Views
Last Modified: 2012-09-22
I want to build a database where each record corresponds to an image in a folder and have that image linked from a record in the database.  There will be approx 750 records (forms).  I want the image to be hyperlinked in the database so that it opens on demand.    Is there a way that I can automatically import a whole folder of images so I don't have to do it one record at a time?
0
Comment
Question by:dkern18195
  • 17
  • 10
28 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38407666
first create a table "tblImages" with fields
ImageID AutoNumber
ImageName   Text
ImagePath      Text

'use this vba codes to get the images from a folder

dim rs as dao.recordset
dim imgFile as string,imgPath as string

imgPath="C:\folderName\"

set rs=currentdb.openrecordset("tblImages")
'assuming your images extension is .jpg

imgFile=dir(imgPath & "*.jpg")
while imgFile<>""
 
      with rs
              .addnew
                   !Imagename=imgFile
                   !ImagePath=imgPath
                   .Update
      end with
      
      imgFile=Dir
      
Wend

rs.close
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38407714
Where do I enter the code?
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38407779
I get an invalid outside procedure call error
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38407823
where did you placed the codes?

post the codes that you have..
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38407830
Module from Visual Basic

As follows:


Dim rs As DAO.Recordset
Dim imgFile As String, imgPath As String

imgPath = "C:\Data\Projects\Appraisal\Dew\images\jpg\"

Set rs = CurrentDb.OpenRecordset("tblImages")

imgFile = Dir(imgPath & "*.jpg")
While imgFile <> ""
 
      With rs
              .AddNew
                   !Imagename = imgFile
                   !ImagePath = imgPath
                   .Update
      End With
     
      imgFile = Dir
     
Wend

rs.Close

End Sub
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38407839
Just a note, the folder where the jpg images are is called jpg  to differentiate from other formats that I store in other folders under the images directory

I really appreciate your help with this
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38407840
add a Name of the sub, and call the sub procedure using its name




sub getImageFiles()


Dim rs As DAO.Recordset
Dim imgFile As String, imgPath As String

imgPath = "C:\Data\Projects\Appraisal\Dew\images\jpg\"

Set rs = CurrentDb.OpenRecordset("tblImages")

imgFile = Dir(imgPath & "*.jpg")
While imgFile <> ""
 
      With rs
              .AddNew
                   !Imagename = imgFile
                   !ImagePath = imgPath
                   .Update
      End With
     
      imgFile = Dir
     
Wend

rs.Close

End Sub
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38407854
Now I get an "amibguous name detected "   error

code is as follows;

Sub dewimport()

End Sub

Dim rs As DAO.Recordset
Dim imgFile As String, imgPath As String

imgPath = "C:\Data\Projects\Appraisal\Dew\images\jpg\"

Set rs = CurrentDb.OpenRecordset("tblImages")

imgFile = Dir(imgPath & "*.jpg")
While imgFile <> ""
 
      With rs
              .AddNew
                   !Imagename = imgFile
                   !ImagePath = imgPath
                   .Update
      End With
     
      imgFile = Dir
     
Wend

rs.Close
Sub dewimport()

End Sub
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38407950
I finally got this to work but the images filenames are not hyperlinked
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38407983
to make the files hyperlink, you need to create a field Data Type Hyperlink in your table,

ImageFile   Hyperlink

and combine the fields ImagePath and ImageName using an update query

Update tblImages set ImageFile=[ImagePath] & [ImageName]


(you can also do this using the codes above)






Sub dewimport()


Dim rs As DAO.Recordset
Dim imgFile As String, imgPath As String

imgPath = "C:\Data\Projects\Appraisal\Dew\images\jpg\"

Set rs = CurrentDb.OpenRecordset("tblImages")

imgFile = Dir(imgPath & "*.jpg")
While imgFile <> ""
 
      With rs
              .AddNew
                   !Imagename = imgFile
                   !ImagePath = imgPath
                   !ImageFile= imgPath & imgFile
                   .Update
      End With
     
      imgFile = Dir
     
Wend

rs.Close

End Sub
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38408034
Now I get a Run-time error '3265'
Item not found in this collection
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38408048
I'm also concerned that this doesn't show a relative address.  If I put the database on a disk, it won't work.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38408154
<Now I get a Run-time error '3265'
Item not found in this collection>

did you add the field "ImageFile" to your table?


<  If I put the database on a disk, it won't work.>

you are not suppose to run a database from a disk. (it will not work)
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38408214
I did add the field.

If I put the database on another machine in a different directory relative to the C: will it work?
I use the disk or a flash to transfer it (sorry for not being clear).
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38409400
I got this to work, but it just loaded another set of records below the first, so I need to recreate the database.  Just deleting the fields starts the numbering at the last record entered.  Also, the links did not work when clicked on.
0
 
LVL 40

Expert Comment

by:als315
ID: 38409713
Look at sample in my article: Images on continuous forms
You can store path to images as separate parameter and it will be easy to move DB.
Included is sample. Unpack all files to some folder and run DB. Double click on image to open it.
Images.zip
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38409757
Thank you.  I will try to work on this  later today.  My database shows the image name underlined and hyperlinked but it does not open when clicked on.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38409761
<Also, the links did not work when clicked on.>

are you clicking the file from the table?
try displaying the records in a form.
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38409781
Link appears in underlined blue text the form but nothing happens when clicking on them other than the line highlights in blue.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38409946
check how the hyperlink field content was formatted..

see this link for reference

http://msdn.microsoft.com/en-us/library/office/aa221344(v=office.11).aspx
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38412115
I don't understand why this is so difficult.  No matter what I do, I cannot get the hyperlink to open the image.  It appears each record has to be done manually, and the further problem is that it wants to open the image in photoshop  instead of in the database when I do a linked image.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38412148
< instead of in the database when I do a linked image.>

if you want to display the image in a form, you don't need a hyperlink.

you need an ImageFrame control...

do you have a copy of the Northwind.mdb?

you can see how it was done in the Employees form.

what version of access are you using?
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38412153
I finally got the hyperlink to open, but the image opens in a browser window.   I need to have it just open in the database window.   I want the image to be linked instead of embedded so that it just opens in the Access field when the form is viewed.
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38412155
Access 2010
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38412161
see this video

http://www.youtube.com/watch?v=0GsF_F2RXY0

and other video relating to images on the same page in you tube
0
 
LVL 1

Author Comment

by:dkern18195
ID: 38412178
I just want a unique record for each image that has the image linked instead of embedded.  Then i will add other fields for descriptors, such as size, date, value, etc.    The video just shows how to reuse images in forms.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38412189
0
 
LVL 1

Author Closing Comment

by:dkern18195
ID: 38425547
I appreciate your help with this.  It turns out that the best solution was to run a batch file to create small images.  I could then drag and drop from my image editor into the field in form view.  I was able to get the file names to load, so it was just a matter of pulling the image file in.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question