DoCmd.TransferText Runtime error '3001' Invalid argument (1 Viewer)

[Riya]

I have a couple of queries & trying to import my log details into an access table. On running the following it stops at the point below & gives me the above error.


DoCmd.TransferText acImportDelim, "Logs Import Specification", "tmp Temporary File", fname2, 0

please help!!!!

Riya

 

[gemma-the-husky]

looks like your fname2 parameter is wrong - this should be a true or false depending on whether your csv table has column headers or not. as you have an import spec, it will ignore the top line if set to true.

i also sont think you need the final parameter as a 0 - just end at the true or false, or if necessary add a final comma

 

[Riya]

I don't quite get that fname2 is picking up a file name. take a look at the codes below might give a better understanding. sorry i am not a coder....

Private Sub ImportLogs_Click()
Dim a As Date, fname1, fname2, d As String, sqltxt As String

DoCmd.SetWarnings False

For a = Me.StartDate To Me.EndDate
DoCmd.SetWarnings False
d = "[logdate] = #" & Format(a, "dd/mm/yyyy") & "#"
Me.filedate = Format(a, "dd/mm/yyyy")
Me.Repaint
z = DLookup("[DateImported]", "tbl ImportLog", "" & d & "")



'If IsNull(z) = False Then
'MsgBox "The File for " & a & " has already been imported."
'Else

DoCmd.OpenQuery "qry delete temporary table"




fname1 = "l:/ex" & Format(a, "yymmdd") & ".Log"
fname2 = "l:/txtfiles/ex" & Format(a, "yymmdd") & ".txt"
FileCopy fname1, fname2

DoCmd.TransferText acImportDelim, "Logs Import Specification", "tmp Temporary File", fname2, 0

Kill fname2


DoCmd.OpenQuery "qry delete images logs"
DoCmd.OpenQuery "qry delete no ref logs"
DoCmd.OpenQuery "qry Append to Logs Table"

sqltxt = "INSERT INTO [tbl ImportLog] ( LogDate, DateImported )SELECT '" & DateValue(a) & "' AS LogDate, '" & Format(Now(), "dd/mm/yyyy") & "' AS importedDate;"
DoCmd.RunSQL sqltxt

b = DMax("[tbl ImportLog].[LogDate]", "tbl ImportLog")
c = Format(Now() - 1, "dd/mm/yyyy")


Me.filedate.Format = Text

Me.filedate = Format(a, "dd/mm/yyyy")
DoCmd.RunMacro "mcro Log Report"

'End If



If b = c Then
Me.LastImported.SetFocus
Me.StartDate.Visible = False
Me.EndDate.Visible = False
Me.ImportLogs.Visible = False
Me.CloseForm.Visible = True


Else
Me.StartDate.Visible = True
Me.EndDate.Visible = True
Me.ImportLogs.Visible = True
Me.CloseForm.Visible = False
Me.StartDate = b + 1
Me.EndDate = c
Me.StartDate.SetFocus
Me.Repaint
End If


Me.LastImported = b
Me.Repaint

Next



DoCmd.SetWarnings True

End Sub

 

[boblarson]

Your problem is twofold

DoCmd.TransferText acImportDelim, "Logs Import Specification", "tmp Temporary File", fname2, 0

SHOULD BE:

DoCmd.TransferText acImportDelim, "Logs Import Specification", "tmp Temporary File", fname2, FALSE

And your file naming for fname1 and fname2 is wrong. You're using forward slashes (used in URLS) instead of BACK slashes (used for computer file locations).

 

[gemma-the-husky]

sorry, i had a quick look at Access help, and thought you had an extra parameter. I'm sure bob is right with the backslashes. I think 0 will probably resolve as false, but typing false as a reserved word is more explicit

 

[Jibbadiah]

To quote - John Nurick [Microsoft Access MVP]... (and save you reading the full post).

Remember that the total size of an MDB file cannot exceed 2GB (including
not just the data but system objects, overhead caused by the way Access
stores tables, indexes, and of course queries, reports, forms, macros
and modules). If the total size of your textfiles is more than about 1GB
you could well be hitting this limit at some stage, so you need to
exclude that possibility.

Instead of using the TransferText macro action and the "Import
Delimited" argument to import the data, use TransferText with "Link
Delimited". In the user interface, use File|Get External Data|Link.

This creates a "linked table" in Access: in most ways it behaves exactly
like an ordinary table, but the data is not imported. Instead, the table
is literally linked to the textfile. When you delete a linked table, the
link is broken but the textfile is not affected.

For technical reasons, records in a table linked to a textfile cannot be
edited, but I have the impression that this will not be a problem for
you. Linking has the big advantage that you won't be importing huge
amounts of data into the textfile and then deleting it again, so "bloat"
and the 2GB limit should be much less of a problem.

... I was importing hundreds of tables of a set format and the change from transfer to link solved my problem.