[AccessD] Grab Filenames

Roz Clarke roz.clarke at donnslaw.co.uk
Wed May 21 09:21:37 CDT 2003


One of my colleagues is trying this out for me, will let you know how she
gets on!

-----Original Message-----
From: John Ruff [mailto:papparuff at attbi.com] 
Sent: 20 May 2003 18:11
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Grab Filenames


Here's code that I tested and it seems to be working.  I have a table called
tbl_Files in which I want to place the name of the database found, the
directory it is located, it's size, when it was created, when it was last
modified, and when it was last accessed.

Private Sub cmdFileObject_Click()
' Provides the user Path, File Name,
' and File Size
' Insure there is a reference to the
' Microsoft Scripting Runtime
' Access 2000/2002
    On Error GoTo cmdFileObject_ERR

    Dim rst As ADODB.Command
    Dim fso As FileSystemObject
    Dim f1 As File
    Dim strFilePath As String
    Dim strFileName As String
    Dim strFileSize As String
    Dim datDateCreated As Date
    Dim datDateModified As Date
    Dim datDateAccessed As Date
    Dim I As Double

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set rst = New ADODB.Command

    DoCmd.Hourglass True
    ' Create a recordset
    With rst
        .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdText
        ' Create the SQL to delete any records in the table tbl_Files
        .CommandText = "DELETE * FROM tbl_Files"
        ' Delete the records
        .Execute
    End With

    With Application.FileSearch
        .NewSearch

	  ' There are over 50 subfolders where the databases reside
	  ' in this My work directory.
        .LookIn = "D:\Documents and Settings\John\My Documents\My Work"

        ' I want to search for all files that have .mda, .mdb, .mde
        ' and .ldb in their name.  Place a semicolon between each
        ' file type so that the FilesSearch will search all that
        ' you have specified
        .FileName = "*.mda; *.mdb; *.mde; *.ldb"

        .MatchTextExactly = False

	  ' FileType not required if you are using .FileName
	  ' with an extension
 '       .FileType = msoFileTypeAllFiles

	  ' Search all subfolders of My Work
        .SearchSubFolders = True

        If .Execute() > 0 Then
            For I = 1 To .FoundFiles.Count

                Set f1 = fso.GetFile(.FoundFiles(I))

                strFilePath = f1.ParentFolder
                strFileName = f1.Name
                strFileSize = f1.Size / 1000 ' Convert to KB
                datDateCreated = f1.DateCreated
                datDateModified = f1.DateLastModified
                datDateAccessed = f1.DateLastAccessed

                DoEvents

                ' SQL statement to insert the data into the tbl_Files table
                rst.CommandText = "INSERT INTO tbl_Files " & _
                        "(FilePath, FileName, FileSize, DateCreated, " & _
                        "DateLastModified, DateLastAccessed) " & _
                        "SELECT '" & _
                        strFilePath & "', '" & _
                        strFileName & " ', " & _
                        strFileSize & ", #" & _
                        datDateCreated & "#, #" & _
                        datDateModified & "#, #" & _
                        datDateAccessed & "#"
                ' Insert the record
                rst.Execute
           Next I
            MsgBox "Done. " & .FoundFiles.Count & " Records were added"
        Else
            MsgBox "No Files Found"
        End If
    End With


cmdFileObject_EXIT:

    On Error Resume Next
    DoCmd.Hourglass False
    Set rst = Nothing
    Exit Sub

cmdFileObject_ERR:

    If Err.Number = 70 Then ' Permission denied
        Resume Next
    End If
    MsgBox Error$

End Sub

John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

Home: 253.588.2139
Cell: 253.307/2947
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
                and your plans will succeed." Proverbs 16:3



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Roz Clarke
Sent: Tuesday, May 20, 2003 8:56 AM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Grab Filenames


Well I'm running Office XP on WXP w/ Service Pack 1.

I can't think of any specific settings which would lead to the exclusion of
the particular databases I'm missing :(

Roz

-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: 20 May 2003 16:48
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Grab Filenames


That's interesting, Roz.  I just pulled your code out, changed the path to
point to a folder on my machine with mdbs in it, and got a zero for the
FoundFiles count.  And I had the same problem with Rocky's sample database.
It has to be a setting that is causing the failures some of us are
experiencing.  Now, if we could just figure out what it is ... :o{

Rocky, any suggestions?

Charlotte Foust

-----Original Message-----
From: Roz Clarke [mailto:roz.clarke at donnslaw.co.uk]
Sent: Tuesday, May 20, 2003 7:27 AM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Grab Filenames



Hmm. I ran a variation of Charlotte's code (informed by Rocky's, but
Charlotte's was closer to what I think I want) that looks like this:

  Dim varItem As Variant
  With Application.FileSearch
    .NewSearch
    .SearchSubFolders = True
    .FileName = "*.mdb"
    .LookIn = "G:\"
    .Execute
    'Debug.Print .FoundFiles.Count
    DoCmd.SetWarnings False
    For Each varItem In .FoundFiles
        strSQL = "INSERT INTO tblFiles (FileInfo) VALUES ( '" & varItem & "'
)"
        'Debug.Print strSQL
        DoCmd.RunSQL strSQL
    Next varItem
  End With

This gets me the filepath, with name, though not the file size or any date
info. But that's fine, I can live with that.

What's baffling me is that the above drive has 280 .mdb files on it (yes, I
know that's ridiculous, which is the point of the exercise) but the
.FoundFiles.count only returns 152. It doesn't seem to have looked in any
sub folders beginning with 'R'. Is this the sort of whacky behaviour you
guys have experienced?!

Roz

-----Original Message-----
From: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com]
Sent: 20 May 2003 15:21
To: Roz Clarke
Subject: Re: Your last E-Mail to donnslaw.co.uk was corrupt of unreadable.
Find Files db


Was curious about the code.  Standing by...

Rocky

----- Original Message -----
From: "Roz Clarke" <roz.clarke at donnslaw.co.uk>
To: "'Rocky Smolin - Beach Access Software'" <bchacc at san.rr.com>
Sent: Tuesday, May 20, 2003 6:32 AM
Subject: RE: Your last E-Mail to donnslaw.co.uk was corrupt of unreadable.
Find Files db


> The email or the code?
>
> I got the zip file but I haven't had a chance to try the code out yet
> - spent all day so far on a really nasty bit of data conversion and 
> now have
a
> stack of support jobs to do. *sigh*
>
> I'll let you know!
>
> -----Original Message-----
> From: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com]
> Sent: 20 May 2003 14:20
> To: Roz Clarke
> Subject: Re: Your last E-Mail to donnslaw.co.uk was corrupt of 
> unreadable. Find Files db
>
>
> did it work?
>
> ----- Original Message -----
> From: "Roz Clarke" <roz.clarke at donnslaw.co.uk>
> To: "'Rocky Smolin - Beach Access Software'" <bchacc at san.rr.com>
> Sent: Tuesday, May 20, 2003 1:00 AM
> Subject: RE: Your last E-Mail to donnslaw.co.uk was corrupt of 
> unreadable. Find Files db
>
>
> > cheers
> >
> > -----Original Message-----
> > From: Rocky Smolin - Beach Access Software 
> > [mailto:bchacc at san.rr.com]
> > Sent: 19 May 2003 17:26
> > To: Roz Clarke
> > Subject: Re: Your last E-Mail to donnslaw.co.uk was corrupt of 
> > unreadable. Find Files db
> >
> >
> > Just sent it.  Note in the code that I'm only grabbing files with a 
> > .jpg extension.  But you can change that to whatever you want.
> >
> > Rocky
> >
> > ----- Original Message -----
> > From: "Roz Clarke" <roz.clarke at donnslaw.co.uk>
> > To: "'Rocky Smolin - Beach Access Software'" <bchacc at san.rr.com>
> > Sent: Monday, May 19, 2003 9:11 AM
> > Subject: RE: Your last E-Mail to donnslaw.co.uk was corrupt of
unreadable.
> > Find Files db
> >
> >
> > > I should get zip files OK... Will have to slap our notwork guys
> > >
> > >
> > > Try zora_db at yahoo.com
> > >
> > > -----Original Message-----
> > > From: Rocky Smolin - Beach Access Software 
> > > [mailto:bchacc at san.rr.com]
> > > Sent: 19 May 2003 17:10
> > > To: Roz Clarke
> > > Subject: Fw: Your last E-Mail to donnslaw.co.uk was corrupt of 
> > > unreadable. Find Files db
> > >
> > >
> > > Roz:
> > >
> > > Apparently I can't send either MDB or ZIP attachments.  :(
> > >
> > > Do you have another email address that might let these go through?
> > >
> > > Rocky
> > >
> > > ----- Original Message -----
> > > From: "Internet Manager Message Inspector" 
> > > <ted.walsh at donnslaw.co.uk>
> > > To: <bchacc at san.rr.com>
> > > Sent: Monday, May 19, 2003 9:08 AM
> > > Subject: Your last E-Mail to donnslaw.co.uk was corrupt of 
> > > unreadable.
> > Find
> > > Files db
> > >
> > >
> > > > The message you sent does not conform with our company 
> > > > electronic communications policy.
> > > >
> > > >
> > > >
> > > > Your message was entitled:  Find Files db
> > > > It was addressed to: roz.clarke at donnslaw.co.uk
> > > > and dated:  Mon, 19 May 2003 09:08:13 -0700
> > > > From IP: 66.75.160.17
> > > >
> > > > The message was redirected for later inspection.
> > > >
> > >
> > >
> >
> >
>
>

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list