Subtracting a field from a linked .csv file

[imincorrigible]

Updating to Access 2013 is causing me some problems. I now have a table linked to a .csv to get some data from it. I am trying to take that some of that data and subtract it from a table in my database but I get an error, updating data in a linked table is not supported by this ISAM. I am not trying to update the data in the .csv file just use it to subtract the number in a table. I have taken that .csv file and imported it into a table in the database and everything works fine. I have made a table in another database from that .csv file and linked to that table, it works fine. I am using a .csv file because I am getting data from a .dbf database that I no longer can link to so I have to export the data out of the .dbf and into a .csv file. Any suggestions on what I might be able to do short of creating a new table every time I want the most up to date data? Thank you for looking.

[HansV]

Option 1:

Don't include the linked .csv file directly in the query. Instead, use DLookup to retrieve a field from the .csv file.
Let's say that you originally linked the Access table to the linked .csv file tblCSV on a field named ID, and that you wanted to subtract the value of the field Amount.
Instead of [tblCSV].[Amount], use DLookup("Amount","tblCSV","ID=" & [ID])

Option 2:

Link to the .csv file and also import it. Let's say that the linked table is tblCSV and the imported version is tblImported.
Create a delete query that deletes all records from tblImported:

DELETE * FROM tblImported

Create an append query that adds all records from tblCSV to tblImported:

INSERT INTO tblImported SELECT * FROM tblCSV

Each time you want to do something with the data from the CSV file, run the delete query and the append query (in that order). You can then use tblImported.
You can run the two queries from a macro or from VBA if you want to automate it.

[imincorrigible]

Hi Hans.
I am trying to get option 1 to work but I get the same error message. I am trying to do the subtraction in an update query. Attached is a test database.
Thanks.

[HansV]

You cannot use the linked table in an update query. You have to fill the local Access table from the linked table, then use the local table to update other data.

(I cannot modify the database for you since I don't have the CSV file)

[imincorrigible]

1. Would I be able to use option 1 if I put it in VBA code behind a command button? Something like this?
strSQL = "UPDATE OpenordsCSV INNER JOIN tblFG ON OpenordsCSV.SPC = tblFG.SPC SET tblFG.Quan = [tblFG].[Quan]-DLookUp("Quan","OpenordsCSV","SPC=" & OpenordsCSV].[SPC]) WHERE (((OpenordsCSV.[ON])=[Forms]![frmOpenordsCSV]![Combo7]));"
2. When you say fill the local Access table from the linked table, is this option 2 you are describing?
Thanks for you help.

[HansV]

1) No. You have to remove OpenOrdCSV from the query entirely, it is not needed - the DLookup takes care of it.
2) Yes.

[imincorrigible]

I got option 2 working. I couldn't get option 1 to work so I might play with it some more when I have a little more time.
Thank you for all your help. Have a great day!