Append records to linked table with autonumbering field: Key conflict!

[Sendeman]

Hi all,

I'm having trouble on a database I have.

When running an append query on a linked table with an autonumbering field, I get a key conflict. This has been noted by others. It seems that when a table is linked, Access converts the autonumber field to a number field. This prohibits running of an append query against the linked table, because it complains about null/duplicated key values (since it no longer autonumbers the key).

Also, I used to be able to perform this append query perfectly well on linked tables in Access 2000. I think in Access 2000 this problem didn't exist, whereas in Access 2007 it does?

Anyway, I need to get it to work in Access 2007 now, so does anyone have any ideas on how to approach this?

Thank a lot in advance! Any help will be appreciated.

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[boblarson]

What you should realize is that an Autonumber IS a number (Long Integer). When you append records you normally do NOT include the field that is the autonumber in the query. Access will add the appropriate number as it appends the data.

Occasionally you may find that Access wants to add an autonumber that is in the middle of your numbering (because of a missing record and it got screwed up). So, what you can do then is to re-seed the autonumber. Find the number that SHOULD be the next in sequence. Then, go to your query and have it select only ONE RECORD and add the field for the autonumber in like this (as if in the QBE grid):

Field: 19322
Table:
Sort:
AppendTo:TheAutoNumberFieldNameHere
Criteria:
or:

The 19322 there is just an example. You would put your actual number there. Then run the query to append the one record. Now you should be able to run the full append query (minus the first record) and it should now be back to normal.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[Sendeman]

Hi Bob,

Thanks for your reply. To be clearer: I didn't include the field that is the autonumber in the query. Access should autofill with any available number. That would be fine. I do not need to have numbers that are in an uninterrupted sequence, just as long as they are unique that's fine.

I tried your suggestion, but it didn't work. I still get the same key conflict error.

I don't think the problem is with (previously) existing numbers in the autonumber field. The table was empty. I even deleted the autonumber field from the source database. I then Compacted and repaired the database and afterwards added the autonumber field again. This should reset the field entirely. When I link the table now, I still have no success filling it. When I fill it from within the source database, I have no problems at all.

I really think it is an access 2007 issue with the linked table. Access 2000 didn't have the same problem with this query.

Any other suggestions would be really helpful. Thanks again in advance!

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[Sendeman]

Hm, I think I may be wrong.

I have another linked table that does not have this problem. I can fill it with another append query I made.

So, this leaves the question. What is wrong with the table/query I'm trying to append to/with without success?

The query seems correct: when I copy the linked table structure and data to a new table in the same database (with the name of the linked table), and then perform the query on that table, it works fine! It is just the linked table that is giving the issues.

What can I do? I have no clue...



I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[Sendeman]

Hi again,

I solved the issue. It turned out to be my own mistake. There was an error in the input data. Thanks for taking the trouble of trying to help me! Even though my problem turned out different than I thought at first, your solution put me on track for finding the real issue.

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -