AutoNumber behavior in linked table

I have a table (Table1) with two fields (recID, title) in an Access 2000 file
format database. The recID is AutoNumber and a Primary Key.

If I use the append query "INSERT INTO Table1 ( recID ) SELECT 1199" to
insert a record, it works fine (assuming a record with that ID does not
exist). I then add records to the same table manually and discovered
different behavior depending upon whether the table is native to the database
or is linked to the database.

In my example, assume that my table has records for 1, 10, and 20. I then
use the query to add a record for 11 and then add a new record manually to
the table. Here is the difference. If the table is a native table, the next
record becomes 21 (desired behavior). If the table is a linked table, the
next record becomes 12 (undesired behavior).

Can someone confirm this behavior. I want the next added record for a
linked table to have an ID that is 1 greater than the highest current ID
regardless of the ID of the previously added record. Is there an easy
solution? My alternative is to query for the highest current ID then add a
record using that ID + 1 in the INSERT query.

This behavior is the same for 2003 database format. Access 97 behaves the
way that I expect. Is using an Access 97 database my answer? That seems
like regression. Am I missing something?

Yes, this is a real problem, even with all current Office and JET service
packs applied.

Until Microsoft fixes it, the only solution is to "upgrade" to using Access
97, as you suggested.

Your situation is similar (but not identical) to this kb article:
BUG: You may receive an error message when you try to insert a new
record in a table that contains an Autonumber field in Access 2003
at:
http://support.microsoft.com/default...b;en-us;884185

The article suggests solving the issue by executing a DLL statement to reset
the Seed. That doesn't work on an attached table, and neither does setting
the Seed of the AutoIncrement column (ADOX.) You would need to
OpenDatabase() directly on the back end to perform these actions.

HTH, though it's not good news.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"genojoe" wrote in message
...
I have a table (Table1) with two fields (recID, title) in an Access 2000
file
format database. The recID is AutoNumber and a Primary Key.

If I use the append query "INSERT INTO Table1 ( recID ) SELECT 1199" to
insert a record, it works fine (assuming a record with that ID does not
exist). I then add records to the same table manually and discovered
different behavior depending upon whether the table is native to the
database
or is linked to the database.

In my example, assume that my table has records for 1, 10, and 20. I then
use the query to add a record for 11 and then add a new record manually to
the table. Here is the difference. If the table is a native table, the
next
record becomes 21 (desired behavior). If the table is a linked table, the
next record becomes 12 (undesired behavior).

Can someone confirm this behavior. I want the next added record for a
linked table to have an ID that is 1 greater than the highest current ID
regardless of the ID of the previously added record. Is there an easy
solution? My alternative is to query for the highest current ID then add
a
record using that ID + 1 in the INSERT query.

This behavior is the same for 2003 database format. Access 97 behaves the
way that I expect. Is using an Access 97 database my answer? That seems
like regression. Am I missing something?