SLOW FileSearch

[DataMiner]

In Access 2002, when I run the code shown below to search for a file, the execute step runs for 5+ minutes and finds 15 files.

If I just use the Windows startup menu search tool I get the same 15 files and it takes maybe 3 seconds.

Is there any way to make VB run this faster???

By the way, by trial and error it seems that if lastimportdate includes a time (ie, is not an integer) then .execute finds NO files. Has anyone else noticed this?

Code start:-----------
lastimportdate=#7/8/2003#
with application.filesearch
.newsearch
.lookin="f:\rawdata\qa"
.filetype=msofiletypeallfiles
.searchsubfolders=true
.propertytests.add "last modified",msoconditiononorafter,lastimportdate
if .execute > 0 then
[do some stuff]
end if
end with
Code end---------------------

 

[bjackson]

i have asked the same question,but alas nobody seems to know of a faster way to search. or if they do they are keeping it to themselves

i am using access 2000 and have the same problem.
the only thing that speeds up my search is to keep moving old files out of the folder that i search to keep their numbers to an
acceptable search time

if you do find a way please let me know

 

[DataMiner]

Unfortunately, I don't own the files I'm searching so I can't move them. I was hoping that limiting the search by date would make it run faster, but that doesn't seem to be the case.

If I find a faster way I'll try to remember to post. Meanwhile, I guess I just have to run my searches overnight, as some of them can take an hour or more.

I am now wondering if the time is just network-traffic related. Yesterday afternoon I ran a search that took more than 30 minutes (not sure how long, because I finally went home and left it running) to find 215 files. This morning I ran the same search in an adjacent folder and it took less than a minute to find 60 files.

I noticed that even when the search is taking a long time, task manager shows only about 5% CPU usage....but my PC seems to be basically unusable during this time anyway. Does that make sense?

 

[jeremie_ingram]

What if you were to creat a batch file that ran the search VIA dos, returning the information to a txt file, that could then be read into the database? I have no idea where you are searching or what you are wanting to do with the results, but could this be a plausible option?

 

[DataMiner]

I'm certainly willing to try that.... can you tell me the syntax for doing a search in DOS? I need to be able to limit the search by last date modified.

What I am doing is concatenating a whole bunch of text files into one file. Each file contains a single record and I am using the DOS "type" command to concatenate them (having been unable to find a decent way to do this in VB) prior to importing into Access.

 

[DataMiner]

Oh, never mind....it's just the "dir" command, isn't it?

 

[dcx693]

Perhaps you can use some ideas from here: HOW TO: Determine When a Shelled Process Ends in Access 2000

I don't know how to set the Dir command in DOS to search for only files using any type of date filtering, but you could get all files and filter them out in Access.

There is another option, though I'm not sure if it would be any faster. You can use the VBA Dir() function, but I've never used it to search subfolders, so I don't know if it can.

 

[jeremie_ingram]

Yes, it is the DIR command, so if you know either the file type or if the have a fixed prefix, you could search for them easily.

C: DIR *.txt /s > C:\found.txt

Would search the C: drive and all subdirectories for any text file. If the had a fixed prefix you could substitute the *.txt with PRE*.txt. The > outputs the results to a file on teh C drive called found.

 

[bjackson]

Jerimie
all this stuff is way over my head but i am willing to try anything

using your method ,if i know the file name and the folder name
and the file ext, what would the syntax for the Dir() function