Access 2000 - Autonumber for table from Make table query

Posted on 2011-05-12
Last Modified: 2012-05-11
Dear Experts,

I have a make table query from where I would like create also a Primary key so an autonumber field for the created table by it.

Could you please advise how to do that

Question by:csehz
    LVL 119

    Accepted Solution

    the easiest way to do this is to open the table in design view, add an autonumber field and make it a primary key.

    you can also do this using

    alter table [NameOfTable] add column id counter
    LVL 77

    Assisted Solution

    cap1 has given you the solution.  The point is, you cannot add the autonumber field via the make-table query.
    LVL 22

    Assisted Solution

    by:Om Prakash
    You can create a table with all the fields, keys & properties you want, and then use an append query, instead of using a create table query

    You can use use TableDefs or DDL statements

    To add auto number field, following is the query:
    alter table table_name add COLUMN AutoField COUNTER EDIT Additional note

    And to make this column as primary key, you can use the following query:
    alter table table_name ADD PRIMARY KEY (AutoField)
    LVL 1

    Author Comment

    Thanks very much, I have learnt from all of three answers, can I give the points as split?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now