title: Delete one record in MS Access database and update the rest by giving them new ascending number
body: <p>I have an Access DB file which contains one table and for example ten records in it. Every record has an ascending number but not as a key. It's just used by VB application to order the appearance of records. I know the way to delete every selected record through my application, but I can't find the way to update the rest nine records by giving them a new ascending number. For example, if I delete the 5th record, I want my app to count the remaining records and give them a new ascending number...</p>
  
  <p>I have done something like this, but of course it doesn't do the job...</p>
  
  <pre class="lang-vb prettyprint-override"><code>Imports System.Data.OleDb
  
  Public Class ApplicationForm
  
      Public Provider As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
      Public DataFile As String = "database.accdb"
      Public ConnString As String = Provider &amp; DataFile
      Public MyConnection As OleDbConnection = New OleDbConnection
  
      Private Sub AppsPaths_DelButton_Click(sender As Object, e As EventArgs) Handles AppsPaths_DelButton.Click
          MyConnection.ConnectionString = ConnString
          MyConnection.Open()
  
          Dim DBCount As Integer = AppsCount_Label2.Text
          Dim i As Integer = 0
          Dim strDelete As String
          Dim strUpdate As String
          strDelete = "DELETE FROM  ApplicationsPaths WHERE TagNum = '" &amp; AppToDel_Label.Text &amp; "'"
          Dim cmdDelete As OleDbCommand = New OleDbCommand(strDelete, MyConnection)
          Do
              strUpdate = "UPDATE [ApplicationsPaths] SET [TagNum] = '" &amp; i.ToString &amp; "'"
              i += 1
          Loop Until i = DBCount
          Dim cmdUpdate As OleDbCommand = New OleDbCommand(strUpdate, MyConnection)
          Try
              cmdDelete.ExecuteNonQuery()
              cmdDelete.Dispose()
              cmdUpdate.ExecuteNonQuery()
              cmdUpdate.Dispose()
              MyConnection.Close()
          Catch ex As Exception
              MsgBox(ex.Message)
          End Try
      End Sub
  
  End Class
  </code></pre>