By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,373 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,373 IT Pros & Developers. It's quick & easy.

INSERT INTO with AutoNumber

P: n/a
Lyn
Hi,
Is there an "easy" way to write a full record (all fields) using "INSERT
INTO..." into a table which has an AutoNumber field?

Normally, to write a full new record I would use:

INSERT INTO [tblTable] VALUES (Value1, Value2, ...);

If (say) the first field (Value1) is an AutoNumber, what value can you put
that will preserve the auto-increment function?

NULL doesn't work because the field is a Long.

Omitting the field (via a comma placemarker) doesn't work either.

0 (zero) works in that the record will be written, but the AutoNumber field
will be set to 0 (contrary to what I have read that there is no way to
override an AutoNumber field!).

What does work is to specify all of the field names (except the AutoNumber
field -- leaving it out altogether allows the auto-increment function to
work normally). For example (Field1 is AutoNumber):

INSERT INTO [tblTable] (Field2, Field3,...) VALUES (Value2, Value3,...);

This is OK (and it is what I have done), but if you have a record with a
lot of fields, it means a lot of extra typing (and chances for errors) to
enter all of the field names except for the one which auto-increments. Is
there a simpler way to do this? (Such as a keyword "value" that says
"ignore me")

Or perhaps you think that it is good practice to list the field names in
the syntax anyway (even when there is no AutoNumber field and regardless of
how many fields there are)?

Cheers,
Lyn.
May 16 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Lyn wrote:
Hi,
Is there an "easy" way to write a full record (all fields) using
"INSERT INTO..." into a table which has an AutoNumber field?
[snip]
What does work is to specify all of the field names (except the
AutoNumber field -- leaving it out altogether allows the
auto-increment function to work normally). [snip]
Listing all field names except the AutoNumber field is the only way to do what
you want.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 16 '07 #2

P: n/a
Lyn
On Wed, 16 May 2007 11:30:07 GMT, Rick Brandt wrote:
Lyn wrote:
>Hi,
Is there an "easy" way to write a full record (all fields) using
"INSERT INTO..." into a table which has an AutoNumber field?
[snip]
>What does work is to specify all of the field names (except the
AutoNumber field -- leaving it out altogether allows the
auto-increment function to work normally). [snip]

Listing all field names except the AutoNumber field is the only way to do what
you want.
Thanks. I suspected as much :-(

Lyn.
May 16 '07 #3

P: n/a
Lyn wrote:
On Wed, 16 May 2007 11:30:07 GMT, Rick Brandt wrote:
>Lyn wrote:
>>Hi,
Is there an "easy" way to write a full record (all fields) using
"INSERT INTO..." into a table which has an AutoNumber field?
[snip]
>>What does work is to specify all of the field names (except the
AutoNumber field -- leaving it out altogether allows the
auto-increment function to work normally). [snip]

Listing all field names except the AutoNumber field is the only way
to do what you want.

Thanks. I suspected as much :-(

Lyn.
It is fairly easy to write a code routine that you can pass the name of a
table or query to and have it print all of the field names comma delimited
to the debug window where they can then easily be copied and pasted into
your query.

I created such a utility because I often have to work with tables on our
AS400 legacy system where the tables are definitely NOT normalized so having
a table with 50 or even 150 fields is not uncommon.

You can also build a simple SELECT query in the QBE grid and then switch to
SQL view and copy all the field names from there.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 16 '07 #4

P: n/a
Lyn
On Wed, 16 May 2007 13:20:19 GMT, Rick Brandt wrote:
>
It is fairly easy to write a code routine that you can pass the name of a
table or query to and have it print all of the field names comma delimited
to the debug window where they can then easily be copied and pasted into
your query.

I created such a utility because I often have to work with tables on our
AS400 legacy system where the tables are definitely NOT normalized so having
a table with 50 or even 150 fields is not uncommon.

You can also build a simple SELECT query in the QBE grid and then switch to
SQL view and copy all the field names from there.
Rick, thanks for the hint. Fortunately in my current case, the table had
only four fields so it was not a big deal. However, it occurred to me that
this could be a big problem if the table had a large number of fields.

Cheers,
Lyn.
May 16 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.