title: MS Access 2013, SQL Server 2012 Express, query timeout
body: <p>I have an app using SQL Server 2012 Express on the local with a Microsoft Access 2016 frontend with forms, macros and modules. It is used in the field to gather inspection data, including many photographs. The problem is that when inserting to the main remote SQL Server 2012 I get an error:</p>
  
  <blockquote>
    <p>Microsoft ODBC SQL Server Driver error: Query timeout expired</p>
  </blockquote>
  
  <p>if the user attaches more than 3 photos. </p>
  
  <p>Thus far I’ve tried raising the Remote query timeout on the remote SQL Server (Properties\Connections), I've raised the server refresh interval to 240 seconds (Tools\Options). I've added code to the VBA in the SQL function in Access:</p>
  
  <pre><code>Dim Conn As ADODB.Connection
  Set Conn = New ADODB.Connection
  Conn.ConnectionTimeout = 120
  </code></pre>
  
  <p>And I’ve added the following function to the AutoExec macro that runs when the app starts:</p>
  
  <pre><code>Function SetTimeout()
        Dim Mydb As Database
        Set Mydb = CurrentDb
        Mydb.QueryTimeout = 640
    End Function
  </code></pre>
  
  <p>Finally I've added "Connection Timeout=90" to the end of the connection string in VBA: </p>
  
  <pre><code>Server=localhost\SQLEXPRESS2012;Database=DamInspector; 
  </code></pre>
  
  <p>A test update script only takes 67 seconds to run but I've tried various lengths of time from "0" (infinite) to 1024. The specific Run-time error is '-2147217871 (80040e31)' [Microsoft][ODBC SQL Server Driver]Query timeout expired<br>
  <strong>Perhaps a different method?</strong><br>
  I have 14 tables that must be synced by 11 inspectors.  7 of the tables are for photos.  Would it be better to run a stored procedure on each local instance of SQL Express rather than scripting with VBA through the linked tables?</p>