Create AutoNumber field

[jempie]

All,

I have trawled boards and sites, but cannot find the answer!

I have a make-table query, and I wish to create an AutoNumber field.

Now I know that if you put fieldname: "" , a Text field will be created and if enter fieldname: [] then a Binary field will be created.

Is there a code I can use to create an AutoNumber field?

Help appreciated!

Regards,

Jempie

 

[llkhoutx]

dim db as dao.database
dim sql as string
dim stbl as string
dim sfld as string
stbl="Your table name"
sfld ="Your autonumber field name"
Set db = CurrentDb
sql = "ALTER TABLE " & stbl & " ADD COLUMN " & sfld & " AUTOINCREMENT"
db.Execute sql


You have no control over the position in the table where the field is inserted. It will be added to the end.

 

[jempie]

is there no way of doing this in the query design?

for example if you enter

FieldName: ""
It will create a Text field called FieldName.

FieldName: []
It will create a Number field called FieldName.

Thanks

 

[Cosmos75]

Are you just wanting to create a new table or use data from a table/query to make a new table?

Create table using SQL

CREATE TABLE tblAutoNum (lngAutoNum AUTOINCREMENT);

Make-Table Query

SELECT * INTO tblNew
FROM tblData;

Alter existing table using SQL

ALTER TABLE tblNew ADD COLUMN lngAutoNum AUTOINCREMENT;

 

[llkhoutx]

Datatype for a fieldname, i.e. autonumber, cannot be created in the Query Design view.

It can be created in the SQL view, as already posted in this thread.

 

[unclejoe]

Hi jempie,

Yes you can. If you have a existing table that has a Column with a “AutoNumber”

First, do your “Make Table Query” as usual. Name that new table, right click the empty space above the QBE fields. Select the Table with an Autonumber.

What you need to do is use that Table, put into the QBE, select the AutoNumbered Column into the QBE field (select click and drag into the first field) and in the Criteria field, put this “Is Null” (without the quotes).

“Is Null” will not copy any data into the new table.

If you want to copy or create similar columns that has the same data type that you wish to use. Select the Columns/Fields in that same Table or use another Table that has the same data types.

You can rename the selected field like “NewCustomer: CustomerName”
Where “CustomerName” is the column of the existing table and where “NewCustomer” is the new column in the new Table.

All,

I have trawled boards and sites, but cannot find the answer!

I have a make-table query, and I wish to create an AutoNumber field.

Now I know that if you put fieldname: "" , a Text field will be created and if enter fieldname: [] then a Binary field will be created.

Is there a code I can use to create an AutoNumber field?

Help appreciated!

Regards,

Jempie