Autonumber field in a maketable query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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).
 
George said:
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
Click to expand...
append).

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

Jamie.

--
 
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...
 
George said:
again.

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

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.

--
 
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.


onedaywhen said:
George said:
again.

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

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.
Click to expand...
 
Back
Top