Generating an Auto Number ID Field in a Make Table Query

[mcl]

How would I go about generating an ID, autonumbered, indexed field in a make
table query aside from just editing the table afterward and making one?

 

[John Vinson]

How would I go about generating an ID, autonumbered, indexed field in a make
table query aside from just editing the table afterward and making one?

You can't. MakeTable queries simply don't have that option.

Note that make-table queries are VERY rarely actually necessary. There
is very little that you can do with a table that you cannot do just as
easily with a Select query.

John W. Vinson[MVP]

 

[mcl]

Not if you using ArcGIS which talks very nicely to Access and in fact uses
access tables as it's Geodatabase format. You point ArcGIS (from ESRI) to an
Access database and the tables light up like a xmas tree in the file open
dialogue.

However, I have found a few "I got you's".
1. No spaces in table names. ArcGIS doesn't like that at all.
2. No spaces, "(", or ")" in field names. ArcGIS doesn't like that at all
either although access doesn't mind them.
3. There are certain reserved words in ArcGIS which caused problems when
used as an Access name (i.e.. mod)
4. If you want to do a select by in ArcGIS you better have an ID field in
the Access table or ArcGIS will not even list it as selectable. (This is
what prompted the question)

I'm a meteorologist/climatologist and I find Access very useful in crunching
our data and linking that to ArcGIS is great.

 

[Jeff Boyce]

Perhaps you could use a more "permanent" table, clear it out each time
before you use it, and append your records into for later use? That way,
you could define an Autonumber/ID field.

Good luck
Jeff Boyce
<Access MVP>

 

[mcl]

Right now I just go in after I generate the table and put in the ID
autonumber field. Not a big deal but I thought I could do it all at once.
I have queries that crunch some climate data creating means. I can update it
every month. Generate a new table which ArcGIS reads. Great way to update
things. Works fine. Just thought I could eliminate an extra (create an ID
field) step. ArcGIS just seems to need that ID field and get's annoyed if it
isn't there. Doesn't seem effect any computations but needs that ID field
there to properly read the table.

 

[John Vinson]

Right now I just go in after I generate the table and put in the ID
autonumber field. Not a big deal but I thought I could do it all at once.
I have queries that crunch some climate data creating means. I can update it
every month. Generate a new table which ArcGIS reads. Great way to update
things. Works fine. Just thought I could eliminate an extra (create an ID
field) step. ArcGIS just seems to need that ID field and get's annoyed if it
isn't there. Doesn't seem effect any computations but needs that ID field
there to properly read the table.

Do note that you can have an ID field (a Primary Key) without that
field being an Autonumber. You'll need some code to insert unique
values into the field, but that should be doable in a make-table.

John W. Vinson[MVP]