Link to home
Start Free TrialLog in
Avatar of Jaime Martinez
Jaime MartinezFlag for United States of America

asked on

unable to execute Update query in Access 2016 on a table linked to SQL.

I am working with Access 2016 and use it as my front end to write data to a table in SQL Server. I introduced a linked excel file so I can update a field on the SQL Table. when i click run it does not update my field (dbo_WIP.ItsInHouseDate). actually nothing happens, no error message at all. I am able to update the filed manually through Access when I open dbo_WIP in Datasheet View so it should be a rights issue.

Any help will be great. thank you in advanced.

dbo_WIP is my SQL Table
[SPECIAL ORDERS] is my linked excel file in access.

UPDATE dbo_WIP INNER JOIN [SPECIAL ORDERS] ON (dbo_WIP.OrdProdID = [SPECIAL ORDERS].PCODE) AND (dbo_WIP.PrdBuyNote = [SPECIAL ORDERS].[UE ORDER NUMBER])
SET dbo_WIP.ItsInHouseDate = [SPECIAL ORDERS]![PRODUCTION FINISH DATE];
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Try to upload samples to easily reproduce the issue.
Try to construct a SELECT statement before to see which rows are affected:
SELECT dbo_WIP.ItsInHouseDate, [SPECIAL ORDERS]![PRODUCTION FINISH DATE]
FROM dbo_WIP 
INNER JOIN [SPECIAL ORDERS] ON (dbo_WIP.OrdProdID = [SPECIAL ORDERS].PCODE) AND (dbo_WIP.PrdBuyNote = [SPECIAL ORDERS].[UE ORDER NUMBER]);

Open in new window


This is the nature of UPDATE command. If it does not update any rows then nothing happens, no message, no effect.
I think the problem is with the JOIN condition between your tables this is way is does not affect any rows. Try the SELECT first.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jaime Martinez

ASKER

Thank you Pat, I was not aware of the  limitation between the two.

I took the Excel file and imported it to the SQL database, its was an easy task, now I'm able to represent it any way I want it.

now that tough part is going to be automating that excel import.
It's not tough at all, use the DoCmd.TransferSpreadsheet method and specify import as the option and the temp table as the target.  Don't forget to run the pass-through query first each time to empty the table before importing the next spreadsheet.

If that was not enough to solve your problem, please start a new thread.  Don't forget to close this one.