Update Query problem (Access XP)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Post the SQL of the query, this will give us a better understanding of what you are doing.
Pat

Reply | Quote

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.

Reply | Quote

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];

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks Pat, it worked and it didn’t work. It updated all of the Descriptions to the words “On Hand” :-0 I am just so frustrated by this thing. I can’t believe that a database has to be this complicated. I must be doing something completely wrong.
I can see why so many people completely abandon their way of doing things and buy a piece of software that forces them to change how they do things.

Thanks for the help.

Bret

Bret

Reply | Quote

Hi Bret,
What worked, and what didn’t work?
I don’t see how your UPDATE query could change the Description.
Would you post your latest UPDATE query again so we can have a look at it.
Pat

Reply | Quote

Thanks Pat, here is the latest update Query. I changed all of the descriptions back to their correct names in the Products table. I tried running the last suggested Update query and now I get the “Updating data to a linked table is not supported” error.

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

Thanks for the help,

Bret

Reply | Quote

Linked text files are not updatable; it seems you can’t use them in an update query even if the only fields to be updated are not in the linked table but in a ‘normal’ Access table. Try importing the text file instead of linking it.

Reply | Quote

I am not wanting to update the linked text file I am wanting to update from the linked text file. So I can’t use a linked table to update from either? If this is correct and I “Import” the table will I have to do an Import everytime I want to update the data from the Palm Pilot? What would be the easiest and if not easy best way to do this?

Thank you,

Bret

Reply | Quote

You wrote:
<>
You can use a linked table to update from as long as it’s not a text file (or Excel sheet, etc??).

What you could do as Hans has suggested is to import the text file into an Access table. Then change your update query to use the Access table you imported into (instead of the linked text file).
Suppose your Access table was called tblTextFilePalmPilot then your update query could look like:

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

You must make sure that your field names are correct in tblTextFilePalmPilot. To do this you could import into the table tblTextFilePalmPilot that has been previously defined. What you will have to do first is to delete all records from table tblTextFilePalmPilot.
The VBA code to accomplish this follows:
DoCmd.RunSql “DELETE * FROM tblTextFilePalmPilot”
DoCmd.TransferText [, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename]

You are now ready to use your query to update the products. This could be the next line of VBA code:-
DoCmd.OpenQuery “qry that updates the OnHold in the Products table”

If I’m not making myself clear enough please post back.

HTH
Pat

Reply | Quote

That’s right, I don’t see a way of updating from a non-updatable linked table. You’ll find good ideas for automating the import in Patt’s reply. Using them, you could put together code that runs from a single click on a command button:

1. Delete all records from the imported table.
2. Import records from the text file into the table (use an import specification – you need to create it manually once, after that you can use it again and again).
3. Run the update query (that updates from the imported table).

The disadvantage of this method is that each time you import, the size of your database will probably grow, so you should compact the database afterwards. But then, it’s wise to compact a database regularly anyway.

Reply | Quote

Here is another copy of an Update Query I tried. This time it warns me that I am about to Update 0 rows, do I want to continue. What I changed was I created a relationship between the two “On Hand” fields instead of just the Barcode fields.

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

thanks,

Bret

Reply | Quote

This query certainly isn’t going to do what you want – it sets the value of [Products].[On Hand] to [S01_data].[On Hand] for those records that already have [Products].[On Hand] = [S01_data].[On Hand] …

Reply | Quote

Hello Hans,

So are you saying that even if the value of “On Hand” in Products is zero that the update won’t take place? So, if nothing else I need to enter a value in all the “On Hand” fields in Products from that point on will the Update Query work?! That, I think I could make an Update query do.

Thanks,

Bret

Reply | Quote

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

Reply | Quote

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.

Reply | Quote