Access VBA - while importing *.CSV I'm getting Run time error 31519. You can not import this file

Question

While using DoCmd.TransferText Method in Access, I'm getting error described in the question title. My code is taking file names from the table in current database (these are files selected from somewhat above 2000 files present in the single folder) and its goal is to import these files' content to the one table in Access. Please read this topic for more info: VBA procedure to import only selected csv files (from one folder) into a single table in access

All files have the same structure and data type.

here is the code I'm using:

 Sub Importing_data_into_a_single_table_csv_version()
   Dim my_path As String
   Dim rs As Recordset
   Dim start As Double
   Dim total_time As String

     DoCmd.SetWarnings True
     my_path = "C:\Users\michal\SkyDrive\csv\bossa\mstcgl_csv\"     ' source folder.

     start = Timer         ' remember time when macro starts.

     Set rs = CurrentDb.OpenRecordset("CSV Files")   ' opens the table with file names.
       Do Until rs.EOF
          If Dir(my_path & rs.Fields(0).Value) <> "" Then
          ' DoCmd.TransferText acImportDelim, "macro_link_specification", "all_stocks_3", "my_path & rs.Fields(0).Value", True
             DoCmd.TransferText acImportDelim, "import", "all_stocks_1", "my_path & rs.Fields(0).Value", True
          ' expression. TransferText ( TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage )

          End If
          rs.MoveNext
       Loop

   total_time = Format(Timer - start, "hh:mm:ss")
   MsgBox "This code ran successfully in " & total_time, vbInformation
 End Sub

The code crashes at DoCmd.TransferText acImportDelim, "import", "all_stocks_1", "my_path & rs.Fields(0).Value", True.

Is there some special way a destination table should be prepared to import these files? Sorry for these type of questions, but I'm using Access for 3 weeks now, so I'm just a beginner. I'm using import specification called "import". Can this be the cause of the problems?

Here is what the destination table looks like:

Here is another table I've been trying to import my data into. The field names in this table have no special characters and the same data types set on the fields, but it makes no difference. The same error 31519 is returned.

Huffman

First of all make sure that you able to import text using standard External Data -> Import Access, use stored specification. I think the problem is in specification. Check decimal separator first. Also try to rename column headers, spacial characters sometimes may cause problems. – Lezlielg

You may have to temporarily rename the file from .mst to .csv to be able to import it. Access can be finicky with these things. – Botfly

@Botfly Yes Andre, Thank You for this suggestion. The code in this question is slightly modified, in relation to the code from previous question. I did all I could think of, to exclude some stupid mistakes. This code works solely on *.CSV files, which names are taken from "CSV Files" linked table :-) I just don't understand why this error appears, when manually I'm able to load these files, one by one into the table. – Huffman

Wait, is "my_path & rs.Fields(0).Value" your actual code? You must remove the quotes so the variables are actually used. DoCmd.TransferText acImportDelim, "import", "all_stocks_1", my_path & rs.Fields(0).Value, True – Botfly

@Botfly I'm checking Your suggestion right now. There's something to it. The error has changed, but the data is still not loading into the Access table. I need some time to figure it out, and I'll get back to You. – Huffman

@Botfly Hello Andre. Please write this comment as an answer, so I could mark it as correct answer. Removing the quotes from FileName parameter of an DoCmd.TransferText Method was the thing that fixed the code, and it's working now. Thank You my friend. – Huffman

Answer 1

The solution turned out to be easy:

DoCmd.TransferText acImportDelim, "import", "all_stocks_1", _
                   "my_path & rs.Fields(0).Value", True

has the file name variables inside quotes, so it was used as literal string (and gave an invalid path).

Correct:

DoCmd.TransferText acImportDelim, "import", "all_stocks_1", _
                   my_path & rs.Fields(0).Value, True

Botfly