Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Wed May 21 10:17:51 CDT 2003
Roz:
What would happen if you moved all of the databases into a test directory.
Would the code retrieve all of them? I'm wondering if its the directory
structure that's confusing the code or something about the dbs that are not
being retrieved.
Rocky
----- Original Message -----
From: "Roz Clarke" <roz.clarke at donnslaw.co.uk>
To: <accessd at databaseadvisors.com>
Sent: Wednesday, May 21, 2003 8:02 AM
Subject: RE: [AccessD] Grab Filenames
> I don't think you silly whatsoever.
>
> The other 128 databases are in directories and subdirectories which sit
> alongside those which are successfully being searched (all in one root
> directory with no shares or other malarkey involved) There are no
different
> permissions on any of the folders or databases being ignored versus those
> being returned. The user(s) testing the code have full administrator
> permissions.
>
> -----Original Message-----
> From: John Ruff [mailto:papparuff at attbi.com]
> Sent: 21 May 2003 15:47
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Grab Filenames
>
>
> Roz,
>
> Please don't think me silly, but...are the other 128 databases in
> directories that were not specified in the .LookIn property. Does the
> person running the code have access to the directories that the other
> databases are located?
>
>
>
> John V. Ruff - The Eternal Optimist :-)
> Always Looking For Contract Opportunities
>
> www.noclassroom.com
> Live software training
> Right over the Internet
>
> 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: Wednesday, May 21, 2003 7:24 AM
> To: 'accessd at databaseadvisors.com'
> Subject: RE: [AccessD] Grab Filenames
>
>
> John
>
> Thanks for this, Karen reports that it works beautifully in terms of
> bringing back the additional info. It still only returns 152 of our 280
> databases though! Gah!
>
> Roz
>
> -----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
> _______________________________________________
> 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
>