How do I programatically get file meta data including owner, last file access, size etc.

Posted on 2009-05-01
Last Modified: 2013-11-27
I am trying to import file Meta data into an application, which is at this stage being designed in AccessVBA. The spec of the data I require for the files it finds in a search include name, size, owner, last access.

I can easily search for files using VBA, that is not a problem, but I cannot get back the granularity of information about the files that I need. Using VBA to search large drives, folders and sub folders is also quite slow.

Is there a way of calling a windows procedure that will run return the data to the calling proc.

I would be grateful if anyone can pointg me in the right direction as I cannot find a way of getting the file data  I need.

I am working with file data on a Windows Servcer 2003 standard server and Access 2003 VBA.  

Question by:DavidHannen
    LVL 65

    Expert Comment

    If you got your search going, how about inserting into a table as you go along?

    eg create a table called tblFileInfo with the relevant fields
    fname (filename)
    crtdate (created date)
    upddate (updated date)
    upduser (updated user)

    as you find each file, insert a record

    then caller simply has to query this table

    Author Comment

    Thanks Rockiroads:

    But I don't have a problem dealing with data once it reaches the application - my issues are about getting the meta data in the first plsce and the fastest way of doing it. (probably a call outside VBA)

    Whilst I can get file names from a search using VBA directly I cannot get owner and last opened date.

    LVL 65

    Accepted Solution

    Ive knocked up an example, but I cant get the owner name, however I have a link which perhaps you can use.
    Revise the table as you want filename, size, owner and last access
    so fieldnames I guess could be FName, Size, Owner, LastAccess

    Public Function SaveFileInfo()

        Dim rs As DAO.Recordset
        Dim i As Long
        Dim fs As object
        Dim f As object

        Set rs = CurrentDb.OpenRecordset("select * from tblFileInfo")
        Set fs = createobject("Scripting.FileSystemObject")

        With Application.FileSearch
            .LookIn = "C:\mystartdir\"
            .SearchSubFolders = True
            .FileName = "*.*"
            .Execute                                            '<--- THIS IS THE SLOW BIT
            For i = 1 To .FoundFiles.count
                Set f = fs.GetFile(.FoundFiles(i))
    'Save filename
                rs!FName = .FoundFiles(i)
    'Save last last access date
                rs!LastAccess = f.DateLastAccessed
    'Save size
                rs!Size = f.Size

    'See this link to get owner
            Next i
        End With

        set rs=nothing
        set fs=nothing
    End Function

    Author Closing Comment

    Thanks, I  have all the file system object stuff - but the link to the scripting Guy article will be invaluable. Thanks

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now