File with images

[kojak43]

I have a folder that contains images. They are all .jpg's.
Is there some way I can import that folder's images and image names into Access?
What I am trying to do is have Access show me the image without it being embedded. Right now, the images are embedded so the DB is huge.

I keep reading there is a way to just furnish the address of the photo's location and it will allow the image to appear in a report. I have tried numerous times to no avail. I have tried this link: http://www.mrexcel.com/board2/viewtopic.php?t=76427 No Joy.

I think what I am doing wrong is that I am tring to use the folder of images, rather than making a new access table to hold those images.

What I am trying to avoid is creating that table from scratch. Hence, the request for a way to import to Access a folder that lists the images.

kojak

 

[bmacr]

I made a recipes database with pictures that does what you want. It has a table called tblPics2 with only one field (text) called Pic2. During the form's Open event it fills the table with filenames ending in .jpg or .gif. I run a delete query that clears the table on Close of the form to ensure that there is always a fresh list of pictures when you open the form.
The pictures are located in a folder called Recipe_Pics which MUST be located in the same folder as the .mdb file.
I have a field in my main form's table where the picture's filename can be stored so that it's linked to the appropriate record. I use the form's Current event to update the picture on the image control as I click from record to record.
Here is the code I use to fill the table...

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Errorhandler

Dim MyPath As String
Dim MyJpg As String
Dim MyGif As String
Dim MyFiles As String
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
DoCmd.SetWarnings False

Set db = CurrentDb
    filename = db.Name
    
    'Get current path
    MyPath = Mid(filename, 1, Len(filename) - Len(Dir(filename)))
    'pathname = MyPath
    
    MyFiles = ""
    
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
    strSQL = "Select Pic2 from tblPics2"
    rst.Open strSQL, conn, , adLockOptimistic

'Re-fill table with .jpg's
MyJpg = Dir(MyPath & "Recipe_Pics\" & "*.jpg") 'Looks for .jpg's
Do While MyJpg <> ""
    rst.AddNew
    MyFiles = MyJpg
    MyJpg = Dir
    rst![Pic2] = MyFiles
    rst.MoveNext
Loop

'Now add .gif's to same table
MyGif = Dir(MyPath & "Recipe_Pics\" & "*.gif") 'Looks for .gif's
Do While MyGif <> ""
    rst.AddNew
    MyFiles = MyGif
    MyGif = Dir
    rst![Pic2] = MyFiles
    rst.MoveNext
Loop

Me.Requery
Set rst = Nothing
Set conn = Nothing
    DoCmd.SetWarnings True
    DoCmd.MoveSize 0, 0
    Exit Sub

Exit_Errorhandler:
    Exit Sub

Errorhandler:
    MsgBox Err.Number
    MsgBox Err.Description
    Resume Exit_Errorhandler
End Sub

You will have to change the table, field, and folder names to suit your situation, obviously.

 

[kojak43]

Thanks BMACR, I was so pleased to see your reply after my long weekend. I have been trying what you wrote between my real assignments.

I have found the following to be curious. The first time I opened my frmImages, it did as you said. It populated the form with 487 image names. Too cool. However subsequent openings of the form has added 487 names, so the current form now contains 2,922 names. Obviously it keeps running and adding the names. Is this where you run the delete query? Is that something you need to do manually? That is, run the delete query each time you populate the form?

The next problem I have is with your third graph. I did the first two graphs OK, (except for the delete query) but the third confuses me. Understand, I am not that well versed in Access.

Is the main form's table, tblPics2? If so, how does it link to the appropriate record? How is the forms Current Event recruited to update the picture?

Sorry to be so dense.
k

 

[bmacr]

The main form's table is not tblPics2. It is another table that has other recipe info, however there is one (text) field in that table that 'permanently' stores the filename of the chosen picture. Each time the Current event for the main form fires, it looks for that filename in the appropriate folder.

Using the form's Current event, you set the image control's Picture property to the filename field in the underlying table. Include the path.
e.g.
Me.ImgPicture.Picture = MyPath & "Recipe_Pics\" & Me.recPic

The table (tblPics2) that has just the one field and is filled with filenames is only used when I want to browse for a picture. I use a separate form for this (frmPicSelect), which is bound to tblPics2. When I double click the picture on the main form, frmPicSelect is launched. I browse to a picture and double-click it. This sets the picture on the main form to whatever the current picture is on frmPicSelect.

The delete query is executed every time the main form is closed, thereby leaving the tblPics2 empty. It is then re-filled the next time the main form is opened. You may wish to do this differently, as it may slow the form's opening. You could have both the delete query and the re-fill code run off of a 'Refresh' button.
The following is the code I use to clear the table on Close of the main form:

Private Sub Form_Close()
On Error GoTo Errorhandler
'** Empties the table **

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
    strSQL = "Select * from tblPics2"
    rst.Open strSQL, conn, , adLockOptimistic
    strSQL = "Delete * from tblPics2"
    DoCmd.SetWarnings False ' Suppresses warning prompt
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    rst.Close
Set rst = Nothing
Set conn = Nothing

    Exit Sub

Exit_Errorhandler:
    Exit Sub

Errorhandler:
    MsgBox Err.Number
    MsgBox Err.Description
    Resume Exit_Errorhandler
End Sub

Sorry if this seems long-winded. Hope it helps.

p.s. You're not dense. I only just learned this myself.

 

[kojak43]

Sorry. I have not ignored your help. I have been traveling and have not had a chance to use your advice.

I'll get back into it soon and will either give you a thanks with a wet sloppy kiss, or beg for more counsel.

k