?
Solved

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

Posted on 2016-11-10
5
Medium Priority
?
152 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
5 Comments
 
LVL 31

Expert Comment

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

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 40

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 40

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

862 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