Solved

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

Posted on 2016-11-10
5
93 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 12

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 36

Accepted Solution

by:
PatHartman earned 500 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 36

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 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