A different autonumber question - single column table

So you have this 1-Field Table as the "One" (Parent) Table in the
One-to-Many relationship???

If you multiple Child Records related to each Parent Record in this Table,
surely there must be some common property / ies whereby you relate Child
Records to a particular Parent Record. The fact that you "group" Child
Records as related to different Parent Records indicates there are some
properties you use to group them. These properties should generally be
store in the One/Parent Record.

I don't know about your set-up, though. However, after some 20+ production
databases (JET & SQL Server back-ends), some with 100+ Tables, I have never
had to use a AutoNumber-Field-only Table.

--
HTH
Van T. Dinh
MVP (Access)


"Tim Ward" wrote in message
...
"Van T. Dinh" wrote in message
...
So you have another Field in this Table which is a ForeignKey Field???

No, I have another table which refers back to the autonumber field with a
foreign key.

--
Tim Ward
Brett Ward Limited - www.brettward.co.uk

Actually, you can't, even with Tab key. You need to enter something (in
another Field) for the AutoNumber Field value to be assigned.

--
HTH
Van T. Dinh
MVP (Access)



"TC" wrote in message
...

"Tim Ferguson" wrote in message
...
"TC" wrote in :

but why would you want to..?

I think he means manually, from the UI. He has opened the table
datasheet. How does he add new records to it (manually)?!



Ummm.... why would he want to?

What about pressing tab whilst the focus is on the autonumber field of
the "new record" row...

Oops, yep, I guess that would do it.

As for why - I fear that we shall never know!

Cheers,
TC

"Tim Ward" wrote in message
...
"Van T. Dinh" wrote in message
...
The only purpose of an AutoNumber Field is to provide uniqueness to each
Record in the Table. Thus, the Table with only the AutoNumber Field
does
not have any useful information content.

Why would you want to create the Table without any meaningful info.
content?

Because the only attribute of this object is a multivalued attribute, so
the
actual information is in a join table which refers back to this main table
via a foreign key.


But surely you want to store some non-key attributes of the object
represented by the main table? I'm not arguing that you are incorrect. I
just can't think of a case where I would want to store child details of a
parent object, but I did not need any non-key attributes of that parent
object!

Cheers,
TC

Ok, should have realized that!

TC


"Van T. Dinh" wrote in message
...
Actually, you can't, even with Tab key. You need to enter something (in
another Field) for the AutoNumber Field value to be assigned.

--
HTH
Van T. Dinh
MVP (Access)



"TC" wrote in message
...

"Tim Ferguson" wrote in message
...
"TC" wrote in :

but why would you want to..?

I think he means manually, from the UI. He has opened the table
datasheet. How does he add new records to it (manually)?!



Ummm.... why would he want to?

What about pressing tab whilst the focus is on the autonumber field
of
the "new record" row...

Oops, yep, I guess that would do it.

As for why - I fear that we shall never know!

Cheers,
TC

"TC" wrote in message ...

But surely you want to store some non-key attributes of the object
represented by the main table? I'm not arguing that you are incorrect. I
just can't think of a case where I would want to store child details of a
parent object, but I did not need any non-key attributes of that parent
object!

No, there are no non-key attributes of the object represented by the main
table. The main table is a table of "lists of widgets", each row
representing one (possibly empty) "list of widgets". There is no information
associated with a "list of widgets" other than the id and the widgets.

(Yes, there is information about who owns which list of widgets, but
different lists of widgets can be owned by different types of objects in a
tree structure, and I've chosen the pattern which has "list of widget" id
fields in the owning objects' tables rather than the pattern which has "type
of owner" and "id of owner" fields in the "list of widget" table. Both
solultions are admittedly mucky, but I've got reasons for claiming the one
I've chosen is slightly less mucky in this particular application.)

--
Tim Ward
Brett Ward Limited - www.brettward.co.uk

"Tim Ward" wrote in message
...
"TC" wrote in message
...

But surely you want to store some non-key attributes of the object
represented by the main table? I'm not arguing that you are incorrect. I
just can't think of a case where I would want to store child details of
a
parent object, but I did not need any non-key attributes of that
parent
object!

No, there are no non-key attributes of the object represented by the main
table. The main table is a table of "lists of widgets", each row
representing one (possibly empty) "list of widgets". There is no
information
associated with a "list of widgets" other than the id and the widgets.

(Yes, there is information about who owns which list of widgets, but
different lists of widgets can be owned by different types of objects in a
tree structure, and I've chosen the pattern which has "list of widget" id
fields in the owning objects' tables rather than the pattern which has
"type
of owner" and "id of owner" fields in the "list of widget" table. Both
solultions are admittedly mucky, but I've got reasons for claiming the one
I've chosen is slightly less mucky in this particular application.)

If all else fails, try a CreatedDate field (setting its default to Date() or
Now()) That way, at least you have a non AutoNumber field to tab into.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Tim Ward" wrote in message
...
"TC" wrote in message
...

But surely you want to store some non-key attributes of the object
represented by the main table? I'm not arguing that you are incorrect. I
just can't think of a case where I would want to store child details of
a
parent object, but I did not need any non-key attributes of that
parent
object!

No, there are no non-key attributes of the object represented by the main
table. The main table is a table of "lists of widgets", each row
representing one (possibly empty) "list of widgets". There is no
information
associated with a "list of widgets" other than the id and the widgets.

(Yes, there is information about who owns which list of widgets, but
different lists of widgets can be owned by different types of objects in a
tree structure, and I've chosen the pattern which has "list of widget" id
fields in the owning objects' tables rather than the pattern which has
"type
of owner" and "id of owner" fields in the "list of widget" table. Both
solultions are admittedly mucky, but I've got reasons for claiming the one
I've chosen is slightly less mucky in this particular application.)


Ok, understood. Well described!

TC

Edit - Append works a treat for me!

Does anyone know the answer to the original question? My reason for needing to do this is simply that my program allows the user to create data structures and I do not want to tell them they can't do something as silly as creating a table with a single column which is autonumber. Maybe they just want to count something that way... For me, this can't be done in Access itself. It needs to be done in a query which potentially, could go through ODBC or OLEDB.

If you do an insert and specify a number, it actually uses that number:
INSERT INTO [Table] (testcol) values (0)
Puts an entry with 0 as the "Autonumber" field.

If I try inserting NULL into the column, I get:
"You tried to assign the Null value to a variable that is not a Variant data type."

Any ideas? I am guessing that this can't be done...


"Tim Ward" wrote:

Table with just one column which is an autonumber column - how do I insert
records?

--
Tim Ward
Brett Ward Limited - www.brettward.co.uk

=?Utf-8?B?RGF2aWQgQ29ub3Jvenpv?= David
wrote in news:E51B0E59-A8CF-4FA6-97D8-
:


If you do an insert and specify a number, it actually uses that number:
INSERT INTO [Table] (testcol) values (0)
Puts an entry with 0 as the "Autonumber" field.



INSERT INTO MyTable (TestCol)
SELECT 1 + MAX(TestCol)
FROM MyTable


.... but it's still a pointless thing to do... g

PS: I haven't tested it, but it might fail on an empty table.. it is left
to the reader to find out how Jet treats NULL+1 as a numeric value.

HTH

Tim F