?
Solved

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

Posted on 2016-11-10
5
Medium Priority
?
123 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 30

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 38

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 38

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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