How to Create Temp Table without AutoNumber field

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

Mike Thomas

Using Access 2000, below is a simplified SQL statement used to make a temp
table.

Since [dipk] is an auto number field/rimary key in the parent table [Packing
Data -Items], I've renamed it and done the multiplication by 1 to so that
these propertied will not be inherited.

Because of what I do with the temp table later in the program, I would like
to wind up with a table with the first column named dipk, but without any
autonumber or primary key status inherited.

SELECT [dipk]*1 AS PackLineKey, [Packing Data -Items].diPackingHEADERKey
INTO xPackLine
FROM [Packing Data -Items]
WHERE False;

Is there a way to do this, or do I need to the rename the field PackLineKey
back to dipk?

Many thanks
Mike Thomas
 
Mike said:
Using Access 2000, below is a simplified SQL statement used to make a temp
table.

Since [dipk] is an auto number field/rimary key in the parent table [Packing
Data -Items], I've renamed it and done the multiplication by 1 to so that
these propertied will not be inherited.

Because of what I do with the temp table later in the program, I would like
to wind up with a table with the first column named dipk, but without any
autonumber or primary key status inherited.

SELECT [dipk]*1 AS PackLineKey, [Packing Data -Items].diPackingHEADERKey
INTO xPackLine
FROM [Packing Data -Items]
WHERE False;

Is there a way to do this, or do I need to the rename the field PackLineKey
back to dipk?
Click to expand...


I think you can just name the field in the query:

SELECT [dipk]*1 AS dipk, . . .

Another way to approach this is to create a table at design
time to have all the right field names and properties. Then
the code can delete the records before executing an Append
query.

If you are planning on this happening relatively often, then
you can avoid the bloat issue, by creating a temporary
database and copying an empty "template" table to it (you
would use an Append query to add the data). Here's an
article that provides details:
http://www.granite.ab.ca/access/temptables.htm
 
Back
Top