Jaime Martinez
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];
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];
Try to upload samples to easily reproduce the issue.
Try to construct a SELECT statement before to see which rows are affected:
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.
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]);
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
If that was not enough to solve your problem, please start a new thread. Don't forget to close this one.