Help! --- DoCmd.TransferText

[Arthur Dent]

Please, help, anyone, any ideas, i'm frantic!

I am TRYing to import a CSV file into an Access table. I set up an import
specification, ran it manually to test, all worked fine, data imported, all
was happy, no problems.

So now i go to try to call that spec, using DoCmd.TransferText, and it is
giving me an error which simply states "Invalid argument." That's it. no
help as to which argument or why, and near as i can tell, there is nothing
wrong with any of my arguments. My code is the following:

DoCmd.TransferText acImportDelim, "MyImportSpec", "MyDestinationTable",
fname, True

ANY ideas or help? I thought i could try using the Scripting runtime, and
use regular expressions, but I cant find online anywhere any regex's which
actually work. The closest i got was one which returned me only the first
column. No help.

I am using Access 2007, (on Vista FWIW)

Thanks!!!!

 

[Ken Snell \(MVP\)]

What is the value of fname variable? Is it a full path and filename string?

 

[Arthur Dent]

yah, its a valid path; S:\Desktop\myfile.csv

What is the value of fname variable? Is it a full path and filename
string?

 

[Ken Snell \(MVP\)]

Post the code that is running the DoCmd.TransferText step; let's see what is
happening prior to it. Also, verify that the specification indeed is saved
under the name that you're using.

 

[Arthur Dent]

This was the code running it ....

'Private Sub ImportPinnacleCSV()
' Dim dlgFile As Office.FileDialog
' Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
'
' With dlgFile
' .AllowMultiSelect = False
' .Title = "Select Pinnacle File"
' .Show
' End With
'
' Dim FName As String: FName = dlgFile.SelectedItems(1)
'
'On Error GoTo importErr
' DoCmd.TransferText acImportDelim, "PinnacleImport", "PinnacleCSV",
FName, True
'
' Exit Sub
'
'importErr:
' MsgBox Err.Description
'End Sub

I double-and-triple checked my spec names, both in the UI, and in the IMEX
system tables.

 

[Ken Snell \(MVP\)]

You "dim" dlgFile as a different data type than how you "set" it in your
code. I would change this line
Dim dlgFile As Office.FileDialog

to this:
Dim dlgFile As FileDialog



The code itself, otherwise, appears to be valid.

I note that you're telling the code that the csv file has a header row in
it. Does the "PinnacleImport" table already exist in the database? If yes,
does its field names exactly match the header row names in the csv file both
in terms of exact name and order?

 

[Dirk Goldgar]

In

So now i go to try to call that spec, using DoCmd.TransferText, and
it is giving me an error which simply states "Invalid argument."
That's it. no help as to which argument or why, and near as i can
tell, there is nothing wrong with any of my arguments. My code is the
following:
DoCmd.TransferText acImportDelim, "MyImportSpec",
"MyDestinationTable", fname, True

This is unlikely to be the cause, but your database isn't by any chance
at or near the 2GB maximum filesize, by any chance? According the KB
article 835416 (http://support.microsoft.com/kb/835416/en-us), you can
get this error when you try to import under those circumstances.