Autonumber field in a maketable query

[Guest]

How do I create a field with an autonumber in a maketable query that uses
tables which do not have a autonumber. I can add an autonumber field to the
table after I create the table, but that means I will have to remember to do
it. I would like my maketable query to do it.

 

[George Nicholson]

One approach: don't use a make table query. Set up your table (with the
auto number field) and then use an append query rather than a make-table to
fill it. Run a delete query to clear all the records out before you run the
append query again. (but your autonumbers will start after those in the
records you just deleted unless you compact between the delete and append).

 

[onedaywhen]

Run a delete query to clear all the records out before you run the
append query again. (but your autonumbers will start after those in the
records you just deleted unless you compact between the delete and

append).

Alternatively, issue a DROP TABLE followed by the CREATE TABLE again.

Jamie.

--

 

[George Nicholson]

Alternatively, issue a DROP TABLE followed by the CREATE TABLE again.

...which I think would put the OP right back where they started: asking how
to create an autonumber field in a MakeTable query...

 

[onedaywhen]

again.

...which I think would put the OP right back where they started: asking how
to create an autonumber field in a MakeTable query...

I was thinking along the lines of e.g.

CREATE TABLE MyTempTable (
key_col INTEGER IDENTITY(1,1) PRIMARY KEY,
data_col NTEXT)
;
INSERT INTO MyTempTable (data_col)
SELECT F1 as data_col FROM
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[A:A]
;
<<use table here>>
;
DROP TABLE MyTempTable
;
CREATE TABLE MyTempTable (
key_col INTEGER IDENTITY(1,1) PRIMARY KEY,
data_col NTEXT)
;
INSERT INTO MyTempTable (data_col)
SELECT ... etc etc

My point is, DROP TABLE MyTempTable is easier than DELETE FROM
MyTempTable followed by compacting the file which would require all
users to be kicked out first.

Jamie.

--

 

[George Nicholson]

No argument from me, if the OP is at a level where they are comfortable
implementing that approach.

--
George Nicholson

Remove 'Junk' from return address.

again.

...which I think would put the OP right back where they started: asking how
to create an autonumber field in a MakeTable query...

I was thinking along the lines of e.g.

CREATE TABLE MyTempTable (
key_col INTEGER IDENTITY(1,1) PRIMARY KEY,
data_col NTEXT)
;
INSERT INTO MyTempTable (data_col)
SELECT F1 as data_col FROM
[Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[A:A]
;
<<use table here>>
;
DROP TABLE MyTempTable
;
CREATE TABLE MyTempTable (
key_col INTEGER IDENTITY(1,1) PRIMARY KEY,
data_col NTEXT)
;
INSERT INTO MyTempTable (data_col)
SELECT ... etc etc

My point is, DROP TABLE MyTempTable is easier than DELETE FROM
MyTempTable followed by compacting the file which would require all
users to be kicked out first.

Jamie.