Using Autonumbering in Access - INSERT statements

Question

I'm having trouble running an INSERT statement where there's an autonumber as the PK field. I have an Auto-incrementing long as the Primary Key, and then 4 fields of type double; and yet Access (using ADO) seems to want five values for the insert statement.

INSERT INTO [MY_TABLE] VALUES (1.0, 2.0, 3.0, 4.0);
>> Error: Number of query values and destinations fields are not the same.

INSERT INTO [MY_TABLE] VALUE (1, 1.0, 2.0, 3.0, 4.0);
>> Success!!

How do I use Autonumbering to actually autonumber?

Microphone

Answer 1

If you do not want to provide values for all columns that exists in your table, you've to specify the columns that you want to insert. (Which is logical, otherwise how should access, or any other DB, know for which columns you're providing a value)?

So, what you have to do is this:

INSERT INTO MyTable ( Column2, Column3, Column4) VALUES ( 1, 2, 3 )

Also , be sure that you omit the Primary Key column (which is the autonumber field). Then, Access will set it to the next value by itself.

You can then retrieve the primary-key value of the newly inserted record by executing a

SELECT @@identity FROM MyTable

statement.

Grownup

Thank you so much! You not only answered my question, but you answered the next question I was in the process of writing: how to extract the autonumber after the insert. Cheers! – Microphone

Answer 2

Mention the column names in your query as you are providing only 4 values whereas you have 5 columns in that table. Database need to know the value you providing is for which column.

Magnolia

Answer 3

My understanding though is that if you are using SQL Server or similar and there are triggers which add additional records the @@IDENTITY may be that of the other additional records.

Pokpoke

Answer 4

Just Leave the Auto Number Out of the Insert Query. It will populate on it's own.

There is an ID field before the ProjectID

INSERT INTO ProjectRiskAssessment 
( ProjectID
, RiskClass
, RiskElement
, RiskAttribute
, RiskQuestion
, RiskScale
, RiskStatus
, RiskSeverity
, RiskProbability
, RiskResponse )
SELECT 
 1 AS Expr2
, PullRiskAssessmentQuestions.RiskClass
, PullRiskAssessmentQuestions.RiskElement
, PullRiskAssessmentQuestions.RiskAttribute
, PullRiskAssessmentQuestions.RiskQuestion
, '0' AS Expr3
, 'Open' AS Expr4
, '1' AS Expr5
, '1' AS Expr6
, ' ' AS Expr7
FROM PullRiskAssessmentQuestions;

Trihedral

Quite not understandable answer, maybe try be more specific to what other person asked and not use code you have. – Brig