Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query problem (Access XP)

    Another problem. I am trying to create an Update Query. The field that I am taking the data from is a linked table. When I try to run the update query I get the following error message, "Updating data in a linked table is not supported by this ISAM." I understand this to mean I can't do this, but what else does this mean? What is an ISAM? How should I create an "link" between these two fields?

    TIA

    Bret

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update Query problem (Access XP)

    What kind of a file are you linking to? You need to fill in the background because not everyone will have followed any previous threads on your problems. If it's a text file, then you can't update it like that.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query problem (Access XP)

    Thanks Charlotte,

    What I have is an inventory database. I use a Palm Pilot with a barcode scanner to hand count inventory in the restaurant. When I Hot Sync the Palm Pilot it creates a text file that I have linked a table to. In this table are four fields, Barcode, Description, Location and On Hand. "On Hand" is the field that I want to "link" to a field in another table of the database. I tried to create an update query that would transfer the data from the On Hand in the linked table, to the Products table "On Hand" field.

    When I try to run the Update Query I get the afore mentioned error. To me the error reads backwards. I am not trying to update the linked table I am trying to update from the linked table. In the query design view I have the following in the Update To: [Products].[On Hand] The field for this update is On Hand and the table is S01_data.

    What I am trying to accomplish here is to take repeating the input of data. I want to have all the data input only the one time when we enter it into the Palm Pilot.

    Thanks,

    Bret

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is there a way other than the update query?

    I was wondering if the same would work by creating a Hyperlink or Lookup for this field?

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Update Query problem (Access XP)

    Post the SQL of the query, this will give us a better understanding of what you are doing.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update Query problem (Access XP)

    If you're trying to pull information in from the linked table and update an Access table, you shouldn't be getting that error, so follow Pat's suggestion and post the SQL. It sounds like your query is at fault.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Is there a way other than the update query?

    Sorry, but I don't have a clue as to what you mean. A hyperlink doesn't make sense if you're trying to update a field, and a lookup is only useful if someone is entering data, not trying to update it from a query.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL of Update Query (Access XP)

    UPDATE Products INNER JOIN S01_data ON (Products.ProductName = S01_data.Description) AND (Products.Barcode = S01_data.Barcode) AND (Products.[On Hand] = S01_data.[On Hand]) SET S01_data.[On Hand] = Products.[On Hand];

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL of Update Query (Access XP)

    Hi Bret

    Your UPDATE statement has a join on Productname, Barcode and OnHand. I suspect you don't want it on OnHand so therefore you UPDATE statement:
    UPDATE Products INNER JOIN S01_data ON (Products.ProductName = S01_data.Description) AND (Products.Barcode = S01_data.Barcode) AND (Products.[On Hand] = S01_data.[On Hand]) SET S01_data.[On Hand] = Products.[On Hand];

    should probably read:
    UPDATE Products INNER JOIN S01_data ON (Products.ProductName = S01_data.Description) AND (Products.Barcode = S01_data.Barcode) SET S01_data.[On Hand] = Products.[On Hand];

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL of Update Query (Access XP)

    Thanks Pat, I tried your version of the SQL and got the same error message. By the way the only real data I do need to update from the S01_Data table to the Products table is the On Hand field. The other fields are the same information.

    Thanks again,

    Bret

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL of Update Query (Access XP)

    Hi Bret

    Your UPDATE query is trying to update the linked table, you will have to change it from:
    UPDATE Products INNER JOIN S01_data ON (Products.ProductName = S01_data.Description) AND (Products.Barcode = S01_data.Barcode) AND (Products.[On Hand] = S01_data.[On Hand]) SET S01_data.[On Hand] = Products.[On Hand];
    to:
    UPDATE Products INNER JOIN S01_data ON (Products.ProductName = S01_data.Description) AND (Products.Barcode = S01_data.Barcode) SET Products.[On Hand] = S01_data.[On Hand];

    HTH

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL of Update Query (Access XP)

    Pat,

    I have been having difficulty getting the file down to a size the lounge likes. Would you allow me to send you a copy directly?
    If you would prefer not I understand.

    Thanks,

    Bret

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL of Update Query (Access XP)

    Hi Bret
    Sure you can email it to me, however, I only have Access2000 so you will have to send me a A2000 format.
    BTW why do you want to send it to me?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  14. #14
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL of Update Query (Access XP)

    Thanks Pat, just to refresh memories, I am trying to create an Update Query that will take one field "On Hand" from a table created from a Palm Pilot import and update those numbers to the "On Hand" field in my Products table in my inventory database.

    As far as sending it to you, I have an email that appears to have come through the Lounge with you stating that it might be easier for everyone to help me if I post the database to the lounge minus any private info. But what is strange, I cannot find that message anywhere in the Lounge. I have searched the postings and haven't seen the original copy.

    The file size is down to 942 KB but when I try to upload it to the lounge I get an error message stating that it is too large.

    Thanks for all the help.

    Bret

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL of Update Query (Access XP)

    Hi Bret
    I noticed your UPDATE query is still trying to update the On Hand field in the S01_data table.

    If you want it to update the Products table then do the following:

    UPDATE Products INNER JOIN S01_data ON (Products.ProductName=S01_data.Description) AND (Products.Barcode=S01_data.Barcode) SET Products.[On Hand] = S01_data.[On Hand];

    Also I cannot access the S01_data table obviously.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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