Update Query

[Bentley]

Hello,

I am trying to update a blank field from a field in a table that is linked to a spreadsheet. I believe I setup the query correctly. I have the two tables joined, I have the destination location set in the Field and Table parts of the query and in the Update To: row, I have the source table and field. However when the query is run, I get a 0 updates. Both fields in each table are labeled the same. Any ideas as to what I am doing incorrectly?

thanks,

Ben

[jwhite]

> I am trying to update a blank field from a field in a table that is linked to a spreadsheet.

Are you saying you created a LINK to an EXTERNAL Excel file? If so, linked Excel files are not updateable within Access, that I know of.

[Bentley]

Yes, we had to link the spreadsheet to get the information into access. However, I need the WO field to be duplicated into another table in order to use the lookups and combo boxes. Any ideas?

[jwhite]

If you need to modify the data then you will need to write a query that SELECTs the records from the linked-Excel sheet and INSERT them into your Access table - then you can modify the data.

[Bentley]

Correct, but how do you insert? I guess that is my actual question. I am stumped on how to get that to work.

[ssanfu]

Have you see this on Append queries?
http://www.w3schools.com/sql/sql_insert.asp

[Bentley]

an insert isn't what I am after I don't believe. Essentially I need the work order field from the linked table from excel to be placed into my work order audit table so my forms and lookups work.

[ssanfu]

And that record already exists in work order audit table???

You want to transfer data from a linked Excel spreadsheet to an Access table?
Your query options are "UPDATE" (an existing field) or "APPEND" (Insert Into - this adds a new record).

Even with VBA, you still must either update an existing record or add a new record.

[Bentley]

I need to transfer the work order field from the linked excel table to a field in a different table. I am having trouble with the update and append query as neither seem to update the audit table as I get a "0 row(s) will be updated".

[Micron]

See if you can create a Select query to return the work order data from the linked spreadsheet in Access. If you can, and include whatever fields you will require at the Update stage, you're halfway there. You might be able to convert the Select query to an Update.

I get the impression from reading replies that there might be some confusion about updating versus appending. You can't update a record that doesn't exist, so let's be sure which it is you need. Sometimes, it's necessary to append a record, then from a source that couldn't be involved in the original append, you perform the update on the newly appended record in a different query. You might have to post some data that resembles the original tables and records you are trying to affect, and the sql statement you're using to attempt that.