How to create autonumber in a maketable query

[winxmun]

hi there,

may i know anybody know how to create autonumber in a maketable query? tks.

Merry Xmas & Happy New Year!!!:beerchug:

[X-BRichard-X]

You wouldn't use a make table query to achieve what you are trying to do. What you would do is to PRE-establish a table with your field names and data types (including your primary key field) then just use an append query to that table.

Using this method, the autonumber field will automatically be populated for you after your append query is executed.

Another option is to use the following code to create a table using VBA then use an append query (instead of a make table query) to the programmatically defined table.

The code to create a table in this fashion is as follows:

DoCmd.RunSQL "CREATE TABLE TableNameHere" _
& "(UnID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, ID1 Long, FIELDT TEXT (150), StartDate Date, EndDate Date, FIELDX TEXT (3), Comments TEXT (255))"

'NOTE1: The UniD COUNTER CONSTRAINT PrimaryKey PRIMARY KEY Line defines an incremental autonumber field With a primary key; ID1 defines a numeric data Type, the FIELDT, FIELDX And COMMENTS fields define text fields With a field size of 150, 3, And 255 characters respectively, And the STARTDATE And ENDDATE fields define a Date data Type.

'NOTE2: You CANNOT have any spaces within the field names called from within the RunSQL string

[winxmun]

noted, will try your method later. tks.

[winxmun]

how to have a refresh autonumber in a table.

example, there is 30 records in the table, i run a qry to delete all records in the table & re-append another 30 records into the same table. The autonumber will auto append started from No. 31. How to have the autonumber start from No. 1 again? tks.

[Carl A]

See This Threads Last Entry:

http://vbaexpress.com/forum/showthread.php?t=8520&highlight=autonumber

[Norie]

Perhaps you could use a data definition query?

ALTER TABLE data ADD id COUNTER PRIMARY KEY,
name VARCHAR(10)

[X-BRichard-X]

No, programmatically autonumbering a table can be achieved. It is no problem! This code is not well documented. Anyway, I've copied and pasted how to do this from my code library. Here's how:

'To achieve this objective, you must create an APPEND query in SQL mode that contains the SQL syntax listed below where Table1 is the name of the table and ID1 is the AutoNumbered field name. When setting up the new table, ensure that the ID1 field is the Primary Key field and that the NEW VALUES property located under the Table's Field Properties windows is set to "Increment." Set the Field Size attribute to "Long Integer."
'To generate the numbering protocol, run the query by double clicking the object in query view within the database window.


PARAMETERS [Number] Long;
INSERT INTO Table1 (ID1)
SELECT [Number] AS Expr1;

'NOTE1: The word Number placed in brackets in this query is a literal and NOT an actual number or any other representation thereof.


'NOTE2: The table MUST be NEW and have no records in it before producing a new primary key numbering protocol. An existing table can be used provided the following (2) conditions are met:
'1) there are no records in the table before the append query is initiated.
'2) the number selected for the renumbering protocol MUST be LARGER than the last primary key number previously assigned to the last record in the table.

When the "Numbers" parameter pops up enter the new autonumber. For this approach to be integrated into your solution, you will have to use a new table which may defeat the purpose of this code in the first instance because a new table's primary key always begins with 1 anyway.

However, perhaps this code will help others who are looking to re-establish the autonumber field (primary key field) from a value different than 1.