Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  3. #3
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  5. #5
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  6. #6
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  7. #7
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  8. #8
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  9. #9
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  10. #10
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  11. #11
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  12. #12
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  13. #13
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  14. #14
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  15. #15
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •