Menu
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?
559
In the Navigation Pane, right-click the table to which you want to add the primary key, and click Design View. Tip: If you don't see the Navigation Pane, press F11 to display it. Locate the first available empty row in the table design grid. In the Data Type field, click the drop-down arrow and click AutoNumber.
AutoNumber is a type of data used in Microsoft Access tables to generate an automatically incremented numeric counter. It may be used to create an identity column which uniquely identifies each record of a table. Only one AutoNumber is allowed in each table. The data type was called Counter in Access 2.0.
The AutoNumber field will automatically add a new, unique number to each of the records in a table. If a field appears in more than one table and is a primary key in one table, it is called a foreign key in the other table (because it is another table's primary key).
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.
60
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.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
