The post Get an auto-number value that was just inserted into an Access database in C# explains how to use auto-numbered fields. This post explains how you can reset the value used by an auto-number field.

To reset an auto-number field in an Access database, execute a 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 auto-number 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 program resets the auto-number 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 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 auto-number values 1, 2, 3, ..., 10. You then reset the auto-number to start over at 1. When you insert a new record, the auto-number field will try to use the value 1 again. If the field must be unique, then that causes an exception. Often auto-number 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 auto-number 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.