Scanning the local file system for specific file types and storing the paths for easy lookup.

[LivMic]

So I've been assigned a project at work and I think Access might be the best solution but thought I would would ask some experts as to how to potentially best get this solved.

The requirement is to scan the local file system for 900,000 images and store their file paths somewhere for fast and easy lookup. I'm thinking that using an Access database would be the easiest way to achieve this and then to use an an excel spreadsheet as a front end to search through these records to display the specific ones. Also it is necessary to run daily or weekly scans of the same directories and only add any new file paths to the database. Also, I'm not sure what tool can be used to scan the required file directories and then populate the database.


Does anyone have any ideas or suggestions on how to tackle this?


Thanks in advance

[Micron]

Forget Excel altogether. Your title suggests storing images in db - short answer = don't! Save the path in a table. Can use image control on form to see picture as well. Could even use that as a thumbnail you use to open the pic in any particular app, such as Photoshop.
Quite possible to scan folders, compare existing to found and save only new paths to db table but will require more code and path by path examination. Even easier to simply save all paths then delete duplicate paths, but not as efficient. Best of both could be a staging table for files found then a query to join both tables and append only what is different - less code I'd say. Access can do everything I see mentioned in your post.

An example would be a form that shows details about a stock item and an image control to load the image of the item. Move to the next record and picture changes. The pics stay in a network or local folder. If you attempt to put the images in a table, you may not get them all in before exceeding the db file size limit

[Welshgasman]

Look at the Folder Image Viewer at https://isladogs.co.uk/example-databases/index.html

That should give you a head start.

[orange]

There was a similar post/thread but also included image specific software several years ago. The dialog in the thread may offer some insight. 900,000 is a lot of images.
I agree with the others---store file system path of image and name in Access.

[LivMic]

Forget Excel altogether. Your title suggests storing images in db - short answer = don't! Save the path in a table. Can use image control on form to see picture as well. Could even use that as a thumbnail you use to open the pic in any particular app, such as Photoshop.
Quite possible to scan folders, compare existing to found and save only new paths to db table but will require more code and path by path examination. Even easier to simply save all paths then delete duplicate paths, but not as efficient. Best of both could be a staging table for files found then a query to join both tables and append only what is different - less code I'd say. Access can do everything I see mentioned in your post.

An example would be a form that shows details about a stock item and an image control to load the image of the item. Move to the next record and picture changes. The pics stay in a network or local folder. If you attempt to put the images in a table, you may not get them all in before exceeding the db file size limit

Thank you for your replies and suggestions. Sorry if I wasn't clear, I did indeed mean that I wanted to scan several directories and find the path to every image file found in every subdirectory and then to store them in the DB as file paths. OK so the more detailed scenario is that they are part numbers in various image formats for e.g part00011222333.jpg, part00011222333.SVG, part00011222333.DWG, part00011222333.PDF etc. So what I need the front end to do is to show the file path as a link to each file associated with the search string for that specific part number. So when the link is clicked it opens that respective file.

Even easier to simply save all paths then delete duplicate paths, but not as efficient. Best of both could be a staging table for files found then a query to join both tables and append only what is different - less code I'd say. Access can do everything I see mentioned in your post.

This sounds like the easiest implementation. Please forgive my ignorance as I am not a programmer but really want to give this a go. So all of this can be done in backend using VBA and JetSQL? So scanning the file system for specific filetypes and can all be coded in VBA?

Would it be at all viable or recommended, to use a search utility such as https://www.nirsoft.net/utils/search_my_files.html, to perform the scan and write the results to a .CSV file which can then be imported into the database? The problem I can see with this is that the process is not all contained within the one application. Ideally I would like a pushbutton solution that I can press once a day or once a week that can scan the directories for any new images and files added and to update the database accordingly.

Thanks in advance

[pbaldy]

Post 5 was moderated, I'm posting to trigger email notifications.

[LivMic]

Thank you mate.

[moke123]

So scanning the file system for specific filetypes and can all be coded in VBA?

Look into FileSystemObject.

You'll need to write a recursive procedure to drill down through the directories. FSO exposes a lot of properties so you can get the file paths, file names, extensions, base names, etc.

Here's an example

[orange]

You can use a Dir command to get all files of a certain extension and put these into a txt file.
You repeat for different file extensions and append to the txt file.

My example to recurse alldirectories/subdirectories from C:\users\JP and down and identify *.jpg files with path

C:\Users\JP>  dir *.jpg /s/b >c:\users\jp\documents\alljpgs.txt

Here is a sample from the alljpgs.txt produced.

C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\crypto-what-banner.jpg
C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\protecty ourself-banner.jpg
C:\Users\JP\AppData\Local\Google\Chrome\User Data\Default\Extensions\fheoggkfdfchfphceeifdbepao oicaho\8.1.0.2126_0\images\crypto\banners\server-image-banner.jpg
C:\Users\JP\AppData\Local\Microsoft\GraphicsCache\ 1\CloudGraphicsResources\Graphics\inkeffectbronze. jpg
C:\Users\JP\AppData\Local\Microsoft\GraphicsCache\ 1\CloudGraphicsResources\Graphics\inkeffectgalaxy. jpg
C:\Users\JP\AppData\Local\Microsoft\GraphicsCache\ 1\CloudGraphicsResources\Graphics\inkeffectgold.jp g
C:\Users\JP\AppData\Local\Microsoft\GraphicsCache\ 1\CloudGraphicsResources\Graphics\inkeffectlava.jp g
C:\Users\JP\AppData\Local\Microsoft\GraphicsCache\ 1\CloudGraphicsResources\Graphics\inkeffectocean.j pg
C:\Users\JP\AppData\Local\Microsoft\GraphicsCache\ 1\CloudGraphicsResources\Graphics\inkeffectrainbow glitter.jpg
C:\Users\JP\AppData\Local\Microsoft\GraphicsCache\ 1\CloudGraphicsResources\Graphics\inkeffectrosegol d.jpg
C:\Users\JP\AppData\Local\Microsoft\GraphicsCache\ 1\CloudGraphicsResources\Graphics\inkeffectsilver. jpg
C:\Users\JP\AppData\Local\Microsoft\Office\Solutio nPackages\33147523bd5f4ce49012f6461c11dce\PackageR esources\OfflineFiles\Aerial_V2_960_e4f16d9ab2de9e 07fc69b471b386ba4b.jpg
C:\Users\JP\AppData\Local\Microsoft\Office\Solutio nPackages\33147523bd5f4ce49012f6461c11dce\PackageR esources\OfflineFiles\Alloy_960_a913b5f968269f69c6 e8532d3e282331.jpg
C:\Users\JP\AppData\Local\Microsoft\Office\Solutio nPackages\33147523bd5f4ce49012f6461c11dce\PackageR esources\OfflineFiles\Aurora_960_e5e180d0afc4c9938 bc4f43c29faad2f.jpg
C:\Users\JP\AppData\Local\Microsoft\Office\Solutio nPackages\33147523bd5f4ce49012f6461c11dce\PackageR esources\OfflineFiles\Bokeh_V2_960_4a9e6622fd62a84 a258eb269895a094c.jpg

You can process the txt file with Access.
Good luck.

[Micron]

@LivMic: since you quoted me you've had a couple of responses. It appears that you might need a recursive function to delve into subfolders of a directory as suggested, so do explore the suggestions given. It seems to me that dwg files are native to AutoCad but I think that won't be an issue as long as Windows has the file type registered on the user pc (and the user has the app required, of course).

[Gicu]

@ LivMic: sorry for the late arrival but I think you are approaching this a bit on the wrong side. If you want to see all the files associated with a part number you should do it live once you know that number; surely there won't be
anywhere near 900,000 files. Using patterns in file searches should be very fast and the advantage is that you know the files are actually there when you need them. Your original approach would waste processing time and memory space and most of all had the potential to be inaccurate when some moves or deletes a file after your scan.

Build a search form for the users to enter a part number then use that to limit the files returned.

Cheers,

[ssanfu]

I also had questions about deleting/moving files.


@ LivMic: What is your folder structure? Is there a main (top) folder with the part files in sub folders? Or are the files spread out willy-nilly (no offense meant to Willy or Nilly) over 1 or more drives?

What is the plan if someone moves a file/folder to a different location? That would create duplicate records in the dB, and one record would be wrong.

Interesting project......

[moke123]

e.g part00011222333.jpg, part00011222333.SVG, part00011222333.DWG, part00011222333.PDF

Is it safe to say that all of your file names will follow this pattern? It appears to be the same part number with different file extensions.
You'll notice in my example there is a field txtBaseName which is populated by an fso method GetBaseName(). GetBaseName() gets the file name without the extension.
You could use that to filter your records using a "Select Distinct txtBaseName . . " query.

FSO also has a GetExtensionName() method which can easily be used to filter out file types

Here's some code changes to the above example which filters out unwanted file types and duplicates.

Sub RecFso(FolPath As String)

    Dim fso As New FileSystemObject

    Dim fol As Folder
    Dim fil As File
    Dim sfol As Folder

    Set fol = fso.GetFolder(FolPath)

    For Each fil In fol.Files

        Select Case fso.GetExtensionName(fil.Path)

        Case "jpg", "png", "pdf", "svg", "dwg"    'Include the file types to include on this line

            InsertToTable fil.Path, fil.Name, fso.GetExtensionName(fil.Path), fil.DateCreated, fil.DateLastModified, fso.GetBaseName(fil.Path)

        Case Else

        End Select

        For Each sfol In fol.SubFolders
            RecFso sfol.Path
        Next

    Next

End Sub

Sub InsertToTable(FPath As String, Fname As String, FExt As String, dteC As Date, dteM As Date, BN As String)

    If DCount("*", "tblFiles", "FPath = """ & FPath & """") > 0 Then Exit Sub      'this will skip any file path already in table

    Const Sql_Insert As String = _
          "Insert into tblFiles" & _
          "(FPath,FName,FExt,dteCreated,dteModified,txtBaseName)" & _
        " Values(p0,p1,p2,p3,p4,p5)"

    With CurrentDb.CreateQueryDef("", Sql_Insert)
        .Parameters(0) = FPath
        .Parameters(1) = Fname
        .Parameters(2) = FExt
        .Parameters(3) = dteC
        .Parameters(4) = dteM
        .Parameters(5) = BN
        .Execute dbFailOnError
        .Close
    End With


End Sub

[orange]

@moke123,

Can you post a database with the code revisions? I tried the original database(post #8) after converting the Declares to work with 64 bit but it gets into some loop "Access Not responding". I tried the revision material by itself, but never go to do the insert. Also had to change
Dim fil As File to Dim fil As Object to get it to run. Had lots of Permission errors.

I like your folder/subfolder recurse code and would like to get a version to work. Perhaps I'm missing something basic??

[moke123]

@Orange It's by no means fast. Takes a couple minutes to run. I'm sure it could be tweaked but it's meant to only be a quick example of how the OP could use FSO.

The only requirement is a reference to FSO. No idea where the permissions errors are coming from.

I haven't gotten any errors but I only ran it against about 100 or so small directories with some subfolders a few layers deep.