ansaurus

Question

In an MS Access Maketable query, how to create an Autonumber field?

Answer 1

A:

I reckon you either need to use TableDefs or DDL. The DDL query would run after the maketable query, for example:

 ALTER TABLE NewTable ADD COLUMN AutoField COUNTER

EDIT Additional note

If you wish to make the new column the primary key, you can run something like:

ALTER TABLE NewTable ADD PRIMARY KEY (AutoField)

Remou 2010-03-12 15:43:45

Thank you sir. I'm a little surprised this question hasn't been asked before on stackoverflow.

PowerUser 2010-03-12 15:52:11

Why would the vast majority of Access users need DDL to do something as simple as adding an Autonumber field to a table? The point of Access is that it makes these kinds of things discoverable through its UI so you don't need to know SQL to get the job done, so in general the only people asking this kind of question are people who are either not using Access at all (they are only using Jet/ACE as a data store) and don't have Access installed, or are so clever that they've never bothered to learn how to use all the useful tools that Access provides out of the box.

David-W-Fenton 2010-03-12 18:26:46

Sorry, David, I don't quite understand. Are you suggesting a better solution than Remou's? Or are you just commenting on MS Access's functionality in general?

PowerUser 2010-03-12 21:20:12

I'm suggesting opening up Access and making the changes to the table through the Access UI, instead of running DDL SQL to do the same thing. The latter is useful when you don't have Access, but it seems to me to be overkill for something so simple as this when you have it and when it's not something that has to be automated (e.g., you need to do this with dozens of tables in many different databases). That's my explanation of why it's not been seldome asked about, because Access users don't need DDL to do this kind of thing, thus, not question about it.

David-W-Fenton 2010-03-13 20:50:30

When it can be done manually, I agree. But in this case, it does need to be automated. So Remou's answer is ideal.

PowerUser 2010-03-15 12:48:49

ansaurus

tags:

views:

answers:

In an MS Access Maketable query, how to create an Autonumber field?

related questions