Can't Import table using DoCmd.TransferText

[ajaeger]

Here is my code. I am bombing at the DoCmd.TransferText line and I'm not sure why. I always get the error message from Err_BadFormat. I've tried .csv, .xls and .txt formats. Thanks.

'IMPORT the file
On Error GoTo Err_BadFormat
filename = "c:\ImportScores.csv"
DoCmd.TransferText acImportDelim, , "tblImportScores", filename

Exit Sub

Exit_cmdImportScores_Click:
Exit Sub

Err_BadFormat:
MsgBox "The file was not imported. Please check the data and reimport before continuing"
Resume Exit_cmdImportScores_Click

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[wemeier]

If your table already exists and it doesn't have exactly the same number of fields as your delimited file you'll get such an error (regardless of what extension you give your file). Otherwise, you must create an import/export specification that identifies the fields to be imported, what data type each field is, what delimiters to use, etc.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[ajaeger]

The file and table are identical. I can import the file into the table in Access by going to Insert>>Table>>Import Table and walking through the "Import Spreadsheet Wizard." I'm just having trouble with this code.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[PHV]

Comment out the On Error instruction to get the Error message

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wemeier]

Also, does the CSV file contain field names in the first record? If it does, add , True to your TransferText method. This acknowledges that the first row contains field names. Otherwise, it will try to treat it as data (and it may be incompatible with your table datatypes).

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[ajaeger]

No field header, just rows of text. Commenting out the "On Error" line just exits the sub without importing the table. There's something in the DoCmd.TransferText line that it doesn't like.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com