Stuart McLachlan
stuart at lexacorp.com.pg
Tue Dec 11 00:42:53 CST 2012
Does the targett table have "Allow Zero length" = "Yes" on the text fields? If not, that could be the problem. -- Stuart On 10 Dec 2012 at 22:18, Rocky Smolin wrote: > 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 > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >