Cannot add new records to table with auto number primary key

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Have just started experiencing some very strange behaviour in Access 2016. This is happening even on a brand new database with one table.

E.g. I have set up a table with an ID field as primary key and data type of autonumber along with a couple of other short text fields. I enter some data just fine, say three rows. Then I run some SQL via VBA with the intention of creating a new data item with the same data as the row with ID 1:
VBA Code:
Dim sqlNewVersion As String
sqlNewVersion = "INSERT INTO [dataItems] SELECT * from [dataItems] WHERE [ID] = 1"    
DoCmd.RunSQL sqlNewVersion

When executing the code, I get an error saying that the row was not inserted because of a primary key duplication.

When I then return to the table and try to enter a new data row manually, I see that the autonumber is setting to '2' which is a duplication of the second data row and therefore won't allow me to enter any new rows of data. I've never seen this before. Any ideas?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
Yes, you cannot add a new record in that way, because you cannot add the Auto number field from the old record from the old record since it already exists, and it must be unique.
So, just add all the other fields, except for the AutoNumber field, and it should work.

For example, let's say the Autonumber field was [ID] and the other fields were [Field1], [Field2], and [Field3]. Then this code should work:
VBA Code:
Dim sqlNewVersion As String
sqlNewVersion = "INSERT INTO [dataItems] SELECT [Field1], [Field2], [Field3] from [dataItems] WHERE [ID] = 1"    
DoCmd.RunSQL sqlNewVersion
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Ahhh, thanks! I knew there'd be a reason for it. Taking a slightly different approach and doing it through an append query now, but it's working!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,912
Messages
5,638,948
Members
417,061
Latest member
matd74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top