title: Access & SQL-Server: Resetting Autonumber field with table wipe
body: <p>I am trying to keep a copy of an activity table synchronized between a SQL Server Express table and the production Access version.  In other tables, I am able to wipe the contents of the SQL Server table, and insert the whole mess back in to capture all of the changes.  However, when I attempt to do that with this table - I get the information - but the Autonumber field increments from the last unused number from the previous iteration.  After a dozen or so 'sync' operations, I am dangerously close to running out of autonumbers for this field.  </p>
  
  <p>I have tried issuing the 'DBCC CHECKIDENT' from the Microsoft Access front end application, which throws an error that the statement didn't start with SELECT, DELETE, PROCEDURE, DROP, ALTER or something like that.  This command DOES work when issued from the SQL Server management console (a tool I do have access to in the current test environment, but will NOT when the application goes production).</p>
  
  <p>I then tried the 'TRUNCATE TABLE' query using the DoCmd.RunSQL command and it threw the error that the operation isn't supported on linked tables (approximately).  This table is linked to the Microsoft Access front-end (the back end table is on SQL Server Express).</p>
  
  <p>So, for a quick summary:</p>
  
  <ul>
  <li>Front End is an Microsoft Access VBA application</li>
  <li>Data is stored on SQL Server Express on a remote machine</li>
  <li>Data tables are linked in the front end application</li>
  <li>I will NOT be able to use SQL Server Management Console commands when this application goes production, it needs to run it's housekeeping on it's own.</li>
  <li>DoCmd.RunSQL and CurrentDB.Execute do not seem to allow the use of the TRUNCATE TABLE -or- the 'DBCC CHECKIDENT' command.</li>
  <li>Both of the aforementioned functions -DO- work when issued from the SQL Server Management Console - see above as to why this isn't a viable option.</li>
  <li>-ALL- other tables behave the way I'd expect them to besides this one, resetting their indentity fields as appropriate.</li>
  </ul>
  
  <p>-- Edited 08/08/2011 @ 15:08 --</p>
  
  <p>Alright - I have tried a number of attempts at a VBA-based pass-through query, all resulting in an ODBC -- call failed error.  Here is the code for the module I created to handle pass-throughs (borrowed the code from dbforums.com):</p>
  
  <pre><code>Function RunPassThrough(ByVal ConnectionString As String, ByVal SQL As String, Optional ByVal QueryName As String)
  
      Dim dbs As DAO.Database
      Dim qdf As DAO.QueryDef
  
      Set dbs = CurrentDb
      Set qdf = dbs.CreateQueryDef
      With qdf
          .Name = QueryName
          .Connect = ConnectionString
          .SQL = SQL
          .ReturnsRecords = (Len(QueryName) &gt; 0)
          If .ReturnsRecords = False Then
              .Execute
          Else
              If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
              dbs.QueryDefs.Append qdf
          End If
          .Close
      End With
      Set qdf = Nothing
      Set dbs = Nothing
  
  End Function
  </code></pre>
  
  <p>As such, I need to specify a connection string to the database; all of the following have failed:</p>
  
  <pre><code>strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;Uid=&lt;my username&gt;;Pwd=&lt;my password&gt;;"
  </code></pre>
  
  <p>Result: Run-time error ODBC -- call failed (error #3146)</p>
  
  <pre><code>strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;TRUSTED_CONNECTION=YES;"
  </code></pre>
  
  <p>Result: Run-time error ODBC -- call failed (error #3146)</p>
  
  <pre><code>strConnect = "ODBC;DSN=instkeeper_beta;"
  </code></pre>
  
  <p>Result: Asks me for the Data Source Name, once specified with the Data Source administration panel, I get Run-time error ODBC -- call failed (error #3146)</p>
  
  <pre><code>strConnect = "ODBC;Server=ENV980-067\ENVIRON_TEST;Database=instkeeper_test;User ID=&lt;my user name&gt;;Password=&lt;my password&gt;;Trusted_Connection=False;"
  </code></pre>
  
  <p>Result: Run-time error ODBC -- call failed (error #3146)</p>
  
  <p>-- Edited 08/08/2011 @ 16:41 --</p>
  
  <p>MORE failures on iterations of the connection strings, I am officially out of ideas on how to make this beast work.  After trying the previous - now any pass-throughs made with the interface fail after asking for a DSN.  No repair is possible, they have to be restored to call on the linked tables and ran through JET.</p>
  
  <pre><code>strConnect = "ODBC;DATA SOURCE=instkeeper_test;"
  </code></pre>
  
  <p>Result: Run-time error ODBC -- call failed (error #3146)</p>
  
  <pre><code>strConnect = "ODBC;DRIVER=SQL Server;SERVER=ENV980-067\ENVIRON_TEST;"
  </code></pre>
  
  <p>Result: Run-time error ODBC -- call failed (error #3146)</p>
  
  <pre><code>strConnect = "ODBC;DRIVER=SQL Server;Server=ENV980-067\ENVIRON_TEST;Database=instkeeper_test;User ID=&lt;my user name&gt;;Password=&lt;my password&gt;;"
  </code></pre>
  
  <p>Result: Run-time error ODBC -- call failed (error #3146)</p>