[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2016-11-10
5
Medium Priority
?
142 Views
Last Modified: 2016-11-11
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];
0
Comment
Question by:Jaime Martinez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 41883222
Try to upload samples to easily reproduce the issue.
0
 
LVL 13

Expert Comment

by:Máté Farkas
ID: 41883893
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.
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41883960
Queries cannot update anything unless they are able to update everything.  Since queries linked to Excel are not updateble (due to a lawsuit that Microsoft lost some years ago), then by joining the SQL Server table to the spreadsheet, you have rendered the entire query not updateable.

You might be able to get around the issue by using a subquery to get the data from the spreadsheet if you only need a single column.  Otherwise, your simplest course of action will be to import the spreadsheet to a linked temp table in SQL Server and run the update query by joining to that table.

I suggest that the temp table be SQL Server because when you join a SS table to a Jet/ACE table, Access requests the entire contents of the SS table be brought down from the server so that Access can do the join locally.  Access will then send back to the server x update queries.  One for each row in the SS table that needs to be updated.

Before reloading the spreadsheet data into the SS temp table, run a passthrough query to delete all existing rows.  The Truncate command is the most efficient way of emptying a table completely since it does no logging.

TRUNCATE TABLE  TEMP_tblNearestReadDate;
0
 

Author Closing Comment

by:Jaime Martinez
ID: 41884149
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.
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 41884221
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question