VBA code to import data

[Kicker]

I have been playing with this all day and have looked at just about every forum I belong to.

The following code works "sometime" and blows up at others. I don't have to do anything with the AccessTest.csv file, it just changes how it works all by itself.

Private Sub Command0_Click()
    DoCmd.RunSQL "delete * from accesstest"
    DoCmd.TransferText acImportDelim, , "Accesstest", "AccessTest.csv", True
End Sub

Here is what I have....
I have a file that has been exported from the company database (oracle).
Two of the fields contain data that could be number or string. The AccessTest table has them as text and when it works, it works great.

the most common error is that the Jet engine can't find the csv file.

Is there another (better?) way of getting the data into my access application?

ttfn
Kicker

 

[chilko]

Try entering a full path

I used this code and it worked consistantly for me when I put in a full path as below. I'm fairly green too so better check test it well. Private Sub Command0_Click()
DoCmd.RunSQL "delete * from accesstest"
DoCmd.TransferText acImportDelim, , "Accesstest", "c:/temp/AccessTest.csv", True
End Sub

 

[cable]

If it complains that it can't find it, then its not lying to you!

From the looks of that code you posted you've made the assumption that the current path will have TestAccess.csv in it. Now unless theres a 'chdir' line somewhere before this import that will not always be the case.

You should replace the filename with full path information, ie c:\data\testaccess.csv, or if a network path \\server\x\data\testaccess.csv.