Runtime Error 3011

[dancat]

Hi folks

I am trying to import multiple csv files into a temp table, here is the code i am using

Private Sub ImportMultiple()
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim filename As String
Dim path As String
Dim Response As String
Dim myStrFilter As String
Dim strInputFileName As String
Dim strSQL As String

path = "c:\Database\"
strFile = Dir(path & "*.csv")
'Lists each file in that directory with .csv extension and stores filename in an array
While strFile <> ""
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
If intFile = 0 Then
MsgBox "No files found"
DoCmd.SetWarnings True
Exit Sub
End If
'Imports each file
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferText acImportDelimi, "ImportSpec", "tblImport", path & strFileList(intFile), True
DoCmd.RunSQL ("UPDATE tblimport SET tblimport.f1 = " & Chr(34) & Replace(strFileList(intFile), ".csv", "") & Chr(34) & " WHERE tblImport.f1 is null ")

Next intFile
End Sub

However when i run the code i get the runtime 3011 error - however the error message displays one of the filenames I am trying to import, so it must see the files in the directory.

I have highlighted in red the line it selects when i hit the debug button.

Any Ideas???

 

[dancat]

Seems to be problem with the filenames, if I rename files first it seems to work ok

Sample of filename: S019 Shrewsbury to 10-02-2012 12.24.59 (15min).csv

Do you know if this should be a problem or if there is anyway around this?

 

[jdraw]

I added an error handler via MZTools.

You could use Debug.print statements within your code to see values of variable, rendered sql, etc as a general debugging/development practice.

Private Sub ImportMultiple()
Dim strFile As String
Dim strFileList() As String
Dim intFile As Integer
Dim filename As String
Dim path As String
Dim Response As String
Dim myStrFilter As String
Dim strInputFileName As String
Dim strSQL As String

   On Error GoTo ImportMultiple_Error

path = "c:\Database\"
strFile = dir(path & "*.csv")
'Lists each file in that directory with .csv extension and stores filename in an array
While strFile <> ""
  intFile = intFile + 1
  ReDim Preserve strFileList(1 To intFile)
  strFileList(intFile) = strFile
  strFile = dir()
Wend
If intFile = 0 Then
 MsgBox "No files found"
 DoCmd.SetWarnings True
Exit Sub
End If
'Imports each file
For intFile = 1 To UBound(strFileList)
 filename = path & strFileList(intFile)
 DoCmd.TransferText acImportDelimi, "ImportSpec", "tblImport", strPath &    strFileList(intFile), True
DoCmd.RunSQL ("UPDATE tblimport SET tblimport.f1 = " & Chr(34) & Replace(strFileList(intFile), ".csv", "") & Chr(34) & " WHERE tblImport.f1 is null ")

Next intFile

   On Error GoTo 0
   Exit Sub

ImportMultiple_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ImportMultiple"
End Sub

I just saw your last post--- You will have issues if you include spaces in file names, and Access field and object names.

 

[dancat]

Yep I have no way of changing the format of the filename before it is sent it is automatically generated.

We are going to be getting sent about 40 of these files everyweek so was hoping that i wouldnt have to rename them.

Is there no way around it?

thanks