title: Set commandtimeout for linked SQL Tables/Views in Access front end
body: <p>We have moved some back-end data tables over from a network drive (mbd file) to being on an SQL Server database.  Things mostly work great, but if staff are accessing things through a VPN (which slows things down a lot), then we get connection errors when we run reports that retrieve a lot of data.  My guess is that I need to set a timeout to a larger value, and I did some research and it seems that I need to set the commandtimeout (or maybe query timeout?).</p>
  
  <p>Below is the VBA code we use to connect the SQL Server tables/views to our Access front end from the SQL Server back end.  Am I right that I likely need to specify a commandtimeout?  Where in this would we add the commandtimeout (or other timeout) value?</p>
  
  <pre class="lang-vb prettyprint-override"><code>Public Sub CreateSQLLinkedTable(strSourceTableName As String, strNewTableName As String)
  
  '************************************************************************************
  '* Create a linked table in the current database from a table in a different SQL Server file.
  '* In:                                                                              *
  '*   strNewTableName - name of linked table to create                               *
  '*   strSourceTableName - name of table in database                             *
  '************************************************************************************
  
  Setup:
  
      Dim tdf As TableDef
      Dim strConnect As String, strMsg As String
      Dim myDB As Database
  
      ' set database vars
      Set myDB = CurrentDb
      Set tdf = myDB.CreateTableDef(strNewTableName)
  
  MakeConnections:
  
      On Error GoTo OnError
  
      ' turn system warnings off
      DoCmd.SetWarnings False
  
      ' define connect string and source table
      ' We do not need to specify the username (Uid) and password (Pwd) in this connection
      ' string, because that information is already cached from the connection to the SQL
      ' Projects database that we created in CheckSQLConnection() that was run to check connection
      ' to the database.  So here we can have a connection string without the Uid and Pwd.
      With tdf
          .Connect = "ODBC;Driver={SQL Server};" &amp; _
                    "server=" &amp; myServer &amp; ";" &amp; _
                    "database=" &amp; mySQLDB &amp; ";"
  
          .SourceTableName = strSourceTableName
      End With
  
      ' execute appending the table
      myDB.TableDefs.Append tdf
  
      ' turn system warnings back on
      DoCmd.SetWarnings True
  
  ExitProgram:
      ' this block of code will run if there are no errors
  
      Exit Sub
  
  OnError:
      ' this block of code runs if there is an error, per On Error assignment above
  
      ' display error message with details
      MsgBox "There was an error connecting to the SQL Server data source Projects. Error = " &amp; err &amp; ", Description: " &amp; err.Description
  
      'exit Projects
      Call CloseFormsAndQuit
  
  End Sub
  </code></pre>