Results 1 to 5 of 5
  1. #1
    tkosel is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    33

    ODBC Query Question

    I Inherited a Microsoft Access database that was written over 19 years ago. I did not write it, and the person who did is no longer available. It has been working fine till recently.
    I have looked at the source, and think it is unnecessarily complex. The Access Front End is linked to tables in a SQL Server Back End. There is a append Query that has all the sudden started to issue error messages. The contents of the append Query appear below.

    INSERT INTO DATA_CUST_QUOTE_LINE_TBL_1 ( PREV_QUOTED, NEW_QUOTED, PREV_EXTENDED_PRICE, NEW_EXTENDED_PRICE, ORDER_DATE, CUSTOMER_ID, QUOTE_DATE, WON_LOSS_DATE, QUOTE_ID, LINE_NO, PART_ID, CUSTOMER_PART_ID, DRAWING_ID, DRAWING_REV_NO, ORDER_QTY, UNIT_PRICE, EXTENDED_PRICE, CUSTOMER_NAME, PRODUCT_CODE, COMMODITY_CODE, CUST_GROUP, SALESREP_ID, TERRITORY, SIC_CODE, STATUS, CURRENCY_ID, TYPE, QUOTE_LINK_TYPE, PART_CREATE_DATE, MASTER_QUOTE_ID, MASTER_QUOTE_LINE, MASTER_USER7 )
    SELECT IIf([vCUST_QUOTE_LINE]![QUOTE_DATE]<[Forms]![New Part Performance]![FromDate],1,0) AS PREV_QUOTED, IIf([vCUST_QUOTE_LINE]![QUOTE_DATE] Between [Forms]![New Part Performance]![FromDate] And [Forms]![New Part Performance]![ToDate],1,0) AS NEW_QUOTED, IIf([vCUST_QUOTE_LINE]![QUOTE_DATE]<[Forms]![New Part Performance]![FromDate],[vCUST_QUOTE_LINE]![EXTENDED_PRICE],0) AS PREV_EXTENDED, IIf([vCUST_QUOTE_LINE]![QUOTE_DATE] Between [Forms]![New Part Performance]![FromDate] And [Forms]![New Part Performance]![ToDate],[vCUST_QUOTE_LINE]![EXTENDED_PRICE],0) AS NEW_EXTENDED, vCUST_QUOTE_LINE.ORDER_DATE, vCUST_QUOTE_LINE.CUSTOMER_ID, vCUST_QUOTE_LINE.QUOTE_DATE, vCUST_QUOTE_LINE.WON_LOSS_DATE, vCUST_QUOTE_LINE.QUOTE_ID, vCUST_QUOTE_LINE.LINE_NO, IIf(vPARTS!PART_ID Is Null,vCUST_QUOTE_LINE!PART_ID,vPARTS!PART_ID) AS PART_ID1, IIf(vPARTS!CUSTOMER_PART_ID Is Null,vCUST_QUOTE_LINE!CUSTOMER_PART_ID,vPARTS!CUST OMER_PART_ID) AS CUSTOMER_PART_ID1, IIf(vPARTS!DRAWING_ID Is Null,vCUST_QUOTE_LINE!DRAWING_ID,vPARTS!DRAWING_ID ) AS DRAWING_ID1, IIf(vPARTS!DRAWING_REV_NO Is Null,vCUST_QUOTE_LINE!DRAWING_REV_NO,vPARTS!DRAWIN G_REV_NO) AS DRAWING_REV_NO1, vCUST_QUOTE_LINE.ORDER_QTY, vCUST_QUOTE_LINE.UNIT_PRICE, vCUST_QUOTE_LINE.EXTENDED_PRICE, IIf(vPARTS!CUSTOMER_NAME Is Null,vCUST_QUOTE_LINE!CUSTOMER_NAME,vPARTS!CUSTOME R_NAME) AS CUSTOMER_NAME1, IIf(vPARTS!PRODUCT_CODE Is Null,vCUST_QUOTE_LINE!PRODUCT_CODE,vPARTS!PRODUCT_ CODE) AS PRODUCT_CODE1, IIf(vPARTS!COMMODITY_CODE Is Null,vCUST_QUOTE_LINE!COMMODITY_CODE,vPARTS!COMMOD ITY_CODE) AS COMMODITY_CODE1, IIf(vPARTS!CUSTOMER_GROUP Is Null,vCUST_QUOTE_LINE!CUST_GROUP,vPARTS!CUSTOMER_G ROUP) AS CUSTOMER_GROUP1, vCUST_QUOTE_LINE.SALESREP_ID, vCUST_QUOTE_LINE.TERRITORY, IIf(vPARTS!CUSTOMER_SIC_CODE Is Null,vCUST_QUOTE_LINE!SIC_CODE,vPARTS!CUSTOMER_SIC _CODE) AS CUSTOMER_SIC_CODE1, vCUST_QUOTE_LINE.STATUS, vCUST_QUOTE_LINE.CURRENCY_ID, 'Q' AS TYPE, IIf(vPARTS!PART_ID Is Null,Null,'M') AS QUOTE_LINK_TYPE, vPARTS.CREATE_DATE, vPARTS.QUOTE_ID, vPARTS.QUOTE_LINE, vPARTS.USER_7
    FROM vCUST_QUOTE_LINE LEFT JOIN vPARTS ON (vCUST_QUOTE_LINE.CUSTOMER_PART_ID = vPARTS.CUSTOMER_PART_ID) AND (vCUST_QUOTE_LINE.LINE_NO = vPARTS.QUOTE_LINE) AND (vCUST_QUOTE_LINE.QUOTE_ID = vPARTS.QUOTE_ID)
    WHERE (((vCUST_QUOTE_LINE.CUSTOMER_ID) Between [Forms]![New Part Performance]![CFrom] And [Forms]![New Part Performance]![CTo]) AND ((vCUST_QUOTE_LINE.QUOTE_DATE) Between [Forms]![New Part Performance]![txtFromDate] And [Forms]![New Part Performance]![txtToDate])) OR (((vCUST_QUOTE_LINE.ORDER_DATE) Between [Forms]![New Part Performance]![txtFromDate] And [Forms]![New Part Performance]![txtToDate]) AND ((vCUST_QUOTE_LINE.CUSTOMER_ID) Between [Forms]![New Part Performance]![CFrom] And [Forms]![New Part Performance]![CTo]));



    The error message issued appears below.

    Click image for larger version. 

Name:	capture.jpg 
Views:	11 
Size:	76.6 KB 
ID:	46821



    Can anyone help me solve this issue?

  2. #2
    tkosel is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    33
    Additional information. If I change the type of query to a select query, I get a more succinct message which is "ODBC Call Failed" Still does not work.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've changed this to something more readable (perhaps not the best) but suspect the issue is with the ODBC driver

    Code:
    INSERT INTO DATA_CUST_QUOTE_LINE_TBL_1  ( PREV_QUOTED 
        ,NEW_QUOTED 
        ,PREV_EXTENDED_PRICE 
        ,NEW_EXTENDED_PRICE 
        ,ORDER_DATE 
        ,CUSTOMER_ID 
        ,QUOTE_DATE 
        ,WON_LOSS_DATE 
        ,QUOTE_ID 
        ,LINE_NO 
        ,PART_ID 
        ,CUSTOMER_PART_ID 
        ,DRAWING_ID 
        ,DRAWING_REV_NO 
        ,ORDER_QTY 
        ,UNIT_PRICE 
        ,EXTENDED_PRICE 
        ,CUSTOMER_NAME 
        ,PRODUCT_CODE 
        ,COMMODITY_CODE 
        ,CUST_GROUP 
        ,SALESREP_ID 
        ,TERRITORY 
        ,SIC_CODE 
        ,STATUS 
        ,CURRENCY_ID 
        ,TYPE 
        ,QUOTE_LINK_TYPE 
        ,PART_CREATE_DATE 
        ,MASTER_QUOTE_ID 
        ,MASTER_QUOTE_LINE 
        ,MASTER_USER7 )
    SELECT IIf([vCUST_QUOTE_LINE]![QUOTE_DATE]<[Forms]![New Part Performance]![FromDate] >
        ,1 
        ,0)  AS PREV_QUOTED 
        ,IIf([vCUST_QUOTE_LINE]![QUOTE_DATE] Between [Forms]![New Part Performance]![FromDate] And [Forms]![New Part Performance]![ToDate] 
        ,1 
        ,0)  AS NEW_QUOTED 
        ,IIf([vCUST_QUOTE_LINE]![QUOTE_DATE]<[Forms]![New Part Performance]![FromDate] >
        ,[vCUST_QUOTE_LINE]![EXTENDED_PRICE] 
        ,0)  AS PREV_EXTENDED 
        ,IIf([vCUST_QUOTE_LINE]![QUOTE_DATE] Between [Forms]![New Part Performance]![FromDate] And [Forms]![New Part Performance]![ToDate] 
        ,[vCUST_QUOTE_LINE]![EXTENDED_PRICE] 
        ,0)  AS NEW_EXTENDED 
        ,vCUST_QUOTE_LINE.ORDER_DATE 
        ,vCUST_QUOTE_LINE.CUSTOMER_ID 
        ,vCUST_QUOTE_LINE.QUOTE_DATE 
        ,vCUST_QUOTE_LINE.WON_LOSS_DATE 
        ,vCUST_QUOTE_LINE.QUOTE_ID 
        ,vCUST_QUOTE_LINE.LINE_NO 
        ,IIf(vPARTS!PART_ID Is Null 
        ,vCUST_QUOTE_LINE!PART_ID 
        ,vPARTS!PART_ID)  AS PART_ID1 
        ,IIf(vPARTS!CUSTOMER_PART_ID Is Null 
        ,vCUST_QUOTE_LINE!CUSTOMER_PART_ID 
        ,vPARTS!CUST OMER_PART_ID)  AS CUSTOMER_PART_ID1 
        ,IIf(vPARTS!DRAWING_ID Is Null 
        ,vCUST_QUOTE_LINE!DRAWING_ID 
        ,vPARTS!DRAWING_ID  )  AS DRAWING_ID1 
        ,IIf(vPARTS!DRAWING_REV_NO Is Null 
        ,vCUST_QUOTE_LINE!DRAWING_REV_NO 
        ,vPARTS!DRAWIN G_REV_NO)  AS DRAWING_REV_NO1 
        ,vCUST_QUOTE_LINE.ORDER_QTY 
        ,vCUST_QUOTE_LINE.UNIT_PRICE 
        ,vCUST_QUOTE_LINE.EXTENDED_PRICE 
        ,IIf(vPARTS!CUSTOMER_NAME Is Null 
        ,vCUST_QUOTE_LINE!CUSTOMER_NAME 
        ,vPARTS!CUSTOME R_NAME)  AS CUSTOMER_NAME1 
        ,IIf(vPARTS!PRODUCT_CODE Is Null 
        ,vCUST_QUOTE_LINE!PRODUCT_CODE 
        ,vPARTS!PRODUCT_ CODE)  AS PRODUCT_CODE1 
        ,IIf(vPARTS!COMMODITY_CODE Is Null 
        ,vCUST_QUOTE_LINE!COMMODITY_CODE 
        ,vPARTS!COMMOD ITY_CODE)  AS COMMODITY_CODE1 
        ,IIf(vPARTS!CUSTOMER_GROUP Is Null 
        ,vCUST_QUOTE_LINE!CUST_GROUP 
        ,vPARTS!CUSTOMER_G ROUP)  AS CUSTOMER_GROUP1 
        ,vCUST_QUOTE_LINE.SALESREP_ID 
        ,vCUST_QUOTE_LINE.TERRITORY 
        ,IIf(vPARTS!CUSTOMER_SIC_CODE Is Null 
        ,vCUST_QUOTE_LINE!SIC_CODE 
        ,vPARTS!CUSTOMER_SIC _CODE)  AS CUSTOMER_SIC_CODE1 
        ,vCUST_QUOTE_LINE.STATUS 
        ,vCUST_QUOTE_LINE.CURRENCY_ID 
        ,'Q'  AS TYPE 
        ,IIf(vPARTS!PART_ID Is Null 
        ,Null 
        ,'M')  AS QUOTE_LINK_TYPE 
        ,vPARTS.CREATE_DATE 
        ,vPARTS.QUOTE_ID 
        ,vPARTS.QUOTE_LINE 
        ,vPARTS.USER_7
    FROM vCUST_QUOTE_LINE  
    LEFT JOIN vPARTS  ON (vCUST_QUOTE_LINE.CUSTOMER_PART_ID  = vPARTS.CUSTOMER_PART_ID) AND (vCUST_QUOTE_LINE.LINE_NO  = vPARTS.QUOTE_LINE) AND (vCUST_QUOTE_LINE.QUOTE_ID  = vPARTS.QUOTE_ID)
    WHERE (((vCUST_QUOTE_LINE.CUSTOMER_ID) Between [Forms]![New Part Performance]![CFrom] And [Forms]![New Part Performance]![CTo]) AND ((vCUST_QUOTE_LINE.QUOTE_DATE) Between [Forms]![New Part Performance]![txtFromDate] And [Forms]![New Part Performance]![txtToDate])) OR (((vCUST_QUOTE_LINE.ORDER_DATE) Between [Forms]![New Part Performance]![txtFromDate] And [Forms]![New Part Performance]![txtToDate]) AND ((vCUST_QUOTE_LINE.CUSTOMER_ID) Between [Forms]![New Part Performance]![CFrom] And [Forms]![New Part Performance]![CCto]))

  4. #4
    tkosel is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    33
    I can view both of the involved tables fine. The ODBC driver allows me to look at both tables in datasheet view with no errors .

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    that is kind of irrelevant - big difference between viewing data and adding new records

    Reviewing your error message in post #1 it is suggesting you are trying to append a text value to an integer field. It may be that the text values consist of numeric characters and the ODBC driver needs updating to allow that to happen (maybe you have had a sql server update which is less forgiving for earlier ODBC drivers).

    If it is not a ODBC driver problem then perhaps you have now got none numeric characters in the field which points to either an issue with your data or one of your calculations. Or perhaps the field in sql server was a text datatype and has been changed to int. But the tone of your post is that 'nothing has changed' so I discounted those possibilities. The value 'R-1' should help you to focus in on where the problem is. Not having seen that particular error before, it might be #245 is telling you which row to look at. You'll have to check

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ODBC Question
    By Bob McClellan in forum Database Design
    Replies: 3
    Last Post: 06-11-2015, 01:11 PM
  2. MS Ofc2010-ODBC linking question
    By ptolson in forum Queries
    Replies: 3
    Last Post: 08-22-2012, 04:01 PM
  3. Qucik question about the ODBC driver
    By baseborn in forum Access
    Replies: 1
    Last Post: 12-14-2010, 06:11 AM
  4. Replies: 5
    Last Post: 06-24-2010, 08:00 AM
  5. Replies: 0
    Last Post: 10-03-2006, 03:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums