Can I use an access database to pull files from a folder?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have an online storefront.
Our products are all in an access database, arranged by part number.

I also have about 30,000 images of products in a file folder.
I only want to select about 15,000 images of these 30,000 images.

How do I select out of my image folder (containing 30,000 images) only the
15,000 images I need (that actually match the 15,000 products in our access
product listing)?

All images and products are arranged via their " part number ".
My product listing is in (MS Access 2000) by part number.
My images are in their folder and named by part number.

It's like selecting only the black jelly beans in a jar of 100,000...ha.

Any suggestions?

Thanks in advance,
David
 
no. you cannot pull the file into access. this is a data
no-no anyway. causes db blote. usually the images are call
to a image control on a form in access by putting the
image's filepath in the image control's picture property.
to change images, you would programmicly change the images
control's picture property to a new file path.
In data bases that use images, the image's filepath is
usually stored in the table with the record.
in your post in excel general questions, you claimed that
your list was in excel. which ever, the advice remains.
put the image's filepath in the same record as the part
number. i know this is not what you want to hear.
no. you cannot select the files and pull them into excel
or access. you are stuck with filepaths.
sorry.
good luck.
 
I do agree with the previous post that it would not be a good idea at all to
bring images into the database. Instead you can just dynamically link to the
image on forms or reports. But, my understanding of your post was a little
different, I thought that you were asking if there is a way to delete the
15,000 or so images that you do not need.

If that is the case, it should be pretty easy given that the names match
your part numbers. In that case, you should be able to use the Dir()
function to loop through all images names, check to see if the part number
exists in a recordset, and delete it if it doesn't.

The code would look something like (air code):

Dim strFileName as String
Dim strFilePath as String
Dim strSQL as String
Dim db as DAO.Database
Dim rst as DAO.Recordset

strFilePath = "YourPath\"
strFileName = Dir(strFilePath & "*.jpg")

strSQL = "SELECT PartNo FROM YourTable"
Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL)

Do While strFileName <> ""
rst.FindFirst "PartNo = " & strFileName
If rst.NoMatch Then
Kill strFilePath & strFileName
Endif
strFileName = Dir()
Loop

rst.close
set rst = nothing
set db = nothing

Of course, you would have to add the actual field, table and path names, and
change the file extension type (if needed).

If you use this, I would recommend testing it on a test folder with a copy
of just a few files to see if it works properly.

HTH, Ted Allen
 
Back
Top