Changing the Value of an AutoNumber : Column Type « Application « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Application
3. Column Type

Changing the Value of an AutoNumber

 
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 & _
       "\mydb.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

 

Related examples in the same category