title: Moving from Access to SQL Server
body: <p>I recently moved my application's database from MS Access to SQL Server. And I am really shocked - even though having a good server - most of the <code>INSERT INTO</code> queries from front-end Access are horribly slow. Previously while retrieving data I was putting it into temporary access table then after retrieving is completed I did:</p>
  
  <pre><code>INSERT INTO permanentTable
      SELECT * 
      FROM tmpTable ;
  </code></pre>
  
  <p>And it took less than 1 minute to move all records to <code>permanentTable</code>.</p>
  
  <p>Now with SQL Server it's slightly different - inserting 80 rows takes about 5-6 minutes. And I was trying to optimize the query (it's a table with 40 columns) by removing the index - and it's not fast at all. On the server itself when running the query - it's really fast. What can be wrong? Tried to insert it one by one - and it takes about 5 seconds for a row to insert.</p>
  
  <p>From MS Access I am using ODBC connection with ADODB.conn string and ADODB.execute method.</p>
  
  <p>I am using a code similar to: </p>
  
  <pre><code>Sub AdoOdbcExample() 
  Dim con As Object 
  Set con = CreateObject("ADODB.Connection") con.Open _ "Driver={SQL Server Native Client 11.0};" &amp; _ "Server=.\SQLEXPRESS;" &amp; _ "Database=myDb;" &amp; _ "Trusted_Connection=yes;" 
  con.Execute "UPDATE Clients SET FirstName='Gord' WHERE ID=5;" 
  con.Close Set con = Nothing 
  End Sub
  </code></pre>
  
  <p>Additionally, this is how my connection string looks like:</p>
  
  <pre><code>Dim conn As ADODB.Connection 
  Dim sConnString As String 
  ' Create the connection string. 
  sConnString = "Driver={ODBC Driver 13 for SQL Server};server=nameserver;database=dbname;trusted_connection=Yes;" 
  ' Create the Connection
  Set conn = New ADODB.Connection 
  ' Open the connection and execute. 
  conn.Open sConnString 
  conn.Execute (query) 
  conn.Close 
  </code></pre>
  
  <p>And the query I am running is:</p>
  
  <pre><code>CurrentDb.Execute ("INSERT INTO [ODBC;Driver={ODBC Driver 13 for SQL Server};server=serverName;database=dbName;trusted_connection=Yes;].permanentTable SELECT * FROM tmpTable")
  </code></pre>