Converting a Select statement in Excel to an update statement

[shabbaranks]

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

[ArviLaanemets]

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.)

[shabbaranks]

Originally Posted by ArviLaanemets

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

[ArviLaanemets]

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?

[shabbaranks]

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

[ArviLaanemets]

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

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.