title: Import textfiles into linked SQL Server tables in Access
body: <p>I have an Access (2010) database (front-end) with linked SQL Server-tables (back-end). And I need to import text files into these tables. These text files are very large (some have more than 200.000 records, and approx. 20 fields)</p>
  
  <p>The problem is that I can't import the tex tfiles directly in the SQL tables. Some files contain empty lines at the start, and some other lines that I don't want to import in the tables.
  So here's what I did in my Access database:</p>
  
  <p><strong>1)</strong> I created a link to the text files.</p>
  
  <p><strong>2)</strong> I also have a link to the SQL Server tables</p>
  
  <p><strong>3a)</strong> I created an Append-query that copies the records from the linked text file to the linked SQLServer table.</p>
  
  <p><strong>3b)</strong> I created a VBA-code that opens both tables, and copies the records from the text file in the SQL Server-table, record for record. (I tried it in different ways: with DAO and ADODB).</p>
  
  <p>[Step 3a and 3b are two different ways how I tried to import the data. I use one of them, not both. I prefer option 3b, because I can run a counter in statusbar to see how many records needs to be imported at any moment; I can see how far he is.]</p>
  
  <p>The problem is that it takes a lot of time to run it... and I mean a LOT of time: 3 hours for a file with 70.000 records and 20 fields!
  When I do the same with an Access-table (from TXT to Access), it's much faster.</p>
  
  <p>I have 15 tables like this (with even more records), and I need to make these imports every day. I run this procedure automatically every night (between 20:00 and 6:00).</p>
  
  <p>Is there an easier way to do this?
  What is the best way to do this?</p>