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

FileSearch not working in Access 2003

P: n/a
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!!!

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


P: n/a
IME, FileSearch is not reliable. It works inconsistently across different
versions of Windows, and doesn't find all file types (such as zip files.) It
has been removed from Access 2007.

For an alternative, see:
List files recursively - List files in a folder and subfolders
at:
http://allenbrowne.com/ser-59.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<st***********@ncmail.netwrote in message
news:11*********************@a34g2000cwb.googlegro ups.com...
>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!!!
Feb 13 '07 #2

P: n/a
On Feb 12, 8:08 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
IME, FileSearch is not reliable. It works inconsistently across different
versions of Windows, and doesn't find all file types (such as zip files.) It
has been removed from Access 2007.

For an alternative, see:
List files recursively - List files in a folder and subfolders
at:
http://allenbrowne.com/ser-59.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<stuart.med...@ncmail.netwrote in message

news:11*********************@a34g2000cwb.googlegro ups.com...
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!!!- Hide quoted text -

- Show quoted text -
Thanks for your help!!!

Feb 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.