#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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.) |
#3
|
|||
|
|||
Quote:
Thanks |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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! 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. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
EXCEL IF STATEMENT Help | bksmith | Excel | 3 | 07-27-2021 07:01 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 |
Select statement to a new document | ishaw | Word | 1 | 10-17-2011 02:23 AM |