AutoNumber Primary key in Database starts jumping suddenly by thousand places .

[abhi1214]

Hi,
The above image is of a table which I need to update according to a new data on daily basis. As you can see when I added two records at the last , the AutoNumber primary key of the table jumped by thousands . I have used the following query to update the table

db.Execute "INSERT INTO D_Counterparty (CPTY_ENTITY_ID,CPTY_DESC)"_ & "SELECT Ctpy_Entity_Id,Ctpy_Entity_Legal_Nm"_ & "FROM NewCU LEFT JOIN D_Counterparty ON D_Counterparty.CPTY_ENTITY_ID=NewCU.Ctpy_Entity_Id"

I never changed the AutoNumber from incremental to random and I have also tried re-seeding it but had no success. Also when I inserted the sample values using a sample table ,the AutoNumber was working fine . Here's that query

  db.Execute "INSERT INTO D_Counterparty (CPTY_ENTITY_ID,CPTY_DESC,) SELECT a,b, FROM sample"

I did not use join in this query , is that making a problem ?

I look forward to a reply as I think there should be some solution. Thank you in advance .

[orange]

This is incorrect syntax.

SELECT a,b, FROM sample

Autonumbers are unique --not sequential and not necessarily positive. You might find this useful.

Good luck.

[abhi1214]

Ya that was an typing error . But dont you think this is because of the join statement which would not insert any record unless the condition is matched but would create a AutoNumber everytime and discard it ? I got this logic as combination of the link you posted and query that I wrote.