SQL for INSERT new table entry

  • Thread starter Thread starter developer needs help
  • Start date Start date
D

developer needs help

Hi

I'm trying to do something which I think is fairly basic.
I have a table with the primary key (ID) set to autonumber
type.
If I use the datasheet view then ID automatically
increases.
That's fine, but I want the SQL for this automatic
insertion so that I can use it in code.
How can I do this without forcing a value to be input into
ID, e.g. INSERT INTO MYTABLE VALUE (1,"myname");
I would like the value 1 to be brought up automatically?
Please help.
Thanks.
 
developer needs help said:
Hi

I'm trying to do something which I think is fairly basic.
I have a table with the primary key (ID) set to autonumber
type.
If I use the datasheet view then ID automatically
increases.
That's fine, but I want the SQL for this automatic
insertion so that I can use it in code.
How can I do this without forcing a value to be input into
ID, e.g. INSERT INTO MYTABLE VALUE (1,"myname");
I would like the value 1 to be brought up automatically?
Please help.
Thanks.
Click to expand...

Just leave the field for the AutoNumber out of the INSERT statement and
Access will automatically apply the next AutoNumber value to it.
 
Hi

Do you mean the SQL is:
INSERT INTO MYTABLE VALUES (NAME);
and so I only entered the name field in the table MYTABLE

or
INSERT INTO MYTABLE VALUES (ID,NAME);
and then ignore entering ID.

I'm trying this out in Access at the moment and so running
it there since the code it will eventually run it has not
been written.

Hope that all makes sense.

Thanks
 
Hi

Do you mean the SQL is:
INSERT INTO MYTABLE VALUES (NAME);
and so I only entered the name field in the table MYTABLE

or
INSERT INTO MYTABLE VALUES (ID,NAME);
and then ignore entering ID.
Click to expand...

Neither of these is a valid Insert statement: you need to reference
the fields and then the values to be inserted into those fields in a
Values()-type Insert statement.

If you execute

INSERT INTO MyTable([Name]) VALUES("Jones");

it will automatically increment the ID and insert a record with
"Jones" in the Name field.

Note that Name is a reserved word, and Access may well get confused
whether you mean the field [Name] or the Name property of some form or
object; it's best to choose another fieldname!
 
Hi

I tried that but I get an error regarding key violations -
presumably the primary key.
I'm no longer using NAME.

Thanks.
-----Original Message-----
Hi

Do you mean the SQL is:
INSERT INTO MYTABLE VALUES (NAME);
and so I only entered the name field in the table MYTABLE

or
INSERT INTO MYTABLE VALUES (ID,NAME);
and then ignore entering ID.
Click to expand...

Neither of these is a valid Insert statement: you need to reference
the fields and then the values to be inserted into those fields in a
Values()-type Insert statement.

If you execute

INSERT INTO MyTable([Name]) VALUES("Jones");

it will automatically increment the ID and insert a record with
"Jones" in the Name field.

Note that Name is a reserved word, and Access may well get confused
whether you mean the field [Name] or the Name property of some form or
object; it's best to choose another fieldname!


.
Click to expand...
 
Hi

I tried that but I get an error regarding key violations -
presumably the primary key.
I'm no longer using NAME.
Click to expand...

What indexes (primary key or other) do you have defined on the table?
What relationships to other tables, if any? Could you post your actual
code?
 
Hi

Sorry for the delay.

I noticed that I'd been making a stupid error. It now
works.
Thanks for your help.
 
Back
Top