Stuart McLachlan
stuart at lexacorp.com.pg
Tue Dec 11 01:58:44 CST 2012
If they migrated the data from Providex to SQL Server, it 's quite possible that some Nulls got changed to empty strings in the process. One option would be to run an update query on the SQL Server changing all empty strings to Nulls. Another, probably better, would be to modify the import query to change empty strings to Nulls during the import. -- Stuart On 10 Dec 2012 at 22:54, Rocky Smolin wrote: > I think allow zero length is NO. It worked on the old Providex db. SO it > seems like it should work the same extracting from the SQL db but I'll try > modifying the BE table tomorrow. I was trying to solve it without modifying > the BE because I don't know the whole FE app and what it does and don't want > to open that can of worms. I also considered deleting the compound PK and > replacing with an Autonumber ID but was holding off on that alternative for > the same reason. > > Thanks. > > Rocky > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan > Sent: Monday, December 10, 2012 10:43 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Switch from Providex to SQL stops append query > > 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 > > > > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >