sqlms-access

Updating Linked Table not Supported in this ISAM


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?


Solution

  • 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"