none
Access oledb reset counter after deleting row RRS feed

  • Question

  • Hi!

    I want to be able to reset the id number column after I delete a row on ms office database.

    So for exaple  I have 1 column witch is named ID and three rows

    1

    2

    3

    I delete the row two and now left

    1

    3

    What I want is to be able to reset the counter so in this case would be

    1

    2

    I have a code but I don't know for what reason the alter table alter column counter (,) doesnt not work

    Any solution?

            Dim Sqlstmt4 As String = "ALTER TABLE tabela1 ALTER COLUMN Test COUNTER(1, 1) "
    
            Dim AccessConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=.\\tedhena.accdb;Jet OLEDB:Database Password=elektrometal;")
            Dim trans As OleDb.OleDbTransaction
            Try
                AccessConnection.Open()
                trans = AccessConnection.BeginTransaction
                Try
                    Dim AccessCommand As New System.Data.OleDb.OleDbCommand(Sqlstmt4, AccessConnection)
                    AccessCommand.Transaction = trans
                  
                    AccessCommand.CommandText = Sqlstmt4
                    AccessCommand.ExecuteNonQuery()
    
                    trans.Commit()
                Catch ex As Exception
                    trans.Rollback()
                    MsgBox("ok")
                End Try
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
            End Try

    Saturday, July 20, 2019 3:15 PM

Answers

  • Sorry, it appears MS-Access does not permit this, only SQL-Server. Here are parts revised to work with MS-Access and I did test it on a database table.

    Imports System.Data.OleDb
    
    Public Class DataOperations
    
        Private ConnectionString As String =
                    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NorthWind.accdb"
    
        Private ReadOnly _keyPositionFieldName As String = "RowPosition"
    
        ''' <summary>
        ''' Read all person records into a DataTable
        ''' </summary>
        ''' <returns></returns>
        Public Function ReadPeople() As DataTable
            Dim dt = New DataTable()
    
            Const selectStatement As String =
                      "SELECT Identifier, RowPosition, CompanyName, ContactName FROM Customers1;"
    
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
                Using cmd = New OleDbCommand() With {
                    .Connection = cn,
                    .CommandText = selectStatement
                    }
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
    
                    'dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
    
                End Using
            End Using
    
            Return dt
    
        End Function
        Public Sub RemoveRow(ByVal id As Integer)
            Const deleteStatement As String = "DELETE FROM Customers1 WHERE Identifier = @id"
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
                Using cmd = New OleDbCommand() With {
                    .Connection = cn,
                    .CommandText = deleteStatement
                    }
                    cmd.Parameters.AddWithValue("@id", id)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    
        Public Sub ResetIdentifiers()
    
            Dim identifierStatement = "SELECT Identifier FROM Customers1; 
    "
            Dim deleteStatement As String = "UPDATE Customers1 " &
                                           $"SET  {_keyPositionFieldName} = @MaxSurrogateKey  " &
                                            "WHERE Identifier = @Identifier"
    
    
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
    
                Using cmd = New OleDbCommand() With {
                    .Connection = cn,
                    .CommandText = identifierStatement
                    }
    
                    cn.Open()
                    Dim dt As New DataTable
                    dt.Load(cmd.ExecuteReader())
    
    
                    cmd.Parameters.Add(New OleDbParameter() With
                                          {
                                          .DbType = DbType.Int32,
                                          .ParameterName = "@MaxSurrogateKey"})
                    cmd.Parameters.Add(New OleDbParameter() With
                                          {
                                          .DbType = DbType.Int32,
                                          .ParameterName = "@Identifier"})
                    cmd.CommandText = deleteStatement
    
                    For index As Integer = 0 To dt.Rows.Count - 1
                        cmd.Parameters("@MaxSurrogateKey").Value = index + 1
                        cmd.Parameters("@Identifier").Value = dt.Rows(index).Field(Of Integer)("Identifier")
                        cmd.ExecuteNonQuery()
                    Next
    
                End Using
            End Using
    
        End Sub
    End Class

    .

    Public Class Form1
        Private ReadOnly _bindingSource As New BindingSource()
        Private ReadOnly _dataOperations As New DataOperations()
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            _bindingSource.DataSource = _dataOperations.ReadPeople()
            DataGridView1.DataSource = _bindingSource
        End Sub
        Private Sub ResetButton_Click(sender As Object, e As EventArgs) Handles ResetButton.Click
            If My.Dialogs.Question("Do you really want to manually reset row position for all records?") Then
                _dataOperations.ResetIdentifiers()
                CType(_bindingSource.DataSource, DataTable).ReorderPositionMarker
            End If
        End Sub
    
        Private Sub EraseCurrentRowButton_Click(sender As Object, e As EventArgs) Handles EraseCurrentRowButton.Click
            If _bindingSource.Current Is Nothing Then
                Return
            End If
            Dim id = CType(_bindingSource.Current, DataRowView).Row.Field(Of Integer)("Identifier")
            _bindingSource.RemoveCurrent()
            _dataOperations.RemoveRow(id)
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by ai1231 Monday, July 22, 2019 2:53 PM
    Monday, July 22, 2019 2:08 PM
    Moderator

All replies

  • Hello,

    Can this be done? Yes. Should this be done? No.

    There really is no good solid reason to do this. Instead, create a integer/number column and work against that column in regards to reordering so the numbers are in sequence but not a primary key. The only time one might consider doing this to a primary key is when a table was used for testing, no other reason.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, July 21, 2019 4:45 AM
    Moderator
  • Should I have to do this? Yes (because I don't have any linked tables) 

    Should I have to do this " create a integer/number column and work against that column in regards to reordering so the numbers are in sequence but not a primary key"? Yes this would work for my case.Can you show me an example? 


    • Edited by ai1231 Sunday, July 21, 2019 5:37 PM
    Sunday, July 21, 2019 5:37 PM
  • The following example comes from a code sample I wrote for Microsoft TechNet Wiki in C# for SQL-Server and have modified it for VB.NET and MS-Access.

    Add a new field named RowPosition of type Integer (Number). RowPosition is the fake primary key. You still remove records by primary key but afterwards run code such as below to reorder RowPosition so the numbers are in sequence.

    For this to work, data is loaded from MS-Access into a DataTable say into a DataGridView. After removal of a row call the following method, replace Person table with the name of your table and use your connection string.

    Public Sub ResetIdentifiers()
        Const deleteStatement As String = "UPDATE Person " &
                                          "SET @MaxSurrogateKey = RowPosition = @MaxSurrogateKey + 1 "
    
        Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
            Using cmd = New OleDbCommand() With {
                .Connection = cn,
                .CommandText = deleteStatement
                }
                cmd.Parameters.AddWithValue("@MaxSurrogateKey", 0)
                cn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
    
    End Sub

    Full solution

    Imports System.Data.OleDb
    Public Class DataOperations
    
        Private ConnectionString As String =
                    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NorthWind.accdb"
    
        Private ReadOnly _keyPositionFieldName As String = "RowPosition"
    
        ''' <summary>
        ''' Read all person records into a DataTable
        ''' </summary>
        ''' <returns></returns>
        Public Function ReadPeople() As DataTable
            Dim dt = New DataTable()
    
            Const selectStatement As String =
                      "SELECT Id,RowPosition  ,FirstName + ' ' + LastName AS FullName FROM Person"
    
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
                Using cmd = New OleDbCommand() With {
                    .Connection = cn,
                    .CommandText = selectStatement
                    }
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
    
                    dt.Columns("id").ColumnMapping = MappingType.Hidden
    
                End Using
            End Using
    
            Return dt
    
        End Function
        ''' <summary>
        ''' Remove row which is called from the "Erase data" button in the form
        ''' </summary>
        ''' <param name="id">Person primary key</param>
        Public Sub RemoveRow(ByVal id As Integer)
            Const deleteStatement As String = "DELETE FROM Person WHERE id = @id"
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
                Using cmd = New OleDbCommand() With {
                    .Connection = cn,
                    .CommandText = deleteStatement
                    }
                    cmd.Parameters.AddWithValue("@id", id)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    
        Public Sub ResetIdentifiers()
            Const deleteStatement As String = "UPDATE Person " &
                                          "SET @MaxSurrogateKey = RowPosition = @MaxSurrogateKey + 1 "
    
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
                Using cmd = New OleDbCommand() With {
                .Connection = cn,
                .CommandText = deleteStatement
                }
                    cmd.Parameters.AddWithValue("@MaxSurrogateKey", 0)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
    
        End Sub
    
        ''' <summary>
        ''' Reorder all keys identified with _keyPositionFieldName
        ''' </summary>
        ''' <param name="pDataTable"></param>
        Public Sub UpdateAllRowsPosition(ByVal pDataTable As DataTable)
    
            Dim updateStatement = $"UPDATE Person SET {_keyPositionFieldName} =" & $" @{_keyPositionFieldName} WHERE id = @PersonId"
    
    
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
                Using cmd = New OleDbCommand() With {.Connection = cn}
    
                    cmd.CommandText = updateStatement
                    cmd.Parameters.Add(New OleDbParameter() With {
                                          .ParameterName = $"@{_keyPositionFieldName}",
                                          .DbType = DbType.Int32
                                          })
    
                    cmd.Parameters.Add(New OleDbParameter() With {
                                          .ParameterName = "@PersonId",
                                          .DbType = DbType.Int32
                                          })
    
    
                    cn.Open()
    
                    Dim newPosition As Integer = 1
    
                    For rowIndex = 0 To pDataTable.Rows.Count - 1
                        ' set new row position
                        cmd.Parameters($"@{_keyPositionFieldName}").Value = newPosition
    
                        cmd.Parameters("@PersonId").Value = pDataTable.Rows(rowIndex).Field(Of Integer)("Id")
    
                        cmd.ExecuteNonQuery()
                        newPosition += 1
                    Next
    
                End Using
            End Using
        End Sub
    End Class

    Form

    Public Class Form1
        Private ReadOnly _bindingSource As New BindingSource()
        Private ReadOnly _dataOperations As New DataOperations()
        Public Sub New()
            InitializeComponent()
        End Sub
    
        Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
            _bindingSource.DataSource = _dataOperations.ReadPeople()
            DataGridView1.DataSource = _bindingSource
            AddHandler Me.FormClosing, AddressOf Form1_FormClosing
        End Sub
        ''' <summary>
        ''' Perform update for RowPosition field on all existing rows
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As FormClosingEventArgs)
            CType(_bindingSource.DataSource, DataTable).ReorderPositionMarker()
            _dataOperations.UpdateAllRowsPosition((CType(_bindingSource.DataSource, DataTable)))
        End Sub
        Private Sub EraseCurrentRowButton_Click(ByVal sender As Object, ByVal e As EventArgs)
            If _bindingSource.Current Is Nothing Then
                Return
            End If
            Dim id = CType(_bindingSource.Current, DataRowView).Row.Field(Of Integer)("id")
            _bindingSource.RemoveCurrent()
            _dataOperations.RemoveRow(id)
    
        End Sub
    
        Private Sub ResetManualKeysButton_Click(ByVal sender As Object, ByVal e As EventArgs)
            If My.Dialogs.Question("Do you really want to manually reset row position for all records?") Then
                _dataOperations.ResetIdentifiers()
            End If
        End Sub
    
    End Class
    

    Wrapper for message box.

    Namespace My
    
        <ComponentModel.EditorBrowsable(ComponentModel.EditorBrowsableState.Never)>
        Partial Friend Class _Dialogs
            Public Function Question(message As String) As Boolean
                Return (MessageBox.Show(message, My.Application.Info.Title, MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = MsgBoxResult.Yes)
            End Function
            ''' <summary>
            ''' Shows text in dialog with information icon
            ''' </summary>
            ''' <param name="message">Message to display</param>
            ''' <remarks></remarks>
            Public Sub InformationDialog(message As String)
                MessageBox.Show(message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Sub
        End Class
    
        <HideModuleName()>
        Friend Module SpecialFormsDialogs
            Private instance As New ThreadSafeObjectProvider(Of _Dialogs)
            ReadOnly Property Dialogs() As _Dialogs
                Get
                    Return instance.GetInstance()
                End Get
            End Property
        End Module
    End Namespace


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, July 21, 2019 6:39 PM
    Moderator
  • I get an error on this line

      CType(_bindingSource.DataSource, DataTable).ReorderPositionMarker()


    Error BC30456 'ReorderPositionMarker' is not a member of 'DataTable'.
    Monday, July 22, 2019 11:48 AM
  • I get an error on this line

      CType(_bindingSource.DataSource, DataTable).ReorderPositionMarker()


    Error BC30456 'ReorderPositionMarker' is not a member of 'DataTable'.
    Public Module DataTableExtensions
        <Runtime.CompilerServices.Extension>
        Public Sub ReorderPositionMarker(ByVal sender As DataTable)
            If Not sender.Columns.Contains("RowPosition") Then
                Return
            End If
    
            Dim indexer = 1
    
            Dim rows = sender.AsEnumerable().Where(Function(row) row.RowState <> DataRowState.Deleted).ToList()
    
            For rowIndex = 0 To rows.Count - 1
                rows(rowIndex).SetField("RowPosition", indexer)
                indexer += 1
            Next
            sender.AcceptChanges()
        End Sub
    End Module



    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 22, 2019 12:10 PM
    Moderator
  • I get "Cannot update '@MaxSurrogateKey'; field not updateable" error

    When I run this .What could be wrong? 

    I didn't changed anything in your code.I created a new table named=Person,With columns(ID-witch is primary key,RowPosition ,FirstName,LastName)

     Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            If My.Dialogs.Question("Do you really want to manually reset row position for all records?") Then
                _dataOperations.ResetIdentifiers()
            End If
        End Sub

    Monday, July 22, 2019 1:10 PM
  • Sorry, it appears MS-Access does not permit this, only SQL-Server. Here are parts revised to work with MS-Access and I did test it on a database table.

    Imports System.Data.OleDb
    
    Public Class DataOperations
    
        Private ConnectionString As String =
                    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=NorthWind.accdb"
    
        Private ReadOnly _keyPositionFieldName As String = "RowPosition"
    
        ''' <summary>
        ''' Read all person records into a DataTable
        ''' </summary>
        ''' <returns></returns>
        Public Function ReadPeople() As DataTable
            Dim dt = New DataTable()
    
            Const selectStatement As String =
                      "SELECT Identifier, RowPosition, CompanyName, ContactName FROM Customers1;"
    
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
                Using cmd = New OleDbCommand() With {
                    .Connection = cn,
                    .CommandText = selectStatement
                    }
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
    
                    'dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
    
                End Using
            End Using
    
            Return dt
    
        End Function
        Public Sub RemoveRow(ByVal id As Integer)
            Const deleteStatement As String = "DELETE FROM Customers1 WHERE Identifier = @id"
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
                Using cmd = New OleDbCommand() With {
                    .Connection = cn,
                    .CommandText = deleteStatement
                    }
                    cmd.Parameters.AddWithValue("@id", id)
                    cn.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    
        Public Sub ResetIdentifiers()
    
            Dim identifierStatement = "SELECT Identifier FROM Customers1; 
    "
            Dim deleteStatement As String = "UPDATE Customers1 " &
                                           $"SET  {_keyPositionFieldName} = @MaxSurrogateKey  " &
                                            "WHERE Identifier = @Identifier"
    
    
            Using cn = New OleDbConnection() With {.ConnectionString = ConnectionString}
    
                Using cmd = New OleDbCommand() With {
                    .Connection = cn,
                    .CommandText = identifierStatement
                    }
    
                    cn.Open()
                    Dim dt As New DataTable
                    dt.Load(cmd.ExecuteReader())
    
    
                    cmd.Parameters.Add(New OleDbParameter() With
                                          {
                                          .DbType = DbType.Int32,
                                          .ParameterName = "@MaxSurrogateKey"})
                    cmd.Parameters.Add(New OleDbParameter() With
                                          {
                                          .DbType = DbType.Int32,
                                          .ParameterName = "@Identifier"})
                    cmd.CommandText = deleteStatement
    
                    For index As Integer = 0 To dt.Rows.Count - 1
                        cmd.Parameters("@MaxSurrogateKey").Value = index + 1
                        cmd.Parameters("@Identifier").Value = dt.Rows(index).Field(Of Integer)("Identifier")
                        cmd.ExecuteNonQuery()
                    Next
    
                End Using
            End Using
    
        End Sub
    End Class

    .

    Public Class Form1
        Private ReadOnly _bindingSource As New BindingSource()
        Private ReadOnly _dataOperations As New DataOperations()
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            _bindingSource.DataSource = _dataOperations.ReadPeople()
            DataGridView1.DataSource = _bindingSource
        End Sub
        Private Sub ResetButton_Click(sender As Object, e As EventArgs) Handles ResetButton.Click
            If My.Dialogs.Question("Do you really want to manually reset row position for all records?") Then
                _dataOperations.ResetIdentifiers()
                CType(_bindingSource.DataSource, DataTable).ReorderPositionMarker
            End If
        End Sub
    
        Private Sub EraseCurrentRowButton_Click(sender As Object, e As EventArgs) Handles EraseCurrentRowButton.Click
            If _bindingSource.Current Is Nothing Then
                Return
            End If
            Dim id = CType(_bindingSource.Current, DataRowView).Row.Field(Of Integer)("Identifier")
            _bindingSource.RemoveCurrent()
            _dataOperations.RemoveRow(id)
        End Sub
    End Class


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by ai1231 Monday, July 22, 2019 2:53 PM
    Monday, July 22, 2019 2:08 PM
    Moderator
  • Code is working now and I was able to understand what this code does.

    Thank you for your time.

    Monday, July 22, 2019 2:54 PM