Reset an Access autonumber field in C#

[autonumber]

The post Get an autonumber value that was just inserted in C# explains how to use autonumbered fields. This post explains how you can reset the value used by an autonumber field.

To reset an autonumber field in an Access database, execute an SQL statement similar to the following:

ALTER TABLE tablename
    ALTER COLUMN fieldname AUTOINCREMENT(startvalue, increment)

Where:

  • tablename is the name of the table
  • fieldname is the name of the autonumber field in the table
  • startvalue is the next value that should be used for the field
  • increment is the amount (default 1) by which the values should be incremented each time you use a value

The following code shows how this example resets the autonumber value for the Students table’s StudentId field.

private void btnReset_Click(object sender, EventArgs e)
{
    // Create the command.
    OleDbCommand cmd = new OleDbCommand(
        "ALTER TABLE Students ALTER " +
            "COLUMN StudentId AUTOINCREMENT(" +
            txtAutoNumberStart.Text + ",1)",
        Conn);

    // Execute the command.
    Conn.Open();
    cmd.ExecuteNonQuery();

    // Close the connection.
    cmd.Dispose();
    Conn.Close();

    MessageBox.Show("Ok");
}

This code simply executes the previous SQL statement using the value that you entered in the txtAutoNumberStart TextBox.

You need to exercise some caution when you use this statement. The database does not automatically check that new auto-generated values are not already in the table. For example, suppose you insert some records with autonumber values 1, 2, 3, …, 10. You then reset the autonumber to start over at 1. When you insert a new record, the autonumber field will try to use the value 1 again. If the field must be unique, then that causes an exception. Often autonumber fields are used to generate unique values such as student IDs so that will be a problem.

The morale is, you probably shouldn’t reset the autonumber value to a smaller value than it already has, at least unless you’ve looked over the values in the database and you’re sure you won’t have any collisions.


Download Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in ADO.NET, database and tagged , , , , , , , , , , , , . Bookmark the permalink.

2 Responses to Reset an Access autonumber field in C#

  1. purposeless says:

    Does not work…
    “System.Data.OleDb.OleDbException: “Syntax error in field definition.””

    • RodStephens says:

      It works for me. Did you download the example or just copy and paste pieces of the post into your program?

      I’m not sure why it wouldn’t work for you. Perhaps you have a different database engine? Or maybe a different version?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.