Query to capture path/filename and update or add records

[src2]

I hope someone can help me with this as my knowledge of access is very limited.

I want to write a query that will prompt the user to select a folder (navigation box would be great) and execute a query that will create a record in a temporary table for every file found. Each record will have the fields recordID, filename, and filepath. The recordID will be the filename without the extension.

After the temporary table is created add any new records to an existing table and update the filename and filepath of any records that already exist.


Thanks a lot for any help you can give.

 

[JerryKlmns]

Application.FileSearch could get you all files of any/specific
type in a folder.

Scripting.FileSystemObject could give you the path, filename and extension of any file. To get only the filename without extension takes a little work to do: remove the length of the extension characters from the string holding the filename!

Then you could append records to the table you want using a recordset object using the values returned form Scripting.FileSystemObject

In alternative of Application.FileSearch, would be to use the Files collection of the Scripting.FileSystemObject after you grab the folder you want and loop throu that.

The choice is yours! Give it a try and post back for extra help on ... anything!

 

[JerryKlmns]

A thought:
Is there going to exist files like "c:\abc\qwerty.xls" and "c:\abc\qwerty.csv" and "c:\cba\qwerty.csv"? In that case your recordID definition should not be used as primary key in any table!

 

[src2]

Thanks for the tip about the record id. It shouldn't be a problem as the folders should only contain jpg files but I might ought to filter out anything except jpg's. As for using Application.FileSearch and scripting.FileSystemObject I don't know how to do this. Could you be more specific?

Thanks

 

[JerryKlmns]

src2,

It is gonna be VBA. Are you interested?
Create a new module, on menu Tools --> References scroll down and select Microsoft Scripting Runtime. Press F1 and search for a]Scripting FileSystemObject and b]Application FileSearch.

And the forum705 is for Access VBA!