By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

Help me build a better kludge

P: n/a
DFS
I need to scan various network folders and capture all the filenames and
create dates.

================================================== =====
1st effort used the typical drive scan

cFile = Dir(sharedPath)
Do While cFile <""
db.Execute("INSERT INTO Table (FileName, blah blah)
cFile = Dir
Loop

But it's very slow when iterating 25,000 files across a slow corporate
network.

================================================== =====
2nd effort - shell out to DOS, run .bat to capture folder
contents in text file, import text file to Access

nwFolder = "dir """ & sharedPath & """ /t:w " & fileListing & ""
Open "C:\docPath.bat" For Output As #1
Print #1, nwFolder
Close #1
v = Shell("C:\docPath.bat", vbHide)
'wait 5 seconds
DoCmd.TransferText acImportFixed, "Import Spec", "tempTable", fileListing,
False

This is fast, but it's just plain ugly. And Shell runs asynchronously so
that's an issue to deal with (this runs in a loop so if I don't put pauses
after the Shell statement it soon errors out).

I don't think I'm interested in ShellWait. I want a simpler solution if
possible.

================================================== =====
3rd effort: suggestions from cdma?
Thanks

Feb 7 '07 #1
Share this Question
Share on Google+
12 Replies


P: n/a
DFS wrote:
3rd effort: suggestions from cdma?
I use the Application.filesearch method described in help. Here's a
proc I pulled out of an Oracle app of mine which populates a Jet table
in the user's front end with image files (the table name is
cImageTableName, a constant) - the "about" form randomly chooses
pictures to display when the splash form is loaded. Look for the help
subject on Application.filesearch. I don't know how well done the
following is as it's a recent creation of mine, but no complaints so
far. The strFileEnding is an image type extension. You'd probably want
to alter this so that there's no specification of a specific file name.

The construction below is laregly based on the help file information for
A2003. The "dblocal" used in dblocal.execute is a David Fenton function
for defining a database variable that you don't have to worry about closing.

Sub sSetUpImageInventorySearchFiles(strFileEnding)
'from access help for filesearch, foundfiles property
'error trapping handled in calling function
Dim i As Integer
Dim intSerial As Integer
Dim strS As String

'Set intSerial to the last number of an image found and then add one

intSerial = Nz(DMax("serial", cImageTableName), 0)

With Application.FileSearch
.LookIn = cImagesLocation
.SearchSubFolders = False
.FileName = "*" & strFileEnding
If .Execute() 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox .FoundFiles(i)
intSerial = intSerial + 1
strS = "insert into " & cImageTableName & " (Serial,
Location) "
strS = strS & vbCrLf & "Select " & intSerial & ", " &
Chr(34) & .FoundFiles(i) & Chr(34)
'Debug.Print strS
dbLocal.Execute strS, dbFailOnError
Next i
Else
'MsgBox "There were no files found."
End If
End With

End Sub

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Feb 7 '07 #2

P: n/a
What you are looking for that doesn't use Shell/Shellwait API's
(unmanaged code) would be in the managed code environment (VB.Net, C#)
if stepping out of Access is an option.

Managed code has the system.diagnostics.process object which can run
synchronously, asynchronously, any way you want. Managed code has
bundled up all the API's for you into this simple process object. The
alternative is to stay with the Shell/Shellwait API's - which does
pretty much the same thing except that the code is unmanaged (not
bundled up in a nice neat debugged package like in .Net) so you have to
manage the code yourself - that's the ugly part.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Feb 7 '07 #3

P: n/a
DFS
Thanks. I forgot to mention I also need the file's last modified date. I
don't see where that's available with the Application.FileSearch object (the
..LastModified property is part of the search, not the results)

Tim Marshall wrote:
DFS wrote:
>3rd effort: suggestions from cdma?

I use the Application.filesearch method described in help. Here's a
proc I pulled out of an Oracle app of mine which populates a Jet table
in the user's front end with image files (the table name is
cImageTableName, a constant) - the "about" form randomly chooses
pictures to display when the splash form is loaded. Look for the help
subject on Application.filesearch. I don't know how well done the
following is as it's a recent creation of mine, but no complaints so
far. The strFileEnding is an image type extension. You'd probably
want to alter this so that there's no specification of a specific file
name.
The construction below is laregly based on the help file information
for A2003. The "dblocal" used in dblocal.execute is a David Fenton
function for defining a database variable that you don't have to
worry about closing.
Sub sSetUpImageInventorySearchFiles(strFileEnding)
'from access help for filesearch, foundfiles property
'error trapping handled in calling function
Dim i As Integer
Dim intSerial As Integer
Dim strS As String

'Set intSerial to the last number of an image found and then add
one
intSerial = Nz(DMax("serial", cImageTableName), 0)

With Application.FileSearch
.LookIn = cImagesLocation
.SearchSubFolders = False
.FileName = "*" & strFileEnding
If .Execute() 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox .FoundFiles(i)
intSerial = intSerial + 1
strS = "insert into " & cImageTableName & " (Serial,
Location) "
strS = strS & vbCrLf & "Select " & intSerial & ", " &
Chr(34) & .FoundFiles(i) & Chr(34)
'Debug.Print strS
dbLocal.Execute strS, dbFailOnError
Next i
Else
'MsgBox "There were no files found."
End If
End With

End Sub

Feb 7 '07 #4

P: n/a
DFS
Rich P wrote:
What you are looking for that doesn't use Shell/Shellwait API's
(unmanaged code) would be in the managed code environment (VB.Net, C#)
if stepping out of Access is an option.
Probably not. I want to keep all the code in the single .mde file I
distribute to the client.
Managed code has the system.diagnostics.process object which can run
synchronously, asynchronously, any way you want. Managed code has
bundled up all the API's for you into this simple process object. The
alternative is to stay with the Shell/Shellwait API's - which does
pretty much the same thing except that the code is unmanaged (not
bundled up in a nice neat debugged package like in .Net) so you have
to manage the code yourself - that's the ugly part.

Rich

Thanks
Feb 7 '07 #5

P: n/a
While "many" people will often point out it is faster to use sql then a
reocrdset, that rule only applies when you don't have to repeat open the
table over and over.

In the time it takes to open, and setup a sql insert to, you likely can grab
about 25,000 records!!!

So, use a reocrdset....open it at the start...and use add.new...

You will speed things up by a bout a factor of 100 in your case...

so

dim rstData as dao.RecordSet
ser rstData = currentdb.OpenRecordSet("tblDirs")
>
cFile = Dir(sharedPath)
Do While cFile <""
rstData.AddNew
rstData!NDir = FileName
rstData!LastModDate = the data...
rstData.Update
cFile = Dir
Loop

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Feb 8 '07 #6

P: n/a
Bri
DFS wrote:
I need to scan various network folders and capture all the filenames and
create dates.

================================================== =====
1st effort used the typical drive scan

cFile = Dir(sharedPath)
Do While cFile <""
db.Execute("INSERT INTO Table (FileName, blah blah)
cFile = Dir
Loop

But it's very slow when iterating 25,000 files across a slow corporate
network.

================================================== =====
2nd effort - shell out to DOS, run .bat to capture folder
contents in text file, import text file to Access

nwFolder = "dir """ & sharedPath & """ /t:w " & fileListing & ""
Open "C:\docPath.bat" For Output As #1
Print #1, nwFolder
Close #1
v = Shell("C:\docPath.bat", vbHide)
'wait 5 seconds
DoCmd.TransferText acImportFixed, "Import Spec", "tempTable", fileListing,
False

This is fast, but it's just plain ugly. And Shell runs asynchronously so
that's an issue to deal with (this runs in a loop so if I don't put pauses
after the Shell statement it soon errors out).

I don't think I'm interested in ShellWait. I want a simpler solution if
possible.

================================================== =====
3rd effort: suggestions from cdma?
Thanks
If you wanted to look at the Wait for Shell option, check it out here:
http://www.mvps.org/access/api/api0004.htm

Or There are API's that can do the file finding very quickly. Here is a
sample code from the KPD-Team's API-Guide (http://www.allapi.net/).
Watch for line wrap:

'Create a form with a command button (command1), a list box (list1)
'and four text boxes (text1, text2, text3 and text4).
'Type in the first textbox a startingpath like c:\
'and in the second textbox you put a pattern like *.* or *.txt

Private Declare Function FindFirstFile Lib "kernel32" Alias
"FindFirstFileA" (ByVal lpFileName As String, lpFindFileData As
WIN32_FIND_DATA) As Long
Private Declare Function FindNextFile Lib "kernel32" Alias
"FindNextFileA" (ByVal hFindFile As Long, lpFindFileData As
WIN32_FIND_DATA) As Long
Private Declare Function GetFileAttributes Lib "kernel32" Alias
"GetFileAttributesA" (ByVal lpFileName As String) As Long
Private Declare Function FindClose Lib "kernel32" (ByVal hFindFile As
Long) As Long

Const MAX_PATH = 260
Const MAXDWORD = &HFFFF
Const INVALID_HANDLE_VALUE = -1
Const FILE_ATTRIBUTE_ARCHIVE = &H20
Const FILE_ATTRIBUTE_DIRECTORY = &H10
Const FILE_ATTRIBUTE_HIDDEN = &H2
Const FILE_ATTRIBUTE_NORMAL = &H80
Const FILE_ATTRIBUTE_READONLY = &H1
Const FILE_ATTRIBUTE_SYSTEM = &H4
Const FILE_ATTRIBUTE_TEMPORARY = &H100

Private Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type

Private Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As FILETIME
ftLastAccessTime As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type
Function StripNulls(OriginalStr As String) As String
If (InStr(OriginalStr, Chr(0)) 0) Then
OriginalStr = Left(OriginalStr, InStr(OriginalStr, Chr(0)) - 1)
End If
StripNulls = OriginalStr
End Function

Function FindFilesAPI(path As String, SearchStr As String, FileCount As
Integer, DirCount As Integer)
'KPD-Team 1999
'E-Mail: KP*****@Allapi.net

Dim FileName As String ' Walking filename variable...
Dim DirName As String ' SubDirectory Name
Dim dirNames() As String ' Buffer for directory name entries
Dim nDir As Integer ' Number of directories in this path
Dim i As Integer ' For-loop counter...
Dim hSearch As Long ' Search Handle
Dim WFD As WIN32_FIND_DATA
Dim Cont As Integer
If Right(path, 1) <"\" Then path = path & "\"
' Search for subdirectories.
nDir = 0
ReDim dirNames(nDir)
Cont = True
hSearch = FindFirstFile(path & "*", WFD)
If hSearch <INVALID_HANDLE_VALUE Then
Do While Cont
DirName = StripNulls(WFD.cFileName)
' Ignore the current and encompassing directories.
If (DirName <".") And (DirName <"..") Then
' Check for directory with bitwise comparison.
If GetFileAttributes(path & DirName) And
FILE_ATTRIBUTE_DIRECTORY Then
dirNames(nDir) = DirName
DirCount = DirCount + 1
nDir = nDir + 1
ReDim Preserve dirNames(nDir)
End If
End If
Cont = FindNextFile(hSearch, WFD) 'Get next subdirectory.
Loop
Cont = FindClose(hSearch)
End If
' Walk through this directory and sum file sizes.
hSearch = FindFirstFile(path & SearchStr, WFD)
Cont = True
If hSearch <INVALID_HANDLE_VALUE Then
While Cont
FileName = StripNulls(WFD.cFileName)
If (FileName <".") And (FileName <"..") Then
FindFilesAPI = FindFilesAPI + (WFD.nFileSizeHigh *
MAXDWORD) + WFD.nFileSizeLow
FileCount = FileCount + 1
List1.AddItem path & FileName
End If
Cont = FindNextFile(hSearch, WFD) ' Get next file
Wend
Cont = FindClose(hSearch)
End If
' If there are sub-directories...
If nDir 0 Then
' Recursively walk into them...
For i = 0 To nDir - 1
FindFilesAPI = FindFilesAPI + FindFilesAPI(path &
dirNames(i) & "\", SearchStr, FileCount, DirCount)
Next i
End If
End Function
Sub Command1_Click()
Dim SearchPath As String, FindStr As String
Dim FileSize As Long
Dim NumFiles As Integer, NumDirs As Integer
Screen.MousePointer = vbHourglass
List1.Clear
SearchPath = Text1.Text
FindStr = Text2.Text
FileSize = FindFilesAPI(SearchPath, FindStr, NumFiles, NumDirs)
Text3.Text = NumFiles & " Files found in " & NumDirs + 1 & "
Directories"
Text4.Text = "Size of files found under " & SearchPath & " = " &
Format(FileSize, "#,###,###,##0") & " Bytes"
Screen.MousePointer = vbDefault
End Sub
--
Bri

Feb 8 '07 #7

P: n/a
On Feb 8, 12:35 pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
While "many" people will often point out it is faster to use sql then a
reocrdset, that rule only applies when you don't have to repeat open the
table over and over.

In the time it takes to open, and setup a sql insert to, you likely can grab
about 25,000 records!!!
Could you explain how one opens "a sql insert"?

Feb 8 '07 #8

P: n/a
DFS
Albert D. Kallal wrote:
While "many" people will often point out it is faster to use sql then
a reocrdset, that rule only applies when you don't have to repeat
open the table over and over.

In the time it takes to open, and setup a sql insert to, you likely
can grab about 25,000 records!!!

So, use a reocrdset....open it at the start...and use add.new...

You will speed things up by a bout a factor of 100 in your case...

I rarely use .AddNew, but I'll give it a try tomorrow. Nothing wrong with a
100x speed increase...
I think the slowness is due to walking the large folders, but I'll test all
combinations:

1) walk folders with no SQL/insert or .AddNew
2) walk folders with SQL/insert
3) walk folders with .AddNew


so

dim rstData as dao.RecordSet
ser rstData = currentdb.OpenRecordSet("tblDirs")
>>
cFile = Dir(sharedPath)
Do While cFile <""
rstData.AddNew
rstData!NDir = FileName
rstData!LastModDate = the data...
rstData.Update
> cFile = Dir
Loop

Feb 9 '07 #9

P: n/a
DFS
Bri wrote:
Here is a sample code from the KPD-Team's API-Guide
(http://www.allapi.net/).

What's up, fella? Why would you direct anyone to that idiotic search site?

Feb 9 '07 #10

P: n/a
"DFS" <nospam@dfs_.comwrote:
>Bri wrote:
>Here is a sample code from the KPD-Team's API-Guide
(http://www.allapi.net/).


What's up, fella? Why would you direct anyone to that idiotic search site?

This used to be a very good website for APIs, but it is dead for some
time now, and it seems that some "domain squattter" got the name.

You still kann get at it under

http://web.archive.org/web/*/www.allapi.net
Greetings
Matthias Kläy
--
www.kcc.ch
Feb 9 '07 #11

P: n/a
Bri
DFS wrote:
Bri wrote:
>Here is a sample code from the KPD-Team's API-Guide
(http://www.allapi.net/).


What's up, fella? Why would you direct anyone to that idiotic search site?
Hmm, that used to be the site for API info. It does look now like its
been taken over. :( Sorry about that. I downloaded their API-Guide years
ago and have used it for learning about all sorts of API calls. The
About lists that as their website, but is must have changed since then.

Did you at least get something out of the sample code I included?

--
Bri
Feb 10 '07 #12

P: n/a
DFS
Bri wrote:
DFS wrote:
>Bri wrote:
>>Here is a sample code from the KPD-Team's API-Guide
(http://www.allapi.net/).


What's up, fella? Why would you direct anyone to that idiotic
search site?

Hmm, that used to be the site for API info. It does look now like its
been taken over. :( Sorry about that. I downloaded their API-Guide
years ago and have used it for learning about all sorts of API calls.
The About lists that as their website, but is must have changed since
then.
Did you at least get something out of the sample code I included?
Yes, thanks.

My new Shell (not ShellWait) kludge works better than my first effort, but
unless I play around with pauses and DoEvents, it consistently generates
errors 70 or 3051.

'loop thru list of folders
Open "D:\docPath.bat" For Output As #1
Print #1, "dir """ & sharedPath & """ /a /t:w /-p /o:gen " & fileListing
& ""
Close #1
v = Shell("D:\docPath.bat", vbHide)
DoCmd.TransferText acImportFixed, "Import Spec Docs", "TEMP_TBL",
fileListing, False
'next
Feb 10 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.