INSERT INTO With AutoNumber field

[Darrell]

I need to be able to INSERT INTO with dynamic sql into a table with an
autonumber key field.

I have read a number of posts from 1997 to 2001 on Google Groups with
this exact question and the answer being given to just ignore the
autonumber field because the database will take care of it. I have done
this with a test query with only the relevant fields included and having
checked the indexes and required fields carefully to be certain that I
am not violating rules unrelated to the autonumber field, and I still
get the "0 records added due to primary key violations" message.

I am using Access 2003.

Any insights?

Thanks in advance,

Darrell

 

[Darrell]

I need to be able to INSERT INTO with dynamic sql into a table with an
autonumber key field.

I have read a number of posts from 1997 to 2001 on Google Groups with
this exact question and the answer being given to just ignore the
autonumber field because the database will take care of it. I have done
this with a test query with only the relevant fields included and having
checked the indexes and required fields carefully to be certain that I
am not violating rules unrelated to the autonumber field, and I still
get the "0 records added due to primary key violations" message.

I am using Access 2003.

Any insights?

Thanks in advance,

Darrell

Just a response to my own post. Using a recordset object and the
".AddNew" method works beautifully. Still don't know about the INSERT
INTO and would be interested in anyone's insights there.

Darrell

 

[John Spencer]

POST the SQl of the query that is failing. We can't see what you are
attempting, so it is rather hard to guess what is causing the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Evi]

You can use Insert Into on a table with an Autonumber field. Just don't
append anything to that field. If , for some reason, you need to keep a
record of the Autonumber (for instance, if you are storing archived data)
then the field on the Append To table can be formatted as Number but, in the
case of archived data, it probably won't be able to remain as the Primary
Key field of the archived table because the user may Compact and Repair his
cleaned out table thus resetting his autonumber.
Evi