FileSearch not working in Access 2003

[stuart.medlin]

I have recently converted an Access 97 database to Access 2003.
However, I am running into a problem with using Application.Filesearch
to locate a file in my directory. The code follows:

-------------------------------
Dim dbDir As String 'Directory where database stored
Dim dbPath As String 'Directory + Name of database
Dim dbFileName As String 'Name of the database
Dim Card1File As String 'Text file to hold Card 1 data
Dim Card2File As String 'Text file to hold Card 2 data
Dim Card3File As String 'Text file to hold Card 3 data
Dim PAAFile As String 'The resulting file created from
export
Dim fs As Variant 'File Search variable

'Retrieve the database path and database name and directory
dbPath = CurrentDb.Name
dbFileName = Dir(dbPath)
dbDir = Left(dbPath, Len(dbPath) - Len(dbFileName))

'Create the Pesticide text file names
Card1File = dbDir & "PAACard1.txt"
Card2File = dbDir & "PAACard2.txt"
Card3File = dbDir & "PAACard3.txt"


'Create final text file by appending julian day to the file name
"PAA200"
PAAFile = dbDir & "paa200." & DatePart("y", Now())

'Export the data from tables PestCard1, PestCard2, PestCard3 into
the 3 text
'files. These 3 text files will then be joined into 1 file.
DoCmd.TransferText acExportFixed, "PestCard1 Export Specification",
_
"PestCard1", Card1File, False, ""
DoCmd.TransferText acExportFixed, "PestCard2 Export Specification",
_
"PestCard2", Card2File, False, ""
DoCmd.TransferText acExportFixed, "PestCard3 Export Specification",
_
"PestCard3", Card3File, False, ""


'Set File search parameter to look for output file name
Set fs = Application.FileSearch
fs.FileName = PAAFile

'if output file is found, append data from the three text files to
the
'end of the current file. "/b" is added as a command line option to
'prevent EOF character from being copied to the file
If fs.Execute > 0 Then
Call Shell(Environ$("COMSPEC") & " /c copy """ & PAAFile & """ +
""" & _
Card1File & """ + """ & Card2File & """ + """ &
Card3File & _
""" """ & PAAFile & """ /b ", vbHide)

'Output file is not found, so Concatenate the 3 text files into one
file
Else
Call Shell(Environ$("COMSPEC") & " /c copy """ & Card1File & _
""" + """ & Card2File & """ + """ & Card3File & """
""" & _
PAAFile & """ /b ", vbHide)
End If

------------------------------------------------------

The filesearch is not finding the file as it should. Has there been a
change with how this works in Access 2003? This worked perfectly in
A97.

Thanks in advance for any help!!!

 

[Guest]

Hi Stuart,

The filesearch is not finding the file as it should.

What is it doing or failing to do?

My recommendation is to re-write your code so that it does not use the
Application.Filesearch method. Two reasons: 1.) It is slower versus using
either the Dir function (to search a single folder) or API code to search
many folders and 2.) Support for this method has been dropped from Access
2007. Therefore, even if you do invest the time to fix your code in Access
2003, it will not compile in 2007, and you'll be faced with having to fix it
again in the future.

If you are only searching one folder, then no problem. Just use the Dir()
function. However, if you need to search a folder and all subfolders, then
Dir() is not an appropriate method. Access Advisor has a free article
available that describes how to use API code to search for MP3 files. One can
easily search for other file extensions as well.

Find Your Data
Using MP3 files as an example, learn how you can search
for data on your hard drive.

http://zones.advisor.com/doc/16279

While it may be a PITA to have to make this change in an existing
application, one benefit is that the API method is much faster versus the
Filesearch method.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Richard]

Hi Stuart
I have a similar problem - the filesearch is Access 2003 works
slightly differently from previous versions. Without going into the
detail here, in my case my code finds 2 files, whereas in all versions
back to 97 (over 5 years) it correctly found 1 file. In my case I am
able to run a further test after the filesearch.
I will invest time in changing from filesearch to Dir in due course,
because of Tom's comments re 2007 dropping the method
Richard