Solved

Date modified info for all files in a directory-MS Access VBA

Posted on 2012-04-06
7
327 Views
Last Modified: 2012-04-11
This is kind of an add on question related to a question that I posted a while back (See Link).
 
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27239606.html 

I was faced with a situation where I needed to copy the Date Modified information from files and folders located in a specified directory, to a database table.  boag2000 provided a great example that helpd solve the issue I was dealing with.  Now, I would like to know if the code can be expanded upon in order to copy the Date Modified information from all files located in a specific directory, including subfolders, to a database table.

Any input is appreciated.
0
Comment
Question by:giswest
  • 3
  • 3
7 Comments
 
LVL 40

Expert Comment

by:als315
ID: 37818387
You can create table with file name  and Date Modified fields (let table will be tblFiles, fields: FName and FMod), open it and add records in loop:
Dim rst as RecordSet
Set rst = CurrentDB.OpenRecordset ("tblFiles")
.....
For Each fil In fldr.Files
        Debug.Print fil.Path, fil.DateCreated
        rst.AddNew
        rst!Fname = fil.Path
        rst!Fmod = fil.DateCreated
        rst.Update
 Next
........
rst.close

Open in new window

0
 

Author Comment

by:giswest
ID: 37823642
als315,

In your example, don't I need to define the directory where the folders reside?  I defined the fldr variable (SEE EXAMPLE BELOW), but I recieve a Compile Error: Invalid Qualifier.  Based on the error, I don't that it likes the way I defined the variable.

Example:

Dim fldr as String

fldr = "C:\SomeFolder\"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37824229
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 40

Expert Comment

by:als315
ID: 37826414
Check this combination of two solutions
Access--EEQ27665707ListFiles.mdb
0
 

Author Comment

by:giswest
ID: 37833443
als315,

After looking at your sample database, I modified my code to the following:

Dim rst As Recordset
Dim attachmentPathRemote As String
Dim sourceFolder As Folder
Dim sourceSubFolder As Folder
Dim fileItem As File
Dim fso As Object

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Attachment_File_List_Remote"
DoCmd.SetWarnings True

attachmentPathRemote = Form_AttachmentsBeta.DatabaseLocationLoad.Value & "Attachments\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set rst = CurrentDb.OpenRecordset("Attachment_File_List_Remote")

    Set sourceFolder = fso.GetFolder(attachmentPathRemote)
    On Error Resume Next
   
    For Each sourceSubFolder In sourceFolder.SubFolders
        For Each fileItem In sourceSubFolder.Files
       
            rst.AddNew
            rst!File_Name = fileItem.Name
            'rst!Folder_Name = fileItem.ParentFolder.Name
            rst.Update
         
        Next
    Next
   
    Set fileItem = Nothing
    Set sourceFolder = Nothing
    Set sourceSubFolder = Nothing
    Set fso = Nothing
rst.Close
Set rst = Nothing

This produces the result I'm looking for, which is to copy the name and date modified of all files contained the directory subfolders into a specified table.

However, I would also like to copy the name of the Parent Folder (Subfolder) that contains the files.  I've tried adding the following line:

rst!Folder_Name = fileItem.ParentFolder.Name

However, this only returns a single record for each subfolder that contains a file.

For example, my current directory contains 8 folders, with only 5 of the 8 folders containing files.  Without adding the "rst!Folder_Name = fileItem.ParentFolder.Name", the names of all files (13 total) contained in the directory folders are copied to the designated table.  With the "rst!Folder_Name = fileItem.ParentFolder.Name", only the names of the folders containing a file (5 folders) and the name of the first file listed in the folders are copied to the designated table.

Any ideas/work arounds regarding this issue?
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 37833898
0
 

Author Closing Comment

by:giswest
ID: 37834716
als315,

Your sample worked perfectly when I ran it using your table (tblFileList) as the destination table.  When I defined my table (Attachment_File_List_Remote) as the destination table, I ran into the same issue that I previously described, where only the name and date modified information of the folders containing a file were copied.  I copied your table to my database, renamed it (Attachment_File_List_Remote), and re-ran the code.  Everything worked fine.  I re-ran the code I posted earlier, and it also worked fine.  There must have been an issue with my original table, or I must have had some sort of filter on it, which caused the odd behaviour.  Anyhow, I really appreciate the time and you put into solving this issue.

Have a good day.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question