Microsoft Office Forums Converting a Select statement in Excel to an update statement

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-31-2018, 12:59 AM
shabbaranks shabbaranks is offline Converting a Select statement in Excel to an update statement Windows 7 64bit Converting a Select statement in Excel to an update statement Office 2007
Advanced Beginner
Converting a Select statement in Excel to an update statement
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default Converting a Select statement in Excel to an update statement

Hi,




I need to update multiple records in an SQL database, I have imported the rows of data which I need to update into Excel. Once I have amended those records how to I then convert that select query to an update query?


Thanks
Reply With Quote
  #2  
Old 10-31-2018, 02:29 AM
ArviLaanemets ArviLaanemets is offline Converting a Select statement in Excel to an update statement Windows 8 Converting a Select statement in Excel to an update statement Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

Link the Excel table (Read-only link, so the table in Excel workbook doesn't get locked, and can be edited) into Access.

In Access, create a procedure which updates Access table with data from linked Excel table. Run this procedure from some Access database event (button click, form open, etc.)
Reply With Quote
  #3  
Old 10-31-2018, 04:56 AM
shabbaranks shabbaranks is offline Converting a Select statement in Excel to an update statement Windows 7 64bit Converting a Select statement in Excel to an update statement Office 2007
Advanced Beginner
Converting a Select statement in Excel to an update statement
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Link the Excel table (Read-only link, so the table in Excel workbook doesn't get locked, and can be edited) into Access.

In Access, create a procedure which updates Access table with data from linked Excel table. Run this procedure from some Access database event (button click, form open, etc.)
Hi - thanks for your response. I have set this up and when I tested it (updating a copy of the linked table) it worked fine. If I try to update the live table I get the message "operation must use an updatable query" yet it is an update query?


Thanks
Reply With Quote
  #4  
Old 10-31-2018, 06:11 AM
ArviLaanemets ArviLaanemets is offline Converting a Select statement in Excel to an update statement Windows 8 Converting a Select statement in Excel to an update statement Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

You need:
1. A table in Excel workbook, where all data editing in Excel is made. Let it be ExcelEditTable. NB! This must not be a result table of query you read data from Access into Excel!;
2. In case you use an ODBC query to read data from Access into Excel at start, then you'll have a query result table. Let it be ExcelImportTable. From this table you must get data into ExcelEditTable - coping data manually, using script, etc. In case you use some Access procedure to write data directly into ExcelEditTable, then you don't have this one;
3. In Access, a table where you need to save edited data finally. It is the table, you are using in Access forms, procedures, etc. (except the procedure updating data from AccessLinkedTable). Let it be AccessTable;
4. In Access, the read-only link (ODBC query) to ExcelEditTable in Excel workbook. Let it be AccessLinkedTable. From Access side, this table is used only by single procedure to update data in AccessTable;

Now my question - which one is the one you are trying to update (i.e. 'live table'), and is giving you this message?
Reply With Quote
  #5  
Old 10-31-2018, 06:31 AM
shabbaranks shabbaranks is offline Converting a Select statement in Excel to an update statement Windows 7 64bit Converting a Select statement in Excel to an update statement Office 2007
Advanced Beginner
Converting a Select statement in Excel to an update statement
 
Join Date: Mar 2011
Posts: 87
shabbaranks is on a distinguished road
Default

Hi,
Hopefully Im not confusing my original question.


Im using access to create and run the query but I would like the data to be in SQL.
Currently I have an Excel spreadsheet which is a replica of the table - linked to Access. This spreadsheet has the missing records in Column A.


I have the live SQL data\table linked to Access (which has the missing records) and I have created the following query to update the SQL table with the missing records


UPDATE Data INNER JOIN Table1 ON Data.unique_id = Table1.unique_id SET Table1 = [Data].[Record]
WHERE (((Table1.ST_Period)="07") AND ((Table1.ST_Year)="18"));


If I test this against a local copy of the SQL table it works if I try and run the update query against the SQL table it errors as above operation must use an updatable query.


If I try to run an update query from the working local table to the sql table it errors also with operation must use an updatable query.
Thanks
Reply With Quote
  #6  
Old 10-31-2018, 11:47 PM
ArviLaanemets ArviLaanemets is offline Converting a Select statement in Excel to an update statement Windows 8 Converting a Select statement in Excel to an update statement Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

Full Update of one table based on another table need up to 3 steps:
1. A delete query to remove records not present in source table from target table;
2. An update query to update all remaining records in target table with info from matching records in source table;
3. An insert query to add into target table records present in source table and missing in target table.

Or you simply clear all info from target table, and insert it anew from source table. In case you have backend database in SQL server, it will something like
Code:
DELETE * FROM TargetTable
 
INSERT INTO TargetTable
SELECT * FROM SourceTable
NB! As you link the Excel table into FrontEnd, the easiest way to run the insert query will be in front-end too. You update SQL Server database table linked into front-end with new data.

NB! When your database is multi-user one, you may have locking problems when you want to update the whole table in back-end, and there are several users using the app at same time.

You can also update the table in SQL Server running a stored procedure in SQL server. The stored procedure reads data directly from Excel table, and updates the info in SQL Server Database table with it. You can call the stored procedure from your Access front-end using a pass-throw query, or you can set up a job, which runs the procedure on schedule.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
EXCEL IF STATEMENT Help bksmith Excel 2 07-18-2017 09:53 AM
Directional help in constructing an Excel 2016 If, Or, And Then Formula Statement Gwilson Excel 3 04-13-2017 11:31 AM
Help! figuring if statement with 2 vlookups excel 2013 MWATKINS7111 Excel 3 02-24-2017 12:58 AM
select statement in macro Nicolette Excel 1 11-19-2013 01:38 AM
Converting a Select statement in Excel to an update statement Select statement to a new document ishaw Word 1 10-17-2011 02:23 AM


All times are GMT -7. The time now is 01:15 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft