Search code examples
javams-accessjdbcucanaccess

change AutoNumber values when inserting rows with UCanAccess

When using the executeUpdate method of UCanAccess, the system always chooses the row number for me. However, after I delete my rows and add new ones, the rows do not start from the beginning (e.g., I deleted 3 rows, then it marks the next 3 rows as 4, 5, and 6). Is there a way I can fix this or name the rows myself? Here is my code: 

statement.executeUpdate("INSERT INTO myDB2"+ " ( columnRows,  columnA, columnB)"
    + " VALUES ( "+rows+", "+a+", "+b+")");`
Solution

  • Resetting the next AutoNumber value

    After deleting rows you can reset the AutoNumber seed values by opening the database in Access and performing a "Compact and Repair Database" operation. UCanAccess is currently unable to reset the seed value itself.

    Inserting arbitrary values into an AutoNumber column

    Starting with version 4.0.2, UCanAccess is able to insert arbitrary positive values into an AutoNumber (Long Integer, Increment) column. For example, with a table named [Client]:

    ClientID - AutoNumber
    FirstName - Text(50)

    we can do

    Statement st = conn.createStatement();
st.execute("DISABLE AUTOINCREMENT ON Client");
st.execute("INSERT INTO Client (ClientID, FirstName) VALUES (123, 'Gord')");

    For more details, see the blog post here.

    Version 4.0.3 (not yet released) extends that capability to allow inserting arbitrary values that are less than or equal to zero.