CODEWITHSUNDEEP
ms-accesssql-server-expressautonumber
Comrad_Durandal
Comrad_Durandal

Reputation: 641

Access & SQL-Server: Resetting Autonumber field with table wipe

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.

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).

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).

So, for a quick summary:

-- Edited 08/08/2011 @ 15:08 --

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):

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) > 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

As such, I need to specify a connection string to the database; all of the following have failed:

strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;Uid=<my username>;Pwd=<my password>;"

Result: Run-time error ODBC -- call failed (error #3146)

strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;TRUSTED_CONNECTION=YES;"

Result: Run-time error ODBC -- call failed (error #3146)

strConnect = "ODBC;DSN=instkeeper_beta;"

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)

strConnect = "ODBC;Server=ENV980-067\ENVIRON_TEST;Database=instkeeper_test;User ID=<my user name>;Password=<my password>;Trusted_Connection=False;"

Result: Run-time error ODBC -- call failed (error #3146)

-- Edited 08/08/2011 @ 16:41 --

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.

strConnect = "ODBC;DATA SOURCE=instkeeper_test;"

Result: Run-time error ODBC -- call failed (error #3146)

strConnect = "ODBC;DRIVER=SQL Server;SERVER=ENV980-067\ENVIRON_TEST;"

Result: Run-time error ODBC -- call failed (error #3146)

strConnect = "ODBC;DRIVER=SQL Server;Server=ENV980-067\ENVIRON_TEST;Database=instkeeper_test;User ID=<my user name>;Password=<my password>;"

Result: Run-time error ODBC -- call failed (error #3146)

Upvotes: 1

Views: 3627

Answers (3)

Cau&#234; Sauter Guazzelli
Cau&#234; Sauter Guazzelli

Reputation: 21

I found out an easy way of doing that trought a SQL query written in Access, at the SQL View!

Check it out at: http://answers.microsoft.com/en-us/office/forum/office_2003-access/reset-autonumber-in-access-table-automatically/66cbcfed-5cbe-40f6-b939-9aea8bbea2de

Write this:

ALTER TABLE YourTable ALTER COLUMN YourField COUNTER(1,1)

Upvotes: 0

Comrad_Durandal
Comrad_Durandal

Reputation: 641

I went through all of my code, and checked with a local VBA expert who pointed out that I had made an error in the naming of my tables while attempting to do a pass-through query. I, basically, was referring to the table by the name it possess as a link in my front-end, and not the actual table name it possesses in SQL Server on the back end.

The following connection string, once this was corrected, worked:

strConnect = "ODBC;DRIVER={SQL Server};SERVER=ENV980-067\ENVIRON_TEST;DATABASE=instkeeper_test;TRUSTED_CONNECTION=YES;"

This, then, allowed me to execute the TRUNCATE TABLE command on the remote table, and execute a DoCmd.RunSQL statement to repopulate the table from the production source.

The end result is that when the option is selected to update the Activity, it will purge the contents of the remote table, then read the contents of the production table into the test table for use while resetting the autonumber.

Upvotes: 0

gbn
gbn

Reputation: 432471

In the SQL Server side, you can

  1. Use another DBCC command

    DBCC CHECKIDENT ('MyTable', RESEED, 1)

  2. Or use TRUNCATE TABLE...

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used.

So you'd run

TRUNCATE TABLE MyTable

Upvotes: 1

Related Questions