DoCmd.TransferText doesn't work unless you run a file manually

[Guest]

I am running the DoCmd.TransferText command to import a number of text files
into a database. If I don't run a file manually first I get an error from
Access on the first file saying the file cannot be found. If I run one file
manually using same file spec to start and then execute the code the
procedure works fine.

Here is the code:

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

Dim MyFile, MyDir, MoveCmd
Dim FileCount As Integer


FileCount = 0

MyDir = "C:\Pawn\"

MyFile = Dir(MyDir & "*.txt") ' Get first file entry

If MyFile = "" Then ' Make sure file exists
MsgBox ("No Files Found")
Else
Do While MyFile <> "" ' Start the loop.
FileCount = FileCount + 1
MsgBox (MyFile)
DoCmd.TransferText acImportFixed, "ImportSpec", "Pawns", MyFile
MyFile = Dir ' Get next entry.
Loop
End If

MsgBox (FileCount & " Files Processed")

 

[John Nurick]

At a guess, it's happening because you forgot that Dir() returns only
the file name, not the path. Try passing
MyDir & MyFile
to TransferText instead of just MyFile.



On Mon, 12 Jun 2006 08:21:03 -0700, B Farrell <B