Seeding a COUNTER in Access 2007

 none
Seeding a COUNTER in Access 2007 RRS feed

  • Question

  • I am having a problem with a statement that I've used frequently, although only once in any database.  What I am trying to do is to reset the counter on an autonumber field to 1.

    ALTER TABLE table_name ALTER COLUMN column_name COUNTER(1,1);

    The first table I am doing this on is itbl_50_1490 and the field is ID, so the statement is:

    ALTER TABLE itbl_50_1490 ALTER COLUMN ID COUNTER(1,1)

    This is the first ALTER that I execute and it works perfectly.  The next step is to do the same thing for a second table, called itbl_50_1490_i with a field with the same name ID, so the statement is:

    ALTER TABLE itbl_50_1490_1 ALTER COLUMN ID COUNTER(1,1)

    When I run this second ALTER statement, I get an error: "Invalid field data type" the error number is 2950.

    I've double-checked the tables and in both tables, ID is the first column and is the primary key.  The field ID has the following properties:

    Data type = "AutoNumber"
    Field Size = Long Integer
    New Values =  Increment
    Format = 
    Caption =
    Indexed = Yes (No Duplicates)
    Smart Tags =
    Text Align = General

    I've tried changing the ID field name and rearranged the order of the commands in the macro to run the one that fails first.  It doesn't change the result.

    Any thoughts are appreciated!

    Mike


    mlr
    Thursday, August 18, 2011 6:45 PM

Answers

  • Note that the table name could become a problem. If you insert the first table, twice, in a query, the query designer will assign the alias to the second reference which will be the same as the second table name. That may bring confusion, to the developper.
    • Marked as answer by Macy Dong Thursday, September 1, 2011 1:59 AM
    Tuesday, August 23, 2011 4:13 PM
    Moderator
  • Got it to work.  Still confused on why I was getting the error.  I copied the table structure of the table where the ALTER statement was working, then modified the copy to match what I needed for the second table.  Re-ran the two ALTER statements and now it works fine. 

    Aack!

     


    mlr
    • Marked as answer by Macy Dong Thursday, September 1, 2011 1:59 AM
    Thursday, August 18, 2011 7:05 PM

All replies

  • Got it to work.  Still confused on why I was getting the error.  I copied the table structure of the table where the ALTER statement was working, then modified the copy to match what I needed for the second table.  Re-ran the two ALTER statements and now it works fine. 

    Aack!

     


    mlr
    • Marked as answer by Macy Dong Thursday, September 1, 2011 1:59 AM
    Thursday, August 18, 2011 7:05 PM
  • Hi Mike,

    In my opinion changing the seed of an autonumber is only asking for troubles instead of solving them.

    So I am very curious to hear what your reasons are to use this technique.

     

    Imb.

    Thursday, August 18, 2011 9:11 PM
  • Hi Mlr,

     

    Thank you for posting in our forum.

     

    I am glad to hear that you got the issue working. Well done!

     

    By your original and new posts, I summarize:

    1. One of the statements can work perfectly in your environment.

    2. The other one can also work well by copying the table structure of itbl_50_1490.

     

    I copied the table structure of the table where the ALTER statement was working, then modified the copy to match what I needed for the second table.

     

    Do you mean that you only modify the name of the fields?

    If yes, perhaps certain settings in itbl_50_1490_1 are incorrect. You can compare the property settings in the two tables. Perhaps you can find some differences.

     

    Here is another workaround which you may want to take as reference.

    Also, the problem can get fixed if you open the database in Access and do compact/repair.

     

    I hope this will be helpful for you.

    If you have any concerns, please feel free to let me know.

     

    Best Regards,


    Macy Dong [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 23, 2011 5:05 AM
  • Note that the table name could become a problem. If you insert the first table, twice, in a query, the query designer will assign the alias to the second reference which will be the same as the second table name. That may bring confusion, to the developper.
    • Marked as answer by Macy Dong Thursday, September 1, 2011 1:59 AM
    Tuesday, August 23, 2011 4:13 PM
    Moderator
  • I got the same error, but got around it by moving the primary key to another field before running the alter command, then switching it back afterwards.  Before anyone asks I wanted to do this on a pre-production version of a database to prepare it for go-live, and of course I deleted everything from the table first

    Bob

    Thursday, November 28, 2013 1:10 PM