Autonumber and INSERT

[Guest]

Am I right in thinking that if you want to use an INSERT statement to add
records to a table with an autonumber field, you can't use:

INSERT into xxx VALUES(value1, value2 etc)

unless you want to supply the autonumber values yourself and you have to use:

INSERT into xxx (field1, field2 etc ) VALUES (value1, value2 etc) instead?

This doesn't seem very user-friendly if you have lots of required fields in
your table.

 

[aaron.kempf]

Access Data Projects are a _LOT_ better than this; you can INSERT and
then use @@IDENTITY in order to pull the new autonumber

sorry; i've been saying this for 5 years

but MDB is NOT competitive with ADP; ADP runs away with the crown

-Aaron

 

[Bill Mosca, MS Access MVP]

This has nothing to do with being user friendly. That's how SQL works. If
you do not specify the "into" fields, the values go into the first fields
available. In fact, your first example is considered bad syntax by most DBAs
and is near impossible to maintain.

 

[Guest]

Yes you are right, but you mean 'programmer-friendly' not 'user-friendly'.
SQL statements in an application should be hidden from users and execute
transparently.

If a table has so many columns as to make specifying a column list more than
a minor chore its more than likely that the table is not well designed and
needs decomposing into two or more related tables. Tall skinny tables are
generally healthier than short fat ones.

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

You're correct that, unless you're providing a value for each field in the
table, you must enumerate the fields. I guess "user-friendly" means
different things to different people, though.

I always use a list of fields, even when I"m inserting every field in the
table. That way, I know for certain which value is going into which field.
Makes it much easier if I have to change a specific value later on, and
better as self-documentation, both of which I consider user-friendly!

 

[Tony Toews [MVP]]

INSERT into xxx (field1, field2 etc ) VALUES (value1, value2 etc) instead?
This doesn't seem very user-friendly if you have lots of required fields in
your table.

I just create a SELECT query using the query designer with the
appropriate fields, switch to SQL mode and then copy the field list to
my VBA code. Then I add the Value list and continue.

Assuming you're doing this in VBA code.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[aaron.kempf]

these guys are right-brain people

I think that they're all full of crap

yes; it is a pain.

I use real tools, for example ADP because it has support for better
query designer

and more importantly; I won't have to rewrite my code when I hit a
half-dozen users.

I believe that Microsoft should tackle the VWDB - as in Very Wide
Database with the same effort that they put into VLDB.

I have worked against at least 20 tables in the past 10 years that
have more than 100 columns. no amount of wishful thinking; no amount
of 'skinny table is a healthy table' crap is going to make it go away.

I have been asking Microsoft to adopt a terminology such as this; and
I believe that this would increase the productivity of countless
developers:

Select * {LIKE '%ID'} from myWideTable

this will give you all of the columns where the column name is like
%ID

pretty basic concept.

I also think that they should support this syntax

Select * {datatype: int} from myWideTable

in order to pull out all of the columns that are integers.

l think that it is a pretty simple concept; but micorsoft is too
concerned with stuffy IBM relational theory that is outdated anyways..

I find it comical- the relational design; highly normalized databases
are not a one-size fits all solution.

a lot of times; it makes sense to have all of your data in ONE ROW
instead of in 100 different rows, for this simple concept:

OUT OF THE BOX, YOU CAN WRAP ALL OF YOUR CHANGES TO THAT ROW IN A
SINGLE TRANSACTION. USING SIMPLE BOUND CONTROLS.

In order to do the same thing across 100 rows; you need about 1000
times as long in db execution time; in order to process this 100 row
transaction.

I find it humorous; not every solution _SHOULD_ fit 3rd normal form

and don't listen to these BlueHeads about DB theory.
they are still stuck in the 90s; I mean get real-- they use MDB in the
year 2007?

ROFLMAO

-Aaron
ADP Nationalist
Microsoft Certified Professional - SQL Server 2005

 

[aaron.kempf]

stick your british stuffy theory elsewhere.

we should have attacked your country in WWI, your country was the one
country that was tyranizing 1/3rd of the world's population.

-Aaron