I have a csv file that is extracted daily. I created a query in MS Access that reads the data from this csv file and updates the corresponding rows in the SharePoint list "sp_list", but only the rows that have had some value change in one of the columns. This is my SQL code in MS Access:
UPDATE sp_list
INNER JOIN csv_file
ON sp_list.[cod_id] = csv_file.[cod_id] & ''
SET sp_list.manager = csv_file.[manager],
sp_list.vendor_code = csv_file.[vendor_code],
sp_list.vendor_name = csv_file.[vendor_name]
WHERE
csv_file.[manager] = 'Test'
AND (
(sp_list.manager & '') <> (csv_file.[manager] & '') OR
(sp_list.vendor_code & '') <> (csv_file.[vendor_code] & '') OR
(sp_list.vendor_name & '') <> (csv_file.[vendor_name] & '')
);
When I test my code in the MS Access preview mode, it does not show any error and shows the rows that it will update, but when I run this query, the error
Updating Linked Table not Supported in this ISAM
appears to me and the query does not execute.
What can I do to make my update query work?
If you can, use DLOOKUP function to take values from csv file.
In this case, Access assumes that the attached file will not be changed (updated).
See example
UPDATE sp_list
INNER JOIN csv_file
ON sp_list.[cod_id] = csv_file.[cod_id]
SET sp_list.manager = DLookUp("manager", "csv_file", "cod_id =" & sp_list.[cod_id])
,sp_list.vendor_code = DLookUp("vendor_code", "csv_file","cod_id=" & sp_list.[cod_id])
,sp_list.vendor_name = DLookUp("vendor_name", "csv_file","cod_id=" & sp_list.[cod_id])
WHERE
csv_file.[manager] = 'Test'
AND (
(sp_list.manager & '') <> (csv_file.[manager] & '') OR
(sp_list.vendor_code & '') <> (csv_file.[vendor_code] & '') OR
(sp_list.vendor_name & '') <> (csv_file.[vendor_name] & '')
);
P.S.
condition sp_list.[cod_id] = csv_file.[cod_id] & ''
is simplified as sp_list.[cod_id] = csv_file.[cod_id]
.
Test data
create table sp_list (cod_id counter NOT NULL, manager varchar(255)
, vendor_code varchar(255), vendor_name varchar(255));
INSERT INTO sp_list (cod_id, manager, vendor_code, vendor_name) VALUES
('1', 'test', 'vendor_code1', 'vendor_name1-0');
CSV file
"cod_id";"manager";"vendor_code";"vendor_name"
1;"test";"vendor_code1";"vendor_name1-3"