none
reset the Auto number field RRS feed

  • Question

  • reset the Auto number field  in Access  database

    I have an automatic numbering field in the Access database
    When the deletion is caused by a defect in the columns and some of them disappear and do not appear in the order I want to restore the automatic numbering

    code  not work

     con.Open()
            On Error Resume Next
            Using cmd As New OleDbCommand("DROP Index PrimaryKey ON purchcard_tb", con)
                cmd.ExecuteNonQuery()
            End Using
            Using cmd As New OleDbCommand("ALTER TABLE purchcard_tb DROP COLUMN ID_purchcard", con)
                cmd.ExecuteNonQuery()
            End Using
    
            Using cmd As New OleDbCommand("alter table purchcard_tb ADD ID_purchcard int identity", con)
                cmd.ExecuteNonQuery()
            End Using
    
            Using cmd As New OleDbCommand("ALTER TABLE purchcard_tb ADD PRIMARY KEY (ID_purchcard)", con)
                cmd.ExecuteNonQuery()
            End Using
    
            con.Close()

    Tuesday, January 23, 2018 10:35 AM

Answers

  • Hello,

    It would seem all you need is something like the following, if more is needed then we are dealing with a compact and repair.

    Anyways here is a quicky which you can tweak as needed.

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
    
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = Path.Combine(AppDomain.
            CurrentDomain.BaseDirectory, "CustomersMain.accdb")
        }
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim nextIdentifier As Integer = 12
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = $"ALTER TABLE Customers ALTER COLUMN Identifier COUNTER({nextIdentifier},1)"
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    Console.WriteLine($"Identifier next value is {nextIdentifier}")
                End Using
            End Using
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 23, 2018 11:15 AM
    Moderator
  • Hi Monemas,

    To reset an autonumber field in an Access database, execute an  statement similar to the following:

     ALTER TABLE tablename ALTER COLUMN fieldname AUTOINCREMENT(startvalue, increment)
    tablename is the name of the table
    fieldname is the name of the autonumber field in the table
    startvalue is the next value that should be used for the field
    increment is the amount (default 1) by which the values should be incremented each time you use a value

    Best  Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by monemas Friday, January 26, 2018 10:47 PM
    Wednesday, January 24, 2018 3:25 AM
    Moderator

All replies

  • Hello,

    It would seem all you need is something like the following, if more is needed then we are dealing with a compact and repair.

    Anyways here is a quicky which you can tweak as needed.

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
    
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = Path.Combine(AppDomain.
            CurrentDomain.BaseDirectory, "CustomersMain.accdb")
        }
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim nextIdentifier As Integer = 12
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = $"ALTER TABLE Customers ALTER COLUMN Identifier COUNTER({nextIdentifier},1)"
                    cn.Open()
                    cmd.ExecuteNonQuery()
                    Console.WriteLine($"Identifier next value is {nextIdentifier}")
                End Using
            End Using
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 23, 2018 11:15 AM
    Moderator
  • Hi Monemas,

    To reset an autonumber field in an Access database, execute an  statement similar to the following:

     ALTER TABLE tablename ALTER COLUMN fieldname AUTOINCREMENT(startvalue, increment)
    tablename is the name of the table
    fieldname is the name of the autonumber field in the table
    startvalue is the next value that should be used for the field
    increment is the amount (default 1) by which the values should be incremented each time you use a value

    Best  Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by monemas Friday, January 26, 2018 10:47 PM
    Wednesday, January 24, 2018 3:25 AM
    Moderator