1

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:

  • Front End is an Microsoft Access VBA application
  • Data is stored on SQL Server Express on a remote machine
  • Data tables are linked in the front end application
  • 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.
  • DoCmd.RunSQL and CurrentDB.Execute do not seem to allow the use of the TRUNCATE TABLE -or- the 'DBCC CHECKIDENT' command.
  • 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.
  • -ALL- other tables behave the way I'd expect them to besides this one, resetting their indentity fields as appropriate.

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

Comrad_Durandal
  • 651
  • 2
  • 10
  • 23
  • 1. Tidy our question up so it isn't one long paragraph 2. Add what you have tried (eg you mention CHECKIDENT in a comment but nit in the question.. and downvote me for it) 3. As per comment, tell us how you want to run it: not "it doesn't work" – gbn Aug 08 '11 at 17:50
  • Your right - I did edit it to reflect this. – Comrad_Durandal Aug 08 '11 at 17:52

3 Answers3

1

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
gbn
  • 422,506
  • 82
  • 585
  • 676
  • The DBCC command does not work, as I did try the checkident command as well. TRUNCATE cannot be issued in this context either, as I get an 'Invalid SQL Statement (error #3129)' when this is done programmatically. Neither of these options work, sorry. – Comrad_Durandal Aug 08 '11 at 17:28
  • I know they work... so what are *you* doing wrong? Verify they work in SQL Server tools then find out why not in Access. And tidy your question too so it's more readable... – gbn Aug 08 '11 at 17:33
  • 1
    @Comrad_Durandal: more too: these are SQL Serve command, not Jet commands. I don't care what Jet says. They should be run on the server or as pass-through queries: not against the linked tables. – gbn Aug 08 '11 at 17:51
  • They do work, but only from the management console. I edited my question to reflect this (your right, I did word it wonky, and tried to revise it to make more sense) as well as hopefully to provide more information. – Comrad_Durandal Aug 08 '11 at 17:51
  • +1 on the pass-through idea - see, I didn't know this. I figured I couldn't DROP or ALTER linked tables - but I didn't know I couldn't just send a raw SQL command to the server using DoCmd.RunSQL. I will look at pass-throughs, see how to use them. – Comrad_Durandal Aug 08 '11 at 18:13
0

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)

Cauê Sauter Guazzelli
  • 21
  • 4
0

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.

Comrad_Durandal
  • 651
  • 2
  • 10
  • 23