[AccessD] Switch from Providex to SQL stops append query

Dear List:
 
I was on call to a local company - a small manufacturer - who has an ERP
system.  They also have a custom Access database - front and back ends -
which supplement this application.  The BE of this custom app is loaded
during start up of the FE by a series of macros which call queries (delete
and append) that extract data from the ERP system and append it to tables in
the custom BE. 
 
Over the weekend they switched their main app's database from Providex to
SQL.  The upgrade went well but one of the append queries called by the
startup macros does not work.  This query extracts sales order data from the
SQL (formerly Providex) database to update order tables in the custom Access
back end. The data is extracted from the SQL (formerly PROVIDEX) database
through ODBC connections.
 
I spent a couple of hours there this morning but was unable to find the
solution.  Am going back tomorrow morning.
 
The append query says most of the records cannot be appended due to
validation rule violations.    Oddly, 5 of the nearly 2000 record DO get
appended.  The unique thing about those records is that there are two fields
in the data to be appended which both have data.  In all the other records
which are rejected, either one or both of those fields is null.  Dropping
those two fields from the append query lowers the number of records not
appended dramatically, but not completely.  And those fields in the target
table are plain vanilla text, not required.  Anyway, they do need to be in
the appended data.
 
I ran the query on the old Providex db and the new one on the SQL db and
compared the data extracted and could see no difference.
 
The target table has two fields as the primary key - a sales order number
and the line number of the sales order. So I deleted all the fields from the
append query except the two primary key fields.  The append fails due to key
violations for ALL of the records - none make it.  Changing this query to a
select query I looked at the two fields.  There are no duplicates.
 
I started again with the original append query and began to delete the
fields one by one to see if I could find the culprit.  After deleting
certain fields, the number of records not appended dropped by a few records.
But none appeared to be the sole culprit.
 
So I'm kind of stumped but I'm thinking that there's some difference in the
data extracted through the ODBC connection to the SQL db that is not
visible.  
 
These queries have been running without problem for quite a while extracting
data from the Providex db and appending it to the custom Access BE.
 
Any ideas about what might be going on here? 
 
MTIA,
 
Rocky Smolin
Beach Access Software
858-259-4334
www.bchacc.com <http://www.bchacc.com/> 
www.e-z-mrp.com <http://www.e-z-mrp.com/> 
Skype: rocky.smolin