Solved Why do I get Run-time error 3011?

[frankt68]

Hello!

I would appreciate it if someone could explain why I'm getting Run time error '3011' The Microsoft Access database engine could not find the object "C:\MyPath\MyFile 01.txt" when running the below code:

Sub LoopThroughFiles()
'to import all MyFiles*.txt data to table MyTable

Dim StrFile As String
Dim Path As String
   
    
    StrFile = Dir("C:\MyPath\MyFile*.txt")
    Path = "C:\MyPath\"
   
    Do While Len(StrFile) > 0
        Debug.Print StrFile
           
       'adds records to Table
       DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTable", Path & StrFile, True
     
               
       StrFile = Dir
    Loop

MsgBox "The end", vbInformation, "Import"
End Sub

The code should import the data from all txt files from folder "C:\MyPath\" with the name MyFile*.txt (for example MyFile 01.txt, MyFile 02.txt.....) into table MyTable. When I run the code, I get the Run time error '3011' The Microsoft Access database engine could not find the object "C:\MyPath\MyFile 01.txt" message.


If I rename the file to MyFile.txt there is no error and I can import the data.

 

[arnelgp]

can you try leaving out the "import spec" name in the TransferText method.

DoCmd.TransferText acImportDelim, , "MyTable", Path & StrFile, True

 

[frankt68]

can you try leaving out the "import spec" name in the TransferText method.

DoCmd.TransferText acImportDelim, , "MyTable", Path & StrFile, True

Doesn't work, it returns the same error 3011.

 

[Gasman]

Space in filename?

 

[arnelgp]

if i remember correctly, there is another person who has same problem
recently here: https://techcommunity.microsoft.com/
caused by recent Updates.
His solution is not to use that Method but instead.
open the Textfile and manually read each line, split into column and
save to the table.

 

[CJ_London]

depends on your delimiters but if they are commas, try sql, rather than transfertext

sqlstr="INSERT INTO MyTable SELECT * FROM (SELECT * FROM [TEXT;DATABASE=" & strPath & ";HDR=Yes]." & strFile & ")  AS txt"
currentdb.execute sqlstr

note that you will need the following values

you need strPath="C:\MyPath" (no end \)
and strFile="[MyFile 01.txt]" (put square brackets around the file name because of the space, this may solve your problem with transferstext)

 

[frankt68]

Thank you both, gasman and arnelgp, for your answers.
As gasman hinted, I also think it has to do with the spaces in the name. As I said before, if I rename the file to MyFile.txt, there is no problem.

The below code works without errors.

Sub LoopThroughFiles()
'to import all MyFiles*.txt data to table MyTable

Dim StrFile As String
Dim Path As String
Dim OldName As String
Dim NewName As String
    
    StrFile = Dir("C:\MyPath\MyFile*.txt")
    Path = "C:\MyPath\"
  
    Do While Len(StrFile) > 0
        Debug.Print StrFile

        'get the original file name
        OldName = Path & StrFile
        
        'temporary rename file'
        NewName = Path & "Zdravila.txt"
        
        Name Path & StrFile As NewName
        Debug.Print NewName
        Debug.Print OldName
          
       'adds records to Table
       DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTable", NewName, True
      
       'change temp. file name back to orginal file name
       Name NewName As OldName             
       StrFile = Dir
    Loop

MsgBox "The end", vbInformation, "Import"
End Sub

However, I still don't understand why the original code doesn't work.

 

[The_Doc_Man]

I'm going to suggest that the "space in the file name" problem is nearly all of what is wrong with that first sample you showed us. VBA uses a "space" character as a parsing token that is a separator from non-blank tokens. When you do this:

DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTable", Path & StrFile, True

The combination Path & StrFile in the 4th argument causes a concatenation, which tells VBA that the implied value string for that argument has been re-formed, which in turn causes a re-parse of that segment. So that segment, based on discussion (and using the name with the space in it), becomes

DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTable", C:\MyPath\MyFile 01.txt, True

The problem HERE is that because of concatenation's side effects and the fact that the other arguments ARE quoted strings, any quotes that MIGHT have been in that string are now gone. As you can see, you now have a partial file spec, a space, and a partial file spec. That space gets swallowed as a delimiter because your post-concatenation name is not in quotes - but the other arguments ARE (and should be in quotes). If you really want to do this, you might build the fully qualified file string as a separate variable and then pass it in to TransferText with quotes added back to replace the ones removed during the concatenation step.

strFQFS = "C:\MyPath\" & DIR( "C:\MyPath\MyFile*.txt" )
DoCmd.TransferText acImportDelim, "MyImportSpec", "MyTable", CHR$(34) & strFQFS & CHR$(34), True

Or something similar in which you try to re-supply quotes around the file-name argument, which IS supposed to be string anyway.

 

[frankt68]

depends on your delimiters but if they are commas, try sql, rather than transfertext

sqlstr="INSERT INTO MyTable SELECT * FROM (SELECT * FROM [TEXT;DATABASE=" & strPath & ";HDR=Yes]." & strFile & ")  AS txt"
currentdb.execute sqlstr

note that you will need the following values

you need strPath="C:\MyPath" (no end \)
and strFile="[MyFile 01.txt]" (put square brackets around the file name because of the space, this may solve your problem with transferstext)

Thank you for your reply. I'm afraid I don't know where to put this code. Is it instead of DoCmd.TransferText? FYI - the delimiter in text files is a semicolon.

 

[CJ_London]

you would use it in place of transfertext - but only works with comma delimiters, not semi colons or other separators so would not be appropriate for this specific requirement. It can be done, but requires a bit more work. Not much point in going into it as you now have a working solution

 

[frankt68]

Thank you all for your replies and explanations.
It turns out that the problem wasn't the space, but the additional dots in the original file name. So, after replacing them with an underscore, my original code works without error.
I apologize for the inconvenience...