none
MS Access Auto ID is starting at 2 vs 1 RRS feed

  • Question

  • MS Access Auto ID is starting at 2 vs 1

    I have a database that I have a tamplete structured database that after data is imported, I appened the new table to the template.

    The database is copied everytime so there are no records in it.

    When I run the append query the AutoID starts with 2, however when I copy the data base manually and copied the table and manually enter data, it starts at 1.

    Is there a way to have this starting at 1 instead of 2?I have compacted and repair. I have re-created the table, and even re-created the Database and table (No copying manually entering everything.)

    I have never seen this before. I am using access 2013.

    Monday, July 27, 2015 9:20 PM

Answers

  • I would suspect that you may have began entering some data into the table and aborted the record, thus causing the autonumber to increment.  If you want to restart your table at 1, you can compact and repair the database while the table is empty of any records.  You can also set the seed.  Allen Brown provides a method for doing this at the following link:

    http://www.allenbrowne.com/ser-26.html


    • Edited by RunningManHD Monday, July 27, 2015 9:49 PM
    • Marked as answer by Broggy69 Thursday, July 30, 2015 3:09 PM
    Monday, July 27, 2015 9:48 PM

All replies

  • Of course, in principle the values of autonumber IDs isn't supposed to matter, so you're not supposed to care if you don't start at 1.  However, I have to admit that I find autonumber inconsistencies (in a new or "clean" databases) ... untidy.

    If this autonumber oddity is happening only when you run your append query, I have to wonder if there's something about that query that is causing it.  Could you post the SQL of the query, and any other queries or code that touch the table before that?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, July 27, 2015 9:38 PM
  • I would suspect that you may have began entering some data into the table and aborted the record, thus causing the autonumber to increment.  If you want to restart your table at 1, you can compact and repair the database while the table is empty of any records.  You can also set the seed.  Allen Brown provides a method for doing this at the following link:

    http://www.allenbrowne.com/ser-26.html


    • Edited by RunningManHD Monday, July 27, 2015 9:49 PM
    • Marked as answer by Broggy69 Thursday, July 30, 2015 3:09 PM
    Monday, July 27, 2015 9:48 PM
  • Of course, in principle the values of autonumber IDs isn't supposed to matter, so you're not supposed to care if you don't start at 1.  However, I have to admit that I find autonumber inconsistencies (in a new or "clean" databases) ... untidy.

    If this autonumber oddity is happening only when you run your append query, I have to wonder if there's something about that query that is causing it.  Could you post the SQL of the query, and any other queries or code that touch the table before that?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    I am using Visual Basic for the append query, below is the code.

    xinName is the file name that VB gets from a for loop looking for all CSV file. So the code repeats for each csv file.

    tTab is just the extension if it is a txt vs CSV. txt is a tab delimited. The folder where the files exist only has the CSV or Tab delimited.

    Dim cmd As OleDbCommand =
                       New OleDbCommand(
                       "CREATE PROC qAppend_" & xinName & "_" & tTab & " as INSERT INTO [7320EN] select " &
                       xinName & "_" & tTab & ".* FROM [" & xinName & "_" & tTab & "]", con)

    I then drop the table after the query runs, since I do not need it again after it appends.

    Dim cmdd As OleDbCommand = New OleDbCommand("DROP Table qAppend_" & xinName _
                                                                & "_" & tTab, con)

    Monday, July 27, 2015 9:58 PM
  • I would suspect that you may have began entering some data into the table and aborted the record, thus causing the autonumber to increment.  If you want to restart your table at 1, you can compact and repair the database while the table is empty of any records.  You can also set the seed.  Allen Brown provides a method for doing this at the following link:

    http://www.allenbrowne.com/ser-26.html



    I have compact and repaired. I have created the data base from scratch, entering fields, and field information manually, no data entered, and still starts at 2.
    Monday, July 27, 2015 9:59 PM
  • I will add, I print the ID on a form that gets mailed out, it is the sequence number, so it must start at 1.
    Monday, July 27, 2015 10:01 PM
  • I am using Visual Basic for the append query, below is the code.

    xinName is the file name that VB gets from a for loop looking for all CSV file. So the code repeats for each csv file.

    tTab is just the extension if it is a txt vs CSV. txt is a tab delimited. The folder where the files exist only has the CSV or Tab delimited.

    Dim cmd As OleDbCommand =
                       New OleDbCommand(
                       "CREATE PROC qAppend_" & xinName & "_" & tTab & " as INSERT INTO [7320EN] select " &
                       xinName & "_" & tTab & ".* FROM [" & xinName & "_" & tTab & "]", con)

    I then drop the table after the query runs, since I do not need it again after it appends.

    Dim cmdd As OleDbCommand = New OleDbCommand("DROP Table qAppend_" & xinName _
                                                                & "_" & tTab, con)

    So you're not actually using Access to write to the table, but instead are using OLEDB?  Are you at any point opening the table in Access, before or after executing the append query shown above?

    You didn't include the code that actually executes the append query.  It probably won't tell us anything, but could we see that, too?

    Does the table have any contraints -- unique keys, for example -- that could be causing a records to be dropped? Does the output table have the same number of records as the input?

    Does the source table have a header row?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, July 28, 2015 12:11 AM
  • I am using Visual Basic for the append query, below is the code.

    xinName is the file name that VB gets from a for loop looking for all CSV file. So the code repeats for each csv file.

    tTab is just the extension if it is a txt vs CSV. txt is a tab delimited. The folder where the files exist only has the CSV or Tab delimited.

    Dim cmd As OleDbCommand =
                       New OleDbCommand(
                       "CREATE PROC qAppend_" & xinName & "_" & tTab & " as INSERT INTO [7320EN] select " &
                       xinName & "_" & tTab & ".* FROM [" & xinName & "_" & tTab & "]", con)

    I then drop the table after the query runs, since I do not need it again after it appends.

    Dim cmdd As OleDbCommand = New OleDbCommand("DROP Table qAppend_" & xinName _
                                                                & "_" & tTab, con)

    So you're not actually using Access to write to the table, but instead are using OLEDB?  Are you at any point opening the table in Access, before or after executing the append query shown above?

    You didn't include the code that actually executes the append query.  It probably won't tell us anything, but could we see that, too?

    Does the table have any contraints -- unique keys, for example -- that could be causing a records to be dropped? Does the output table have the same number of records as the input?

    Does the source table have a header row?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    I do have code to import the CSV/Tab files before they are appended.

    Here is the full import code, and qAppend code.

    Dim cmd As OleDbCommand =
                       New OleDbCommand(
                       "CREATE PROC qAppend_" & xinName & "_" & tTab & " as INSERT INTO [7320EN] select " &
                       xinName & "_" & tTab & ".* FROM [" & xinName & "_" & tTab & "]", con)
    
                    'Creates append query
                    con.Open()
    
                    Try
                        cmd.ExecuteNonQuery()
                    Catch ex As System.Exception
                        MsgBox(ex.ToString)
                    End Try
    
                    con.Close()
    Dim aConStr As String = WF & "\VDD" & aCon
            Dim sConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
            Dim cmdT As OleDbCommand = New OleDbCommand()
            Dim con As OleDbConnection = New OleDbConnection(sConStr + aConStr)
    
    Dim conp As String = "SELECT * INTO [" & xinName & "_" & tTab & "] FROM [Text;DATABASE="
    
                    Dim Comm_1 As New OleDbCommand(conp & WF & "\VDD\Data\" &
                                                   "\;HDR=No].[" & inName & "]", con)
    
                    'Inport Data in to Access
                    con.Open()
    
                    Try
                        Comm_1.ExecuteNonQuery()
                    Catch ex As System.Exception
                        MsgBox(ex.ToString)
                    End Try
    
                    con.Close()
    Each CSV/Tab file has different number of headers, so I append them into a table with all possible fields for efficiency.

    Tuesday, July 28, 2015 3:12 PM
  • I found one fix using the variable software to subtract 1 from the field, would prefer have the data correct, but the printing will be correct.
    Tuesday, July 28, 2015 3:44 PM
  • I dont know the answer to your question but if you are expecting autonumbers to start at 1 and increment by 1 each time you may be sadly disappointed. I have seen several instances where autonumbers jumped out of sequence for no aparrent reason and I have even seen autonumbers decrement instead of increment. They are guaranteed to be unique and that is all.
    Thursday, July 30, 2015 12:28 AM
  • I dont know the answer to your question but if you are expecting autonumbers to start at 1 and increment by 1 each time you may be sadly disappointed. I have seen several instances where autonumbers jumped out of sequence for no aparrent reason and I have even seen autonumbers decrement instead of increment. They are guaranteed to be unique and that is all.

    I did re-create the database on my personal laptop and that seems to be working. I copied that to the site desktop and everything is still working. While I understand it is not a guarantee, but so far now it is working. I have several others jobs similar working without incident.
    Thursday, July 30, 2015 3:09 PM