Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advice sought: update from text file (A97)

    Hello

    A datalogger creates a text file, and I am able to link to that file. Can successfully query data from the file (e.g qryLectroCountDelRead ). What I want to accomplish is to relate qryLectroCountDelRead to aTblTank through an intermediary table (utblDelivery) , and to update several fields in aTblTank .

    Easy enough to accomplish if all tables are real tables, but when I try to execute an Update query that includes the linked table, I get this error message: "Updating data in a linked table is not supported by this ISAM" (error #3616). Here is the SQL statement:

    UPDATE aTblTank INNER JOIN (qryLectroCountDelRead INNER JOIN uTblDelivery ON qryLectroCountDelRead.DeliveryID = uTblDelivery.DeliveryID) ON aTblTank.TankID = uTblDelivery.TankID SET aTblTank.CurrentProductID = [qryLectroCountDelRead].[productid], aTblTank.CurrentOutstanding = [qryLectroCountDelRead].[airspace], aTblTank.CurrentInventoryDate = [qryLectroCountDelRead].[deliverydate], aTblTank.CurrentInventorySourceID = 10, aTblTank.InventoryUpdateReasonID = 0, aTblTank.InventoryUpdatedByEmployeeIDfk = EmployeeIDFromJetLogin();

    If I remove two of the fields (aTblTank.CurrentOutstanding and aTblTank.CurrentInventoryDate) from the SQL statement, then the error message no longer appears. Note, one field from qryLectroCountDelRead is still used as an update source:

    UPDATE aTblTank INNER JOIN (qryLectroCountDelRead INNER JOIN uTblDelivery ON qryLectroCountDelRead.DeliveryID = uTblDelivery.DeliveryID) ON aTblTank.TankID = uTblDelivery.TankID SET aTblTank.CurrentProductID = [qryLectroCountDelRead].[productid], aTblTank.CurrentInventorySourceID = 10, aTblTank.InventoryUpdateReasonID = 0, aTblTank.InventoryUpdatedByEmployeeIDfk = EmployeeIDFromJetLogin();


    Is there a way to include a linked text file in an update query without resorting to actually creating a temporary table? Thanks for any advice offered.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advice sought: update from text file (A97)

    If you import the text file instead of linking it, you can skip the step of creating a temporary table from the linked table.

Posting Permissions

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