Database cannot append all the records

[Paul H]

"due to key violations."

I'm sure everyone has seen this before. I have and I'm frustrated. This query ran last week, but I recently refreshed my data and something must have changed. The question is what? I'm poking around looking at relationships and ID fields, but I'm coming up empty. It there an easier way to find the error. The clock is ticking and I need to move on.

Thanks,

Paul

[pbaldy]

First things that come to mind are that you're trying to append a record or records that already exist, based on the primary key or indexes, or the query isn't populating a required field.

[Paul H]

Neither, the table I am appending is empty and I have no required field other than the Primary Key, which is an autonumber.

[pbaldy]

Not sure if that's the error you'd get, but if you have referential integrity enabled, and you tried to append a record for customer 123 and that customer didn't exist in the customers table, you'd get an error. Can you post the db?

[Paul H]

I found it. Somewhere in the process of renaming a table and replacing it with another, the relationship followed the defunct table, even though I went into the relationships window and hooked it up to the new table, because the table still existed there was a hidden relationship I couldn't see and the related record in that table did NOT exist. I deleted the table and Access prompted me to delete the offending relationship, which I did. Now I'm back in business, but have lost several critical hours. Grrr!

Thanks for your help and tolerating the rant.

From one Paul to another.

[pbaldy]

Glad you got it sorted out. Sorry I wasn't much help.

[Paul H]

Glad you got it sorted out. Sorry I wasn't much help.

Frankly, just having some place (like this) or someone (like yourself) to bounce things off of is helpfull. I have found many times just the process of trying to document my error has led me to a solution before I have a chance to post it. So thanks to you or anyone one else who supports sites like these. We'd be lost without you.

Paul

btw, I now know who John Galt is.

[pbaldy]

Thank you for your kind words!

We Paul's have to stick together.

[Paul H]

I know how this problem came about. I was trying to replace one table with another by renaming the old one and then renaming the new one to the original name (something like that) I have set up all these relationships in advance and these got all crosswired by doing that. Is there a better way of doing this.

Also one reason for doing this would be to reset the Autonumber field to 1. I don't know another way of doing that.

[pbaldy]

The usual method for resetting the autonumber is to compact/repair the database. Historically that will do it, though I have some memory of reading something about 2010 not doing it. Try it and see if it works.