Changing the Auto Number

When you create a table in a Microsoft Office Access 2003 database, you can assign an AutoNumber data type to a primary key field manually using the Access user interface. The AutoNumber is a unique sequential number (incremented by 1) or a random number assigned by Microsoft Access whenever a new record is added to a table. Microsoft Office Access 2003 allows you to set the start and step value of auto-increment fields programmatically by using Jet 4.0 SQL statements (see Chapter 19 for more information).

The procedure in Hands-On 11-11 opens the ADO Recordset object based on the Shippers table located in the Northwind database, retrieves the last used AutoNumber value, and determines the current step (increment) value in effect.

© Hands-On 11-11: Changing the Value of an AutoNumber

1. In the Visual Basic Editor window, choose Insert | Module.

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

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub ChangeAutoNumber()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strSQL As String Dim beginNum As Integer Dim stepNum As Integer

Set conn = New ADODB.Connection conn.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Set rst = New ADODB.Recordset With rst

.CursorType = adOpenKeyset .LockType = adLockReadOnly .Open "Shippers", conn .MoveLast End With beginNum = rst(0)

rst.MovePrevious stepNum = beginNum - rst(0)

MsgBox "Last Auto Number Value = " & beginNum & vbCr & _ "Current Step Value = " & stepNum, vbInformation, _ "AutoNumber"

rst.Close conn.Close Set conn = Nothing End Sub

Creating and Manipulating Databases with ADO

0 0

Post a comment