Import Directory into access table 1

[jlg5454]

Hi,

I've been experiencing a Run Time error of 3011 in my code. I'm trying to import a entire directory into one access table. The code I used is below:

Private Sub Command1_Click()

Dim ImportFile
ImportFile = Dir("M:\dd\dd\IVR Database\June 05\*.txt")
Do While ImportFile <> ""
DoCmd.TransferText acImportDelim, "comma", "tblJune", ImportFile
ImportFile = Dir
Loop

End Sub


Is there something that I'm missing?

Thanks for any assistance that anyone can provide.

JG

 

[earthandfire]

3011 means object not found - if your directory is valid then is the table actually called tblJune?

 

[jlg5454]

tblJune is the correct name of the table. Within the error message, It states that it can not find object '0601.txt'.
Why is it stating this when this file is in the correct path and I'm looking to import all files and not just one text file.

Thanks for your reply

 

[axism]

hmm i m not sure if Dir("M:\dd\dd\IVR Database\June 05\*.txt") will pick up all the txt files or not. maybe try removing the *.txt from it? i had problem with transfertext before for exporting coz i didnt have that schema file created. i did notice that if u leave that parameter out during exporting, it will created by access. unless already have a "comma" schema, else try leaving it blank.
DoCmd.TransferText acImportDelim, , "tablename", ImportFile
that's my guess only. if u dont have any other thoughts, can give it a try i guess. good luck

 

[earthandfire]

Try:

Dim path As String
Dim fullname As String
Dim ImportFile
Path = "M:\dd\dd\IVR Database\June 05\"
ImportFile = Dir(path & "*.txt")
Do While ImportFile <> ""
fullname = path & ImportFile
DoCmd.TransferText acImportDelim, "comma", "tblJune", fullname
ImportFile = Dir
Loop

Dir just returns the filename, so TransferText will be looking in the default or working directory as opposed to the path that Dir is looking in.

Hope this helps.

 

[jlg5454]

Earthandfire,

Thanks! it worked like a charm!