add ImageAvailable = True

[Maureen]

I would like to make image files available from access in order to create a
"merged" cataloge. I have my report and query completed and it works great.
Now all I need to do is update my table with the path and file name to the
images. I am working with a single table [Inventory] with:

[Inventory].[ProdID] as the key, [Inventory].[PathtoImage],
[Inventory].[ImageName] and [Inventory].[ImageAvailable] (there are other
fields that do not affect what I need here. My PathtoImage =
\\graphics01\images the ImageName = [Inventory].[ProdID].jpg
[ImageAvailble] is a check box that defaults to false.

We have a few hundred images available and we are adding more each day, we
have over 20,000 products. I am hoping that there is a way to create an
update query with some code that will scour \\graphics01\images and when it
finds an image for a record have it set ImageAvailble to True

I know very very little about using VB in queries but I am quite sure that
this can be done. Any suggestions would be appreciated!

M

 

[Diana Criscione]

Maureen,

I'm not exactly sure I understand what you want to do but as I understand it, you just want to update the ImageAvailable field to TRUE if there is an image in the images folder that matches the product ID. Is that correct? Are you storing the image name as well? If it is the same as the prodID (plus the .jpg) there really isn't a need to store is it there? You just need to know if it's available.

Either way, what you need is a loop that will iterate through the files in the images folder and match it against the product ID's.

First what I would do is create a recordset of the filenames in the images folder.
When you add the filenames, I would "cut off" the extension of the filename so you just have to worry about the actual name.

Second, using your inventory table, loop through each of the records one by one and look for a matching filename in the first recordset. When a match is found, you update Available = True. If you are also needing the store the image name, do it at the same time.

Try this....

''
'' GET FILE NAMES FROM GRAPHICS FOLDER
''
Dim sFileDir As String
Dim rsFileInfo As ADODB.Recordset
Set rsFileInfo = New ADODB.Recordset

' create the filename field -- this is a string data type, length 255
rsFileInfo.Fields.Append "FileName", adBSTR, 255

' open the recordset
rsFileInfpen

' get the files in the correct directory
sFileDir = Dir("C:\graphics01\images\*.jpg") ' change the path as necessary

Do While sFileDir <> ""
If sFileDir <> "." And sFileDir <> ".." Then
rsFileInfo.AddNew
rsFileInfo!FileName = sFileDir
' alternatively, use the next line instead to get the file name
' minus the last four characters ".jpg"
' rsFileInfo!filename = Left(sFileDir, Len(sFileDir) - 4)
rsFileInfo.Update
Debug.Print rsFileInfo!FileName
' get the next file in the directory
sFileDir = Dir
End If
Loop
rsFileInfo.MoveFirst

''
'' COMPARE FILES TO RECORDS IN INVENTORY TABLE
''
Dim rsInv As ADODB.Recordset
Set rsInv = New ADODB.Recordset
rsInv.ActiveConnection = CurrentProject.Connection
' open recordset of inventory records that do not already show the image is available
rsInv.Open "SELECT * FROM Inventory WHERE (((Inventory.ImageAvailable)=False));", , adOpenKeyset, adLockOptimistic

' loop thru recordset to find matches in rsFileInfo
Do Until rsInv.EOF
Do Until rsFileInfo.EOF
' this next test assumes you have taken out the ".jpg" from the filename
' if not, then create a variable to hold the string combo of ProdID + .jpg
If rsInv!ProdID = rsFileInfo!FileName Then
rsInv!ImageAvailable = True
rsInv!ImageName = rsFileInfo!FileName
End If
rsFileInfo.MoveNext
Loop
rsFileInfo.MoveFirst
rsInv.MoveNext
Loop
rsFileInfo.Close
rsInv.Close
Set rsFileInfo = Nothing
Set rsInv = Nothing

I would like to make image files available from access in order to create a
"merged" cataloge. I have my report and query completed and it works great.
Now all I need to do is update my table with the path and file name to the
images. I am working with a single table [Inventory] with:

[Inventory].[ProdID] as the key, [Inventory].[PathtoImage],
[Inventory].[ImageName] and [Inventory].[ImageAvailable] (there are other
fields that do not affect what I need here. My PathtoImage =
\\graphics01\images the ImageName = [Inventory].[ProdID].jpg
[ImageAvailble] is a check box that defaults to false.

We have a few hundred images available and we are adding more each day, we
have over 20,000 products. I am hoping that there is a way to create an
update query with some code that will scour \\graphics01\images and when it
finds an image for a record have it set ImageAvailble to True

I know very very little about using VB in queries but I am quite sure that
this can be done. Any suggestions would be appreciated!

M

 

[Maureen]

Diana,

Thanks for your post - I know very little about modules and code but I knew
this was possible. I have reviewed your post and understand the logic, the
file names are the same as the ProdID and I am not storing the image name.
I pasted your code into a new module in access made a few tweaks and ran
it - WORKED PERFECT FIRST TIME - VERY COOL.

I realize that it is possible for images to be moved, renamed or delted... I
am going to to start by setting all ImageAvailable to false before doing the
update to stay accurate. I have setup an autoexec macro this macro calls an
update query to reset all to false then calls your code to check and update.
I would guess that there is an easy addition to your code that woudl look
after the reset first... I will see what I can do on my own. If you have an
opportunity to post how you would handle it I'll have a look later on.

Many thanks for your time and consideration providing the direction I
needed!
M

Maureen,

I'm not exactly sure I understand what you want to do but as I understand

it, you just want to update the ImageAvailable field to TRUE if there is an
image in the images folder that matches the product ID. Is that correct?
Are you storing the image name as well? If it is the same as the prodID
(plus the .jpg) there really isn't a need to store is it there? You just
need to know if it's available.

Either way, what you need is a loop that will iterate through the files in

the images folder and match it against the product ID's.

First what I would do is create a recordset of the filenames in the images folder.
When you add the filenames, I would "cut off" the extension of the

filename so you just have to worry about the actual name.

Second, using your inventory table, loop through each of the records one

by one and look for a matching filename in the first recordset. When a
match is found, you update Available = True. If you are also needing the
store the image name, do it at the same time.

Try this....

''
'' GET FILE NAMES FROM GRAPHICS FOLDER
''
Dim sFileDir As String
Dim rsFileInfo As ADODB.Recordset
Set rsFileInfo = New ADODB.Recordset

' create the filename field -- this is a string data type, length 255
rsFileInfo.Fields.Append "FileName", adBSTR, 255

' open the recordset
rsFileInfpen

' get the files in the correct directory
sFileDir = Dir("C:\graphics01\images\*.jpg") ' change the path as necessary

Do While sFileDir <> ""
If sFileDir <> "." And sFileDir <> ".." Then
rsFileInfo.AddNew
rsFileInfo!FileName = sFileDir
' alternatively, use the next line instead to get the file name
' minus the last four characters ".jpg"
' rsFileInfo!filename = Left(sFileDir, Len(sFileDir) - 4)
rsFileInfo.Update
Debug.Print rsFileInfo!FileName
' get the next file in the directory
sFileDir = Dir
End If
Loop
rsFileInfo.MoveFirst

''
'' COMPARE FILES TO RECORDS IN INVENTORY TABLE
''
Dim rsInv As ADODB.Recordset
Set rsInv = New ADODB.Recordset
rsInv.ActiveConnection = CurrentProject.Connection
' open recordset of inventory records that do not already show the image is available
rsInv.Open "SELECT * FROM Inventory WHERE

(((Inventory.ImageAvailable)=False));", , adOpenKeyset, adLockOptimistic

' loop thru recordset to find matches in rsFileInfo
Do Until rsInv.EOF
Do Until rsFileInfo.EOF
' this next test assumes you have taken out the ".jpg" from the filename
' if not, then create a variable to hold the string combo of ProdID + .jpg
If rsInv!ProdID = rsFileInfo!FileName Then
rsInv!ImageAvailable = True
rsInv!ImageName = rsFileInfo!FileName
End If
rsFileInfo.MoveNext
Loop
rsFileInfo.MoveFirst
rsInv.MoveNext
Loop
rsFileInfo.Close
rsInv.Close
Set rsFileInfo = Nothing
Set rsInv = Nothing

I would like to make image files available from access in order to create a
"merged" cataloge. I have my report and query completed and it works great.
Now all I need to do is update my table with the path and file name to the
images. I am working with a single table [Inventory] with:

[Inventory].[ProdID] as the key, [Inventory].[PathtoImage],
[Inventory].[ImageName] and [Inventory].[ImageAvailable] (there are other
fields that do not affect what I need here. My PathtoImage =
\\graphics01\images the ImageName = [Inventory].[ProdID].jpg
[ImageAvailble] is a check box that defaults to false.

We have a few hundred images available and we are adding more each day, we
have over 20,000 products. I am hoping that there is a way to create an
update query with some code that will scour \\graphics01\images and when it
finds an image for a record have it set ImageAvailble to True

I know very very little about using VB in queries but I am quite sure that
this can be done. Any suggestions would be appreciated!

M