title: Escape double quotes in Access VBA - INSERT INTO ... SELECT
body: <p>I own VBA code below that matter many text files to an Access table. But there is a problem for cases of .TXT files containing text with double quotation marks and consequently breaks all the other fields of this record with null values.</p>
  
  <p>I tried to put a Replace function in the selection of the Product field, but did not work for Double Quotes. With other characters it works, but double quotes (no) ...</p>
  
  <p>Which adjustments do you recommend? Any suggestions would be appreciated.</p>
  
  <p>*Note: The actual data is more than 1 million records...</p>
  
  <p><b>SCHEMA.INI</b><br>
  [Test_temp.csv]<br>
  ColNameHeader=false<br>
  Format=Delimited(;)<br>
  Col1="product" Text<br>
  Col2="price" Double<br></p>
  
  <p><b>TEXT FILE CSV : test01.txt</b><br>
  TV SAMSUNG 21" WIDESCREEN LED;170<br>
  TV PHILIPS 27" WIDESCREEN LED;200<br>
  HD SEAGATE 1TB 7200RPM;150<br></p>
  
  <p><br>
  <b>Code VBA Access:</b>
  <br></p>
  
  <pre><code>Sub TableImport()
  
      Dim strSQL As String
      Dim db As DAO.Database
  
      Dim strFolder As String
      strFolder = CurrentProject.Path
  
      Set db = CurrentDb
  
      strSQL = "DELETE FROM tbTest"
      db.Execute strSQL, dbFailOnError
  
      Dim strFile As String
      strFile = Dir(strFolder &amp; "\test*.txt", vbNormal)
  
      Do Until strFile = ""
  
          FileCopy strFolder &amp; "\" &amp; strFile, strFolder &amp; "\Test_temp.csv"
  
          strSQL = ""
  
          strSQL = " INSERT INTO tbTEST(product,price)"
          strSQL = strSQL &amp; " SELECT fncReplace(product),price"
          strSQL = strSQL &amp; " FROM [Text;HDR=no;FMT=Delimited;DATABASE=" &amp; strFolder &amp; "].Test_temp.csv"
  
          db.Execute strSQL, dbFailOnError
  
          strFile = Dir
  
      Loop
  
      db.Close
  
  End Sub
  
  
  Public Function fncReplace(varStr As Variant) As String
      If IsNull(varStr) Then
          fncReplace = ""
      Else
          fncReplace = Replace(Trim(varStr), """", "''")
      End If
  End Function
  </code></pre>
  
  <p><br>
  <b>UPDATE - It worked - Suggested by: Andre451</b>
  <br></p>
  
  <pre><code>Sub TableImport()
  
      Dim strSQL As String
      Dim db As DAO.Database
  
      Dim strFolder As String
      strFolder = CurrentProject.Path
  
      Set db = CurrentDb
  
      strSQL = "DELETE FROM tbTest"
      db.Execute strSQL, dbFailOnError
  
      Dim strFile As String
      strFile = Dir(strFolder &amp; "\test*.txt", vbNormal)
  
      Do Until strFile = ""
  
          FileCopy strFolder &amp; "\" &amp; strFile, strFolder &amp; "\Test_temp.csv"
  
          DoCmd.TransferText acLinkDelim, "specIMPORTAR", "linkData", strFolder &amp; "\Test_temp.csv", False
  
          strSQL = ""
          strSQL = " INSERT INTO tbTEST(product,price)"
          strSQL = strSQL &amp; " SELECT product,price"
          strSQL = strSQL &amp; " FROM linkData"
  
          db.Execute strSQL, dbFailOnError
  
          strFile = Dir
  
          DoCmd.DeleteObject acTable, "linkData"
  
      Loop
  
      db.Close
  
  End Sub
  </code></pre>