Changing the Seed and Increment Value of Auto Number Columns

When a table contains a field with an AutoNumber data type, you can set a seed value and an increment value. The seed value is the initial value for the column, and the increment value is the number added to the seed value to obtain a new counter value for the next record. If not specified, both seed and increment values default to 1. You can use the DDL to change the seed and increment values of AutoNumber columns by using one of the following three statements:

ALTER TABLE Table_name ALTER COLUMN Column_name AUTOINCREMENT (seed, increment) ALTER TABLE Table_name ALTER COLUMN Column_name COUNTER (seed, increment)

ALTER TABLE Table_name ALTER COLUMN Column_name IDENTITY (seed, increment)

The example procedure in Hands-On 19-15 modifies the seed value of the existing AutoNumber column in the SchoolId column to start at 1000. Because we changed the SchoolId column's data type to the Text data type in one of the

Programming with the Jet Data Definition Language earlier hands-on exercises, you will modify the SchoolId column in the Sites.mdb file you created in Hands-On 19-3 at the beginning of this chapter.

© Hands-On 19-15: Changing the Start (Seed) Value of the AutoNumber Field

This hands-on uses the Sites.mdb database file and tblSchools table created in Hands-On 19-3.

1. In the Visual Basic Editor window, insert a new module.

2. In the module's Code window, enter the ChangeAutoNumber procedure as shown below.

3. Run the ChangeAutoNumber procedure.

4. Launch Microsoft Office Access with the Sites.mdb database and open the tblSchools table.

5. Enter a couple of new records in this table. In the YearEstablished field enter the date in the format mm/dd/yyyy. Note that the first new record is numbered 1000, the second 1001, the third 1002, and so on.

Sub ChangeAutoNumber()

Dim conn As ADODB.Connection Dim strDb As String Dim strConnect As String Dim strTable As String Dim strCol As String Dim intSeed As Integer

On Error GoTo ErrorHandler strDb = CurrentProject.Path & "\" & "Sites.mdb" strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" &_ "Data Source=" & strDb strTable = "tblSchools" strCol = "SchoolId" intSeed = 1000

Set conn = New ADODB.Connection conn.Open strConnect conn.Execute "ALTER TABLE " & strTable & _ " ALTER COLUMN " & strCol & _ " COUNTER (" & intSeed & ");"

ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

If Err.Number = -2147467259 Then

MsgBox "The database file cannot be located.", _

vbCritical, strDb Exit Sub

Else

MsgBox Err.Number & ":" & Err.Description

Part III

Resume ExitHere End If End Sub

0 0

Post a comment