INSERT INTO table with autonumber (1 Viewer)

[stumbler]

Hi,
I 'm trying to add records to a table, but this table contains a autonumber.
The command i use

        strSQLcommand = "INSERT INTO [tblOrder] select * FROM temp"
        cmdCreateTable.CommandText = strSQLcommand
        cmdCreateTable.Execute

but the problem is that there is an autonumber column in tblOrder.
I removed this column from the temp table, but still the raises an error
that double values are generated.

Can someone give me a hint?
Thx

 

[John Big Booty]

The problem lies in your SQL statement, it is selecting and inserting all the field from your origin table and trying to insert them all in your destination table, try something along the lines of;

INSERT INTO [YourDestinationTable] ( Field1, Field2 )
SELECT YourOriginTable.Field1, YourOriginTable.Field2
FROM [I]YourOriginTable[/I];

You will need to name all other fields, in the INSERT INTO clause and SELECT clause.

 

[John Big Booty]

Your code would look something like;

strSQLcommand = "INSERT INTO [YourDestinationTable] ( Field1, Field2 ) " & _
                "SELECT YourOriginTable.Field1, YourOriginTable.Field2 " & _
                "FROM [I]YourOriginTable[/I];"
cmdCreateTable.CommandText = strSQLcommand
cmdCreateTable.Execute

 

[John Big Booty]

Hint; You can't insert data into an Autonumber field.

 

[stumbler]

I removed the column containing the autonumber out of the temp table (ALTER TABLE temp DROP COLUMN xyz (=the autonumber column). all other fields are identical but still it is not working.

 

[stumbler]

i want to avoid going to the table line by line and do a mass update.
if possible.

 

[John Big Booty]

Sorry there was a slight error in the code I posted above. The italicised section is now correct.

 

[Pat Hartman]

Hint; You can't insert data into an Autonumber field.

You can if you use an append query and the value doesn't duplicate an existing autonumber.

 

[John Big Booty]

You can if you use an append query and the value doesn't duplicate an existing autonumber.

I stand corrected

Although I would have thought that doing so would be fraught with potential issues.

 

[stumbler]

Thx for your help!
solved it by enumerating the fields to update as John suggested
Don't really trust adding info to an autonumber

think it's better to give that privilege to access

 

[Pat Hartman]

Although I would have thought that doing so would be fraught with potential issues.

Conversions would be a nightmare if you couldn't do this. There is no other reason for it that I can determine but I've used it many times.

 

[boblarson]

Conversions would be a nightmare if you couldn't do this. There is no other reason for it that I can determine but I've used it many times.

Reseeding an Autonumber is one use for it as well. Sometimes it gets messed up and it needs a kick in the pants to get it going again. So, doing an insert of one record with the autonumber specified as the next one it should be using will get it back on track.

 

[John Big Booty]

Thanks Guys