CODEWITHSUNDEEP
sqlms-accessinsertadoautonumber
Smashery
Smashery

Reputation: 59653

Using Autonumbering in Access - INSERT statements

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?

Upvotes: 7

Views: 39468

Answers (4)

user3310546
user3310546

Reputation: 3

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;

Upvotes: 0

Tony Toews
Tony Toews

Reputation: 7882

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.

Upvotes: 0

Bhushan Bhangale
Bhushan Bhangale

Reputation: 10987

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.

Upvotes: 1

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

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.

Upvotes: 19

Related Questions