Append query not appending

Topic

New Reply

Here’s an interesting situation that I’m wondering if anyone has an answer to. I’ve devised a solution, but it’s less-than-elegant, and seems like it should be unnecessary.

I receive a file of “address changes” each month which may contain address changes or new records. By comparing the two tables in a Left Outer join, I can determine which “address changes” do not exist in the current customer table and will need to be appended. I perform a make-table query to place these new records in their own table “New Customers”, and then later in the processing (after I’ve made the address changes) perform an append query to append all the records in this table to the customer table. There are currently over 20,000 customers in the table; I’m trying to add 755 new records. There are 365 records that are actually changes. When I run the append query, it appends 13 and tells me the rest have not been added due to key violations. Now the only key field is the customer number, and I already confirmed it’s not duplicate when I ran the Select query where I used the Left Outer join to create the “New Customers” table in the first place. So I run the append query again. Interestingly, it appends another 18 records now. A third run nets another 15 and so on. Eventually, after running the query 8 times, all records get appended to the customer table.

I’m running all this in VBA using SQL statements. What I’ve done as a workaround is determine how many records I should have (by issuing a DCOUNT against the New Customers table) and then executing the Query repeatedly (using dbs.Execute “Queryname” in a loop instead of using SQL) and incrementing a counter to count .RecordsAffected. When this counter reaches the dcount, I exit the loop and carry on.

My question: What would make a query only append 13 records when it’s obvious there really are no key violations since it’s able to append all the records if I run it 8 times in succession?

Has anyone run up against something like this before? Interestingly, I’ve refreshed my data from a backup, compacted and repaired the database and received EXACTLY the same result requiring 8 runs of the query with the same number appended each time. Very strange.

Reply | Quote

Replies

Here’s an interesting situation that I’m wondering if anyone has an answer to. I’ve devised a solution, but it’s less-than-elegant, and seems like it should be unnecessary.

I receive a file of “address changes” each month which may contain address changes or new records. By comparing the two tables in a Left Outer join, I can determine which “address changes” do not exist in the current customer table and will need to be appended. I perform a make-table query to place these new records in their own table “New Customers”, and then later in the processing (after I’ve made the address changes) perform an append query to append all the records in this table to the customer table. There are currently over 20,000 customers in the table; I’m trying to add 755 new records. There are 365 records that are actually changes. When I run the append query, it appends 13 and tells me the rest have not been added due to key violations. Now the only key field is the customer number, and I already confirmed it’s not duplicate when I ran the Select query where I used the Left Outer join to create the “New Customers” table in the first place. So I run the append query again. Interestingly, it appends another 18 records now. A third run nets another 15 and so on. Eventually, after running the query 8 times, all records get appended to the customer table.

I’m running all this in VBA using SQL statements. What I’ve done as a workaround is determine how many records I should have (by issuing a DCOUNT against the New Customers table) and then executing the Query repeatedly (using dbs.Execute “Queryname” in a loop instead of using SQL) and incrementing a counter to count .RecordsAffected. When this counter reaches the dcount, I exit the loop and carry on.

My question: What would make a query only append 13 records when it’s obvious there really are no key violations since it’s able to append all the records if I run it 8 times in succession?

Has anyone run up against something like this before? Interestingly, I’ve refreshed my data from a backup, compacted and repaired the database and received EXACTLY the same result requiring 8 runs of the query with the same number appended each time. Very strange.

Reply | Quote

Here’s an interesting situation that I’m wondering if anyone has an answer to. I’ve devised a solution, but it’s less-than-elegant, and seems like it should be unnecessary.

I receive a file of “address changes” each month which may contain address changes or new records. By comparing the two tables in a Left Outer join, I can determine which “address changes” do not exist in the current customer table and will need to be appended. I perform a make-table query to place these new records in their own table “New Customers”, and then later in the processing (after I’ve made the address changes) perform an append query to append all the records in this table to the customer table. There are currently over 20,000 customers in the table; I’m trying to add 755 new records. There are 365 records that are actually changes. When I run the append query, it appends 13 and tells me the rest have not been added due to key violations. Now the only key field is the customer number, and I already confirmed it’s not duplicate when I ran the Select query where I used the Left Outer join to create the “New Customers” table in the first place. So I run the append query again. Interestingly, it appends another 18 records now. A third run nets another 15 and so on. Eventually, after running the query 8 times, all records get appended to the customer table.

I’m running all this in VBA using SQL statements. What I’ve done as a workaround is determine how many records I should have (by issuing a DCOUNT against the New Customers table) and then executing the Query repeatedly (using dbs.Execute “Queryname” in a loop instead of using SQL) and incrementing a counter to count .RecordsAffected. When this counter reaches the dcount, I exit the loop and carry on.

My question: What would make a query only append 13 records when it’s obvious there really are no key violations since it’s able to append all the records if I run it 8 times in succession?

Has anyone run up against something like this before? Interestingly, I’ve refreshed my data from a backup, compacted and repaired the database and received EXACTLY the same result requiring 8 runs of the query with the same number appended each time. Very strange.

Reply | Quote

Here’s an interesting situation that I’m wondering if anyone has an answer to. I’ve devised a solution, but it’s less-than-elegant, and seems like it should be unnecessary.

I receive a file of “address changes” each month which may contain address changes or new records. By comparing the two tables in a Left Outer join, I can determine which “address changes” do not exist in the current customer table and will need to be appended. I perform a make-table query to place these new records in their own table “New Customers”, and then later in the processing (after I’ve made the address changes) perform an append query to append all the records in this table to the customer table. There are currently over 20,000 customers in the table; I’m trying to add 755 new records. There are 365 records that are actually changes. When I run the append query, it appends 13 and tells me the rest have not been added due to key violations. Now the only key field is the customer number, and I already confirmed it’s not duplicate when I ran the Select query where I used the Left Outer join to create the “New Customers” table in the first place. So I run the append query again. Interestingly, it appends another 18 records now. A third run nets another 15 and so on. Eventually, after running the query 8 times, all records get appended to the customer table.

I’m running all this in VBA using SQL statements. What I’ve done as a workaround is determine how many records I should have (by issuing a DCOUNT against the New Customers table) and then executing the Query repeatedly (using dbs.Execute “Queryname” in a loop instead of using SQL) and incrementing a counter to count .RecordsAffected. When this counter reaches the dcount, I exit the loop and carry on.

My question: What would make a query only append 13 records when it’s obvious there really are no key violations since it’s able to append all the records if I run it 8 times in succession?

Has anyone run up against something like this before? Interestingly, I’ve refreshed my data from a backup, compacted and repaired the database and received EXACTLY the same result requiring 8 runs of the query with the same number appended each time. Very strange.

Reply | Quote

Here’s an interesting situation that I’m wondering if anyone has an answer to. I’ve devised a solution, but it’s less-than-elegant, and seems like it should be unnecessary.

I receive a file of “address changes” each month which may contain address changes or new records. By comparing the two tables in a Left Outer join, I can determine which “address changes” do not exist in the current customer table and will need to be appended. I perform a make-table query to place these new records in their own table “New Customers”, and then later in the processing (after I’ve made the address changes) perform an append query to append all the records in this table to the customer table. There are currently over 20,000 customers in the table; I’m trying to add 755 new records. There are 365 records that are actually changes. When I run the append query, it appends 13 and tells me the rest have not been added due to key violations. Now the only key field is the customer number, and I already confirmed it’s not duplicate when I ran the Select query where I used the Left Outer join to create the “New Customers” table in the first place. So I run the append query again. Interestingly, it appends another 18 records now. A third run nets another 15 and so on. Eventually, after running the query 8 times, all records get appended to the customer table.

I’m running all this in VBA using SQL statements. What I’ve done as a workaround is determine how many records I should have (by issuing a DCOUNT against the New Customers table) and then executing the Query repeatedly (using dbs.Execute “Queryname” in a loop instead of using SQL) and incrementing a counter to count .RecordsAffected. When this counter reaches the dcount, I exit the loop and carry on.

My question: What would make a query only append 13 records when it’s obvious there really are no key violations since it’s able to append all the records if I run it 8 times in succession?

Has anyone run up against something like this before? Interestingly, I’ve refreshed my data from a backup, compacted and repaired the database and received EXACTLY the same result requiring 8 runs of the query with the same number appended each time. Very strange.

Reply | Quote

Here’s an interesting situation that I’m wondering if anyone has an answer to. I’ve devised a solution, but it’s less-than-elegant, and seems like it should be unnecessary.

I receive a file of “address changes” each month which may contain address changes or new records. By comparing the two tables in a Left Outer join, I can determine which “address changes” do not exist in the current customer table and will need to be appended. I perform a make-table query to place these new records in their own table “New Customers”, and then later in the processing (after I’ve made the address changes) perform an append query to append all the records in this table to the customer table. There are currently over 20,000 customers in the table; I’m trying to add 755 new records. There are 365 records that are actually changes. When I run the append query, it appends 13 and tells me the rest have not been added due to key violations. Now the only key field is the customer number, and I already confirmed it’s not duplicate when I ran the Select query where I used the Left Outer join to create the “New Customers” table in the first place. So I run the append query again. Interestingly, it appends another 18 records now. A third run nets another 15 and so on. Eventually, after running the query 8 times, all records get appended to the customer table.

I’m running all this in VBA using SQL statements. What I’ve done as a workaround is determine how many records I should have (by issuing a DCOUNT against the New Customers table) and then executing the Query repeatedly (using dbs.Execute “Queryname” in a loop instead of using SQL) and incrementing a counter to count .RecordsAffected. When this counter reaches the dcount, I exit the loop and carry on.

My question: What would make a query only append 13 records when it’s obvious there really are no key violations since it’s able to append all the records if I run it 8 times in succession?

Has anyone run up against something like this before? Interestingly, I’ve refreshed my data from a backup, compacted and repaired the database and received EXACTLY the same result requiring 8 runs of the query with the same number appended each time. Very strange.

Reply | Quote

Here’s an interesting situation that I’m wondering if anyone has an answer to. I’ve devised a solution, but it’s less-than-elegant, and seems like it should be unnecessary.

I receive a file of “address changes” each month which may contain address changes or new records. By comparing the two tables in a Left Outer join, I can determine which “address changes” do not exist in the current customer table and will need to be appended. I perform a make-table query to place these new records in their own table “New Customers”, and then later in the processing (after I’ve made the address changes) perform an append query to append all the records in this table to the customer table. There are currently over 20,000 customers in the table; I’m trying to add 755 new records. There are 365 records that are actually changes. When I run the append query, it appends 13 and tells me the rest have not been added due to key violations. Now the only key field is the customer number, and I already confirmed it’s not duplicate when I ran the Select query where I used the Left Outer join to create the “New Customers” table in the first place. So I run the append query again. Interestingly, it appends another 18 records now. A third run nets another 15 and so on. Eventually, after running the query 8 times, all records get appended to the customer table.

I’m running all this in VBA using SQL statements. What I’ve done as a workaround is determine how many records I should have (by issuing a DCOUNT against the New Customers table) and then executing the Query repeatedly (using dbs.Execute “Queryname” in a loop instead of using SQL) and incrementing a counter to count .RecordsAffected. When this counter reaches the dcount, I exit the loop and carry on.

My question: What would make a query only append 13 records when it’s obvious there really are no key violations since it’s able to append all the records if I run it 8 times in succession?

Has anyone run up against something like this before? Interestingly, I’ve refreshed my data from a backup, compacted and repaired the database and received EXACTLY the same result requiring 8 runs of the query with the same number appended each time. Very strange.

Reply | Quote

Here’s an interesting situation that I’m wondering if anyone has an answer to. I’ve devised a solution, but it’s less-than-elegant, and seems like it should be unnecessary.

I receive a file of “address changes” each month which may contain address changes or new records. By comparing the two tables in a Left Outer join, I can determine which “address changes” do not exist in the current customer table and will need to be appended. I perform a make-table query to place these new records in their own table “New Customers”, and then later in the processing (after I’ve made the address changes) perform an append query to append all the records in this table to the customer table. There are currently over 20,000 customers in the table; I’m trying to add 755 new records. There are 365 records that are actually changes. When I run the append query, it appends 13 and tells me the rest have not been added due to key violations. Now the only key field is the customer number, and I already confirmed it’s not duplicate when I ran the Select query where I used the Left Outer join to create the “New Customers” table in the first place. So I run the append query again. Interestingly, it appends another 18 records now. A third run nets another 15 and so on. Eventually, after running the query 8 times, all records get appended to the customer table.

I’m running all this in VBA using SQL statements. What I’ve done as a workaround is determine how many records I should have (by issuing a DCOUNT against the New Customers table) and then executing the Query repeatedly (using dbs.Execute “Queryname” in a loop instead of using SQL) and incrementing a counter to count .RecordsAffected. When this counter reaches the dcount, I exit the loop and carry on.

My question: What would make a query only append 13 records when it’s obvious there really are no key violations since it’s able to append all the records if I run it 8 times in succession?

Has anyone run up against something like this before? Interestingly, I’ve refreshed my data from a backup, compacted and repaired the database and received EXACTLY the same result requiring 8 runs of the query with the same number appended each time. Very strange.

Reply | Quote

Leave a problem for a while and the answer comes to you. It’s autonumber. The system is trying to add new records with an autonumber value lower than the highest value in the Customer table. After adding 13 records, it finds a duplicate and stops. That explains why it repeats with the same pattern after a restore from backup. How can I reset the autonumber to start at one more than the highest value and still (ideally) use SQL commands to run the append? I’d like to use SQL commands to keep the process running quickly.

Reply | Quote

Leave a problem for a while and the answer comes to you. It’s autonumber. The system is trying to add new records with an autonumber value lower than the highest value in the Customer table. After adding 13 records, it finds a duplicate and stops. That explains why it repeats with the same pattern after a restore from backup. How can I reset the autonumber to start at one more than the highest value and still (ideally) use SQL commands to run the append? I’d like to use SQL commands to keep the process running quickly.

Reply | Quote

Leave a problem for a while and the answer comes to you. It’s autonumber. The system is trying to add new records with an autonumber value lower than the highest value in the Customer table. After adding 13 records, it finds a duplicate and stops. That explains why it repeats with the same pattern after a restore from backup. How can I reset the autonumber to start at one more than the highest value and still (ideally) use SQL commands to run the append? I’d like to use SQL commands to keep the process running quickly.

Reply | Quote

Leave a problem for a while and the answer comes to you. It’s autonumber. The system is trying to add new records with an autonumber value lower than the highest value in the Customer table. After adding 13 records, it finds a duplicate and stops. That explains why it repeats with the same pattern after a restore from backup. How can I reset the autonumber to start at one more than the highest value and still (ideally) use SQL commands to run the append? I’d like to use SQL commands to keep the process running quickly.

Reply | Quote

Leave a problem for a while and the answer comes to you. It’s autonumber. The system is trying to add new records with an autonumber value lower than the highest value in the Customer table. After adding 13 records, it finds a duplicate and stops. That explains why it repeats with the same pattern after a restore from backup. How can I reset the autonumber to start at one more than the highest value and still (ideally) use SQL commands to run the append? I’d like to use SQL commands to keep the process running quickly.

Reply | Quote

Leave a problem for a while and the answer comes to you. It’s autonumber. The system is trying to add new records with an autonumber value lower than the highest value in the Customer table. After adding 13 records, it finds a duplicate and stops. That explains why it repeats with the same pattern after a restore from backup. How can I reset the autonumber to start at one more than the highest value and still (ideally) use SQL commands to run the append? I’d like to use SQL commands to keep the process running quickly.

Reply | Quote

Leave a problem for a while and the answer comes to you. It’s autonumber. The system is trying to add new records with an autonumber value lower than the highest value in the Customer table. After adding 13 records, it finds a duplicate and stops. That explains why it repeats with the same pattern after a restore from backup. How can I reset the autonumber to start at one more than the highest value and still (ideally) use SQL commands to run the append? I’d like to use SQL commands to keep the process running quickly.

Reply | Quote

Leave a problem for a while and the answer comes to you. It’s autonumber. The system is trying to add new records with an autonumber value lower than the highest value in the Customer table. After adding 13 records, it finds a duplicate and stops. That explains why it repeats with the same pattern after a restore from backup. How can I reset the autonumber to start at one more than the highest value and still (ideally) use SQL commands to run the append? I’d like to use SQL commands to keep the process running quickly.

Reply | Quote

If your field is truly an autonumber, the system should be trying to set it to the current value. Is there something else going on here that I’ve misunderstood?

Reply | Quote

There’s definitely SOMETHING going on here that I don’t understand because the system seems to be choosing an autonumber value that is within the range of the existing records. I haven’t tested this theory exhaustively, but it’s the only thing I can think of that would cause this type of behaviour. I have two indexed fields in this table – an autonumber CustomerID (the primary key) and the Customer Number itself. Customer numbers can be changed (in case of a merger, for example), so I didn’t make it the key to the table. Perhaps I should have….

Reply | Quote

You indicate “by comparing the two tables…”

What field(s) are you comparing to determine if the records in the new data is a new record or modification of an existing record in the existing table?

Are you using the ID setup as an autonumber as the key and then checking to see if that autonumber exists? If you are using the ID as the field driving your information I see where you could have a problem if you attempt to run an append query.

I would do this in a two step process if you are using the ID.

Step 1 – Where the id’s are the same, use an update query to update the fields that are modified.

Step 2 – For all id’s in the new file that are not in the existing file, you will need to run an append query to append the records, however, this is where the issue comes in. The new ID’s will most likely NOT be the same as those loaded as the system will generate them. You may want to use a different field to store the ID so it remains a constant.

Could you please provide some more information on what fields you are comparing in the tables. Thanks.

Reply | Quote

I’m comparing Customer Numbers, since that’s a “known entity”. The system assigns the autonumbers, so the individual generating the address change file that I receive has no idea what the CustomerID might be. So really, when I’m looking for duplicates, I’m only concerned with the Customer Numbers, and that’s what I compare. The file being appended doesn’t contain any CustomerID numbers – I’m letting the system generate them on its own as part of the Append process. That seems to be what’s causing the problem. Will it hurt to run the append 8 times? Am I going to have CustomerID numbers added to the new records that match records that were deleted previously? They couldn’t have been deleted unless the related records were deleted first, so it shouldn’t be an issue as far as data integrity. I’m just surprised that the CustomerIDs are assigned within the range of existing CustomerIDs.

Reply | Quote